Microsoft Office Tutorials and References
In Depth Information
For VBA to execute this macro, it must go through the same behind-the-scenes gyrations four
separate times to satisfy each of the four commands that reference range A1. And if your workbook
design changes, where you move the number of interest from cell A1 to cell K5, you need to go into
the code, find each related code line, and change the cell reference from A1 to K5.
Fortunately, there is a better way to handle this kind of situation — by declaring a variable to refer
to the value in cell A1 just once, like this:
Dim myValue As Integer
myValue = Range(“A1”).Value
Range(“C3”).Value = myValue
Range(“D5”).Value = myValue / 12
Range(“E7”).Value = myValue * 365
MsgBox “The original value is “ & myValue
By assigning the number value in cell A1 to the myValue variable, you’ve increased your code’s
efficiency and its readability, and VBA will keep the number value in memory without having to
reevaluate cell A1. Also, if your cell of interest changes from A1 to some other cell, say cell K5,
you only need to edit the cell address in the assignment code line to refer to cell K5, like so:
myValue = Range(“K5”).Value
As you’ve probably noticed in this situational example, a variable declaration is advisable, but it is
not an absolute requirement for the WithoutVariable macro to function. However, as you will see
in the upcoming lessons, variable declaration will be a necessary practice for handling more
complex tasks that involve loops, object manipulation, and conditional decision-making. Don’t worry —
after you see a few examples of variables in action and start practicing with them on your own,
you’ll quickly get the hang of when and how to declare variables.
dATA T ypEs
Simply stated, VBA’s role in life is to manipulate data in a way your computer can understand it. A
computer sees information only as a series of binary numbers such as 0s and 1s — very differently
than how humans see information as numerals, symbols, and letters of the alphabet.
Your macros will inevitably manipulate data of varying types, such as text, or numbers, or range
objects. Part of VBA’s job is to bridge the communication gap between humans and computers,
by providing a method for telling the computer what type of data is being referred to in code.
When you specify a data type in VBA, you help the computer to know how it should regard your
data so that your macros will produce the results you’d expect, based on the types of data you are
understanding the different data Types
Data types are the different kinds of ways you can store data in memory. Table 6-1 shows a list of
common data types, with their descriptions and memory usage.