More questions on variables

[davidam]

Hello All,
I might be dreaming, but my project seems to run more quickly now that I have added numerous variables to ensure that it never gets lost. I am wondering how far I need to take it. Is it just good practice to always use a variable; here are a few examples...

 Range("A10").Value = ActiveCell.Column 'used to navigate back to the same point on the sheet
 Range("B2").Value = Range("B3").Value ' used to create a data saving checkpoint

Should I:
a. Be trying to avoid using spreadsheet valuations for checkpoints and the like and trying to use variables instead? So in the first example, where A10 is used to return the user to where he was before some action, should I set a variable = A10 and then use this variable for navigation rather than referring back to the cell value?
b. (B3 sums up all of the values entered onto the sheet and then uses this sum to check whether changes are being made to the data...so it will always be a spreadsheet value)...should I be using something like: wkbSheet.Range("B3").Value to reference it, even if it is the only book open? and...
c. In the case of the summation above should I be setting a variable = B3 (the summation) and then using this for comparisons?
etc..
Thanks,
David
(Recent graduate from the 'recorder school' of VBA coding)

 

[Smitty]

A general rule of thumb is that anything you're going to reuse should be a variable. So if you refer to A10 multiple times in your code, you set it as a variable, that way you only explicitly refer to A10 once.

If you only have one wb oopen, then there's really no need to explicity refer to it. If you're going to work on multiple workbooks than setting them as variables is good, because again, you only have to explicity refer to the workbook once.

As for B3, if you're going to refer to its value repeatedly, then use a variable for the same reasons.

HTH,

 

[davidam]

Great...makes sense.
Thank you!