This post may contain affiliate links. Please read my disclosure for more info.
Advertisement:

2.1 Using variables

Beginning VBA

Beginning VBA

Previous Part: 2.0 First steps in programming VBA

 

What are variables

Variables are pieces of code that we can use to store information and objects.  A variable is a bit like a bucket that you keep different things in.

The main attributes of the variables are:

  • Name – the code by which we refer to the variable (what do you want to call your bucket?)
  • Type – different variables hold different types of information (what types things are stored in your bucket?

When looking at recorded VBA code you will notice no variables are created.  Therefore, variables are definitely something you will need to program yourself.

 

Declaring variables

In a previous chapter we setup the VBE environment so that we will always declare variables.  This means we must create all our variables before we can use them, this is known as declaring the variable.

To declare a variable the code follows a pattern:

Dim [myVariableName] As [type of variable]
  • myVariableName is the name we will give our variable
  • type of variable represents the type – is it text? a number? an object? etc.

Advertisement:

Example:
If we wanted to declare a variable to count the number of rows in the worksheet we would declare it as follows:

Dim rowsInWorksheet As Long

Long is a type of variable (see below for more detail)

 

Types of variables

Each variable type has different properties.

Numeric variable types

Numeric variable types can only contain numbers and can be used in calculations.

Byte (memory size: 1 byte)
A whole number from 0 to 255

Integer (memory size: 2 bytes)
A whole number from -32,768 to 32,767

Long (memory size: 4 bytes)
A whole number from -2,147,483,648 to 2,147,483,648

Single (memory size: 8 bytes)
A negative number from -3.402823E+38 to -1.401298E-45
A positive number from 1.401298E-45 to 3.402823E+38


Advertisement:

Double (memory size: 8 bytes)
A negative number from -1.79769313486232e+308 to -4.94065645841247E-324
A positive number from 4.94065645841247E-324 to 1.79769313486232e+308

Currency (memory size: 8 bytes)
A number from -922,337,203,685,477.5808 to 922,337,203,685,477.5807

Decimal (memory size: 8 bytes)
If no decimal places +/- 79,228,162,514,264,337,593,543,950,335
With decimal places +/- 7.9228162514264337593543950335.

Boolean (memory size: 2 bytes)
Values can be TRUE/FALSE (1 or 0).

Date (memory size: 8 bytes)
January 1, 0001 to December 31, 9999, provided as a number.  The number to the left of the decimal place represents the data, the value to the right of the decimal place represents the time.

Non-numeric variant types

Non-numeric variant types contain mainly text and cannot be can used in calculations.

String (memory size: Length of String (for fixed-length strings) or Length of String + 10 Bytes for variable length strings)
Fixed length strings can have 1 to 65400 characters
Variable length strings can have 0 to 2 billion characters

To declare a fixed length string use “String * n” where n is the length of the string.


Advertisement:

Object (memory size: 4 bytes)
Contains the address/location of the object.

Other variant types

Variant (memory size: 16 bytes for numbers, string length + 22 bytes for strings)
Can contain any of the other variable types (except a fixed length string).

 

Naming variables

The names of variables must meet certain criteria:

  • Less than 255 characters in length
  • Can contain only letters, numbers and the underscore character (no spaces, periods or special characters)
  • Must always begin with a letter
  • Cannot be the same as a named reserved for special uses within the VBA language.

Giving a variable a value

We have declared the variable, but it is currently empty.  The give the variable a value we need to understand what type of variable it is.

For all variables which are not objects.

Dim myNumber As Integer
myNumber = 500

For variables which are objects

Dim myWorkbook As Object
Set myWorkbook = ThisWorkbook

 

Previous Part: 2.0 First steps in programming VBA

Next Part: 2.2 Loops (part 1)