Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel Named Ranges and VBA

Excel Named Ranges and VBA

Excel Named Ranges and VBA

I am writing an Excel VBA code that will collect information from the spreadsheet and then manipulate it.

I have several single-cell named ranges that I want to bring into the VBA code.

My question is:

Is it better/easier to declare variables in the VBA code and populate them with the named range values? Or is it better to continually just refer to the named range in the code?

For example, is it better to do something like this:

dim fc_prime as long
fc_prime = Range("fc_prime")

Or is it better to just continuously write Range("fc_prime") throughout the code as I perform calculations?

I would like the code to be as 'readable' as possible, so when I refer back to it after several months I can figure out what I was doing. :)

Thank you.

RE: Excel Named Ranges and VBA


I’m a big advocate of Named Ranges. In practice they have multiple benefits and uses:

1) documentation in the sheet(s)

2) usage in sheet(s)

3) usage in VBA like


x = y * [fc_prime] 

Often use Formulas > Defined Names > Create from selection...


glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel Named Ranges and VBA

From my perspective, it makes more sense to leave all definitions in one place (e.g. at the start of the code). At the very least, if you have defined a name outside the VBA, then you should leave a note somewhere in your code to alert the reader that the range is defined outside of VBA.

Personally I don't like named ranges much because there can be conflicts and users might not so easily spot an error. However, I appreciate they do have their uses and ultimately think this is a matter of preference.

RE: Excel Named Ranges and VBA

I recommend a slightly amended version of your first option:

dim fc_prime as long
fc_prime = Range("fc_prime").Value2

The Range object has a huge number of properties. .Value is the default, but it makes your code more readable if you specify the property you want, rather than relying on the default. Using .Value2 is quicker than .Value because it just brings in the value (as a long in this case, or a variant if you don't specify), whereas .Value has some other information (I forget what).

Using Range("fc_prime") throughout the code will be much slower because it has to do the data transfer each time, rather than using the defined value. Also the value transferred will be a variant, which is much slower than using a long (or a double).

Of course the extra time often won't matter, but sometimes it will, so it makes sense to use the most efficient procedure.

Also it saves typing.

Doug Jenkins
Interactive Design Services

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close