Variables Are the Elements You Interact With

Your code consists of variables, operators and statements. Variables are the nuts and bolts of an application. Conceptually, variables represent the items or objects that your code works on. For example, a variable can represent a number that you need to perform math on, a text string that you need to edit, a worksheet, a workbook, and so on. You can programmatically manipulate anything in Excel that you interact with in normal use. To interact with these items

♦ Create a variable of the appropriate object or data type.

♦ Assign an initial value to the variable.

♦ Perform operations or execute statements to modify or interact with the variable.

VBA makes it very easy to start programming without first having a fundamental understanding of the concept of declaring and using variables. Unlike many other programming languages, you can use a variable simply by using a variable name in your code (provided that you're not using the Option Explicit statement). This is known as implicit variable declaration. For example, consider the following simple subroutine that displays an informational message box.

Sub DisplayMessage(Message)

InfoButtons = vbOKOnly + vbInformation MsgBox Prompt:=Message, Buttons:=InfoButtons End Sub

In this example InfoButtons is a variable that contains a value that instructs the MsgBox function which buttons and icon to display. You didn't need to do anything special to begin using this variable other than come up with a name.

In most other programming languages, you'd first need to declare the variable. Conceptually you can think of it as declaring your intention to use the variable. Internally, when your program runs, the computer reserves a chunk of memory that your program can refer to by the name you gave it. When you declare a variable, you also need to state to what kind of data type the variable refers. Among other things, this allows the computer to determine how much memory it needs to set aside because different data types require different amounts of memory for their storage.

At first, you may think that using variables without having to declare them is a gift. Those programmers at Microsoft sure are good to us to give us this functionality. I mean, why declare variables if you don't have to, right? Unfortunately, a drawback to this exists. Although it helps ease people into programming, it also allows you to shoot yourself in the foot because it allows you to introduce visually subtle, yet programmatically significant errors into your programs. The people who tend not to explicitly declare variables are usually beginners who may not yet have the debugging proficiency to easily identify the source of their problem. The following example easily illustrates the issue at hand.

Sub ImplicitVariablePitfall()

DomesticInvestments = InputBox("Enter domestic investment amount") ForeignInvestments = InputBox("Enter foreign investment amount") TotalInvestment = DomesticInvestments + ForiegnInvestments MsgBox "The total investment = " & TotalInvestment End Sub

Enter this code into an empty module that doesn't specify Option Explicit and run it a couple of times. You should notice that no matter what you input for the foreign investment amount, the total investment amount always equals what you entered for the domestic amount. The reason is that there is a spelling error in the TotalInvestment calculation. The "ei" in ForeignInvestments has been transposed. As a result, a new variable (with an initial default value of 0) is used in this calculation rather than the variable you intended to use. Unlike this short example, many beginners tend to write very lengthy procedures that make this type of error much more difficult to locate.

Now, enter the statement Option Explicit at the top of the module. If you run the procedure now, you'll get a "Variable Not Defined" compile error and the first offending statement will be highlighted. In order to remedy the situation, you need to rewrite the procedure as follows:

Sub ImplicitVariablePitfall() Dim DomesticInvestments Dim ForeignInvestments Dim TotalInvestment

DomesticInvestments = InputBox("Enter domestic investment amount") ForeignInvestments = InputBox("Enter foreign investment amount") TotalInvestment = DomesticInvestments + ForiegnInvestments MsgBox "The total investment = " & TotalInvestment End Sub

If you attempt to run this code (the spelling error is still present) a compile error occurs and the misspelled variable is highlighted. The result is that you find out about these kinds of errors in the development process rather than in a meeting where your boss is presenting errant numbers due to your faulty program. I'll dive a little deeper into declaring variables later in this chapter.

Developing their conceptual model of variables is one of the first hurdles for many beginning programmers. The easiest way to get started with variables is to start with the basic types. First, you need to be aware of the different kinds of data types that are available.

0 0

Post a comment