Variables - Types and Declaration

A variable is used almost every time you output a value or take input from a user. A variable is simply a (temporary) means of storing a value which is used within the macro. There are several different types of variable, used for storing different types or sizes of values:

Byte

Holds positive values from 0 to 255. Requires only a single byte of memory, so is very efficient.

Boolean

Only 2 possible values: True (-1) and False (0). Requires 2 bytes.

Currency

Stores numbers from -922,337,203,685,477.5808 to 922,337,203,685,477.5807 as fixed-point data.

Date

Stores dates and times as real numbers using 8 bytes of memory. The number to the left of the decimal point is the date and the value to the right of the decimal point is the time.

Double

A floating-point data type, ranging from -3.402823E38 to -1.401298E-45 for negative values and from 1.401298E45 to 3.402823E38 for positive values.

Integer

Holds positive and negative integer values from -32,768 to 32,767. In the past there was a performance advantage to using integers instead of long variables (integers take up less memory), however in recent versions of Excel integer variables are converted to long variables, meaning that any performance advantage no longer exists. Requires 4 bytes (formerly 2 bytes).

Long

Holds positive and negative numbers from -2,147,483,648 to 2,147,483,647, using 4 bytes of memory.

Object

A data type that stores the address of an object in Excel.

Single

A floating-point data type, ranging from -3.402823E38 to -1.401298E-45 for negative values, and 1.401298E-45 to 3.402823E38 for positive values. Requires 2 bytes of memory.

String

A variable that stores text rather than numbers, from 0 to 65,535 characters. Can take any letter, number, spaces, punctuation or symbols.

Variant

The largest memory-wise of the variables at 16 bytes, a variant data type can store any kind of data (strings, numerical values, dates, etc.) If a variable is not explicity declared as one of the other variable types, Excel automatically assigns the variant data type to a variable.

Variable Declaration and Scope

When writing a macro you usually know what data each variable will be storing, so you can declare each variable as an appropriate data type. A variable is usually declared locally - within a procedure. When that procedure ends, the variable is erased from the computer's memory. Declaring a variable in this way is done for example as shown below:

Sub Macro() Dim intVariable As Integer End Sub

A variable can also be declared at the beginning of a module (which can contain more than one procedure). In this case, the data stored in the variable is valid for and can be used in all of the procedures within that module:

Dim intVariable As Integer Sub Macro() 'Code End Sub

Finally, a variable can be declared at the top of a public module. These variables are then valid for all procedures within all modules:

Public intVariable As Integer

It is also possible to declare more than one variable of the same type at the same time. This would be done like this:

Dim intHeight, intWeight As Single

I generally choose variable names that indicate the variable type; this makes it easier further down in the code to quickly identify what type of variable you're using for certain data. Note also that variable names must be continuous characters with no symbols or spaces, and cannot start with numbers.

So are you ready to write your first program with variables and user input?


Example Macro
The goal of this macro is to build on the first "Hello, World!" macro in my previous post by taking input from the user and displaying a message saying how old the user is.

Open a new workbook and set up a couple of cells as shown below (note that you must use the same cells - B2 and C2 - or edit the code below to read the correct cell for the user input). Then add a button and edit the caption of the button to read "Calculate age" (clicking cancel when the "Assign macro" window pops up after creating the button).

Select the button, then click on the View Code button in the toolbar. Declare two variables; the user's birthday (a date type variable) and the user's age (an integer type variable):

Sub Button1_Click()
    Dim intAge As Integer
    Dim dateBirthday As Date
End Sub

Now we need to read the user's birthday into the dateBirthday variable and then calculate the user's age from their birthday. Add the following lines to do so:

dateBirthday = Range("C2").Value

intAge = Application.WorksheetFunction.RoundDown((Date - dateBirthday) / 365, -0.1)

The calculation of intAge uses VBA's ability to call functions from Excel itself. In this case we need to round down - for example if the user is turning 38 in 2 months, using an integer variable for his age will result in it being rounded up to 38 - when he's actually still 37! Using the RoundDown function makes sure the calculated age is correct. And finally we display a message giving the user's age by adding this line:

Msgbox "The user is " & intAge & " years old.", vbOkOnly, "Age"

Your completed macro code should look like this:
You can now close the code editor window; enter your birthday into cell C2 and click the "Calculate age" button and, if your code is correct, you Excel should give you your age!