Using Variables in Excel VBA
A Variable is used to store information temporarily so that it can be used for execution within the Procedure, Module or the Workbook. First, some rules when using Variables.
a) The name you give a Variable must begin with a letter and not a number. Numbers can be included within the Variable name, just not as the first character.
b) A Variable name cannot be longer than 250 characters.
c) A Variable name cannot be the same as any of Excel’s key words. This means you cannot name a Variable with such names as Stop, GoTo etc.
d) Variable names must be one continuous string of characters. You can seperate with an underscore or capitilising certain letters.
A Variable can be given any name, so long as it is a valid name, as per the four points above. A Variable could be called “ExcelGeek” and then declared as any one of the data types shown below. It is good practice to standardise your naming so that when you, or anyone else, reads the code back it can be easily understood and followed, and it is clear what type of data the Variable is. A method I often employ is to prefix the Variable name with first few letters of the data type. For example, let’s say I want to declare “ExcelGeek” as a String, I would call the Variable “strExcelGeek”.
Remember, you may understand the code now, but as is often the case you may have to come back to it a year later! Will you understand it then!? I once went through some code I had written a year previous and I was shocked with my lack of naming conventions. I initially thought someone else had written it!
Variables can be declared as any one of the these data types:
Boolean data type
This data type has only two possible values, True (-1) or False (0). Boolean variables are stored as 16-bit (2-byte) numbers.
Byte data type
Used to hold positive integer numbers from 0 to 255. Byte variables are stored as single, unsigned 8-bit (1-byte) numbers.
Currency data type
The Currency data type ranges from -922,337,203,685,477.5808 to 922,337,203,685,477.5807. This data type should be used for calculations involving money and for fixed-point calculations where accuracy is particularly important. The at sign (@) type-declaration character represents Currency in Visual Basic.
Date data type
Variables declared as a data type are used to store dates and times as a real number. These variables are stored as 64-bit (8-byte) numbers. The value left of the decimal represents a date. The value to the right of the decimal represents a time.
Double data type
A data type that holds double-precision floating-point numbers as 64-bit numbers in the range -1.79769313486232E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values. The number sign (#) type-declaration character represents the Double in Visual Basic.
Integer data type
A Integer data type holds integer variables stored as 2-byte whole numbers ranging from -32,768 to 32,767. Integer data types can also be used to represent enumerated values. The percent sign (%) type-declaration character represents an Integer in Visual Basic (VB).
Long data type
A 4-byte integer that ranges in value from -2,147,483,648 to 2,147,483,647. The ampersand (&) type-declaration character represents a Long in VB.
Object data type
This data type is used to represent any Object reference. Object variables will be stored as 32-bit (4-byte) addresses that refer to objects.
Single data type
A data type that stores single-precision floating-point variables as 32-bit (2-byte) floating-point numbers, that range in value from -3.402823E38 to -1.401298E-45 for negative values. For positive values they range from 1.401298E-45 to 3.402823E38. The exclamation point (!) type-declaration character represents a Single in VB.
String data type
A data type consisting of a sequence of characters that represent the characters rather than their numerical values. A String data type can include letters, numbers, spaces, and punctuation. A String can store fixed-length strings ranging in length from 0 to approximately 63K characters. Whereas for dynamic strings they can range in length from 0 to around 2 billion characters! The dollar sign ($) type-declaration character represents a String in Visual Basic.
Variant data type
The Variant data type can contain date, numeric, or string data as well as the special values Empty & Null. This data type has a numeric storage size of 16 bytes and can contain data up to the range of a Decimal, or character storage size of 22 bytes (plus string length), and can store any character text. The VarType function defines how the data in a Variant is treated. If you don’t declare a Variable as a type then by default they will become Variant data types. Variant data types will take slightly longer to process in code because Excel must first work out what the data type is.
So that is a brief run down of data types in Excel VBA. The most important point is to have good naming conventions, even if it means writing it down on a piece of paper, and be sure you declare the Variable as the correct data type.