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."

TrackBack URI for this entry

Comments (1)

Subscribe to this comment's feed

Ted said:

Very nice. I have avoided vba and macros up to this point but I think I'll give it a try.

Monday June 16, 2008 10:13AM

Show/Hide comment form

Name

Email

Title

Comment

Write the displayed characters

Add Comment