I could probably write a book on variables, but this short note might help some of the novice coders.
Not every object needs to be put into an object variable, and neither do all values need to be in variables, even if used in a function.
Use object variables when lengthy references will be needed multiple times, otherwise just use the actual reference. For example:
Your code deals with two workbooks and multiple sheets which will need to be accessed or refenced three or more times, Instead of writing Workbooks(1).Sheets(1).Range...etc., you can:
the sh1 and sh2 variables can be used multiple times to transact between the two workbooks without having to spell out the whole parent child chain each time. Additional sheet varibles can be created and used the same way. But don't over use variables and cloud up the code. Another misuse of variables is creating a new variable that equals an existing variable. That really confuses the issue, not only for a reviewer, but for the user as well. Once you have created the variable, use it consistently without bouncing back and forth between the variable and the full reference. That is also confusing to anyone attemting to analyze code, because it interrupts the thought process by having to confirm the object is logically correct at that point in the code. Variables are tools, not absolute requirements in coding. By being consistent, yet frugal, in their use, code can be more efficiently created and operated.Please Login or Register to view this content.
Bookmarks