Excel Named Ranges and VBA 1

[JoelTXCive]

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.

 

[SkipVought]

Hi,

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...

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[LRJ]

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.

 

[IDS]

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

http://newtonexcelbach.wordpress.com/