VBA and Excel for Engineers and Scientists – Part 2

Variables and Data Types

We started out discussing a container that will hold all of your code, a Module. Within your Excel Spreadsheet, you can have multiple Modules. Within each module you can have multiple Functions and Sub Procedures which are themselves containers of code in a sense. The smallest "container” is a variable which can contain a floating point number, an integer, a string of text, or a boolean (True/False) value among other things.

Before I get more into talking about what a variable is and how to work with it, let’s look at some of the most common things you will be storing in variables. Below is a table for the various data types.

Table 1: Data Types (From Walkenbach, 2004b)

The middle column from Table 1 is a suggested prefix for your variable’s name so that when you are coding, you can see what type of variable it is. This is not mandatory, but you may consider it to keep things tidy. At a minimum, use descriptive, unique variable names. Here are a few rules that VBA has for variable naming (After Walkenbalk, 2004a):

• You can use alphabetic characters, numbers and some punctuation characters, but the first character must be alphabetic.
• VBA variables are not case sensitive. For readability, it is suggested to use mixed case (MyVariable instead of myvariable)
• No spaces or periods, underscore character is acceptable.
• Watch out for VBA reserved words. If you use one of these words, you will get a syntax error. Reserved words appear in blue for the default settings in the VBE, they include words like "Then”, "Else”, and names of existing functions.

To see how to store values to a variable and to retrieve them, take a look at the following simple code examples (Sub or Function wrapper omitted).

Listing 4: Storing to and retrieving from variables

'Store a number to a variable, works the same for integers MyNumber = 1.234  'Get the contents of one variable and put it in another CopyOfNumber = MyNumber  'Store a text value, don't forget the quotation marks MyName = "Rockman"  'Store a boolean value IsCorrect = True IsWrong = False 

Here are some more examples, but these are utilizing the suggested variable prefix from Table 1. You can see how that might be useful. I also mixed in a math operator (* for multiplication), more on those later.

Listing 5: More variable examples (with suggested variable prefix)

sngX = 2.5 * 67.586 sngY = sngX * 34.9 blnTrueFalse = True strName = "Randy Post" dteBirthday = #7/1/2000# 'If you're hard coding a date or time, you need dteLunch = #12:00:00#       'the hash marks (pound sign) intCount = 2 

Another basic aspect of working with variables is appending to them. You would need that for a counter variable, but the concept works for string variables as well. VBA doesn’t have a particularly eloquent way of doing this like some other programming languages, but like I said, I don’t really give a crap about what’s eloquent.

Listing 6: Appending to variables

sngSum = 5.0 sngSum = sngSum + 10.0 'So sngSum now equals 15   'To "append" to strings, also known as concatenation, 'use the ampersand (&) character strMessage = "The Sum Is: " strMessage = strMessage & _ sngSum & " units." 

Tip: You can break long lines of code with the underscore character preceded by a space. Remember to indent the next line at least one tab for readability (multiple tabs or spaces are fine).

Tip: Another tip, the vbCrLf (carriage return/line feed) character is useful for adding a new line when appending to string variables. Use it like this: strNote = "Line 1″ & vbCrLf & "Line 2″