Excel Named Ranges and VBA
Excel Named Ranges and VBA
(OP)
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.
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
CODE
Often use Formulas > Defined Names > Create from selection...
Skip,
for a NUance!
RE: Excel Named Ranges and 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
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/