Microsoft Office Tutorials and References
In Depth Information
Assigning Values to Variables
One common data type is called Integer , which, as you will see in Table 6-1, refers to whole
numbers within a certain range. Using the preceding four steps as a sequential construction guide, here is
a typical-looking variable declaration statement:
Dim myValue As Integer
You’ll soon see the enormous benefit that this kind of innocent-looking statement can have in your
macro. Although a few wrinkles exist in the variable declaration process, a variable declaration
statement will often look no more complicated than this.
You will find that editing a macro in the Code window is very similar to editing a
Word document. Of course, rules exist for proper syntax of VBA code lines, but
the principles of typing text, selecting words and deleting them with the Delete
key, pressing Enter to go to the next line down — all these word-processor kinds
of behaviors with which you are familiar — will help make the macro editing
process an intuitive one.
Assigning VAluEs To VAriABlEs
After the variable declaration statement, which might be the next code line or 100 code lines later in
your macro, depending on what you are doing, you will have a statement that assigns a value to the
myValue variable. Here’s an example of assigning the number in cell A1 to the myValue variable:
myValue = Range(“A1”).Value
The value you assign might be an actual value that is stored in a cell, as in the preceding example, or it
might be a value you create, again, depending on the task at hand. This notion will become clearer
with more examples you’ll be seeing throughout the book.
WHy you nEEd VAriABlEs
I mentioned earlier that in some situations, employing a variable will be a sensible option. Suppose
you have a number in cell A1 that you are referring to for several analytical purposes throughout
your macro. You could retrieve that number by referring to its A1 cell address every time, but that
would force Excel to look for the same cell address and to recommit the same number to memory
every time.
As a simplified example, here is a macro with four commands, all invoking the value in cell A1:
Sub WithoutVariable()
Range(“C3”).Value = Range(“A1”).Value
Range(“D5”).Value = Range(“A1”).Value / 12
Range(“E7”).Value = Range(“A1”).Value * 365
MsgBox “The original value is “ & Range(“A1”).Value
End Sub
 
Search JabSto ::




Custom Search