Excel VBA Variables declaration

Excel VBA Variables declaration

In this post you’ll learn Excel VBA variables declaration.

Excel VBA Variables

 

What is Excel VBA Variables?

Excel VBA Variables are used to store different type of data such as integer, String, boolean, double, date, currency etc,. in computer memory to use that values in procedure, module or workbook. The values stored in the variables can be retrieved from computer memory if user want to use the values to do further programming before the variables loses its scope. Values stored in the variables are temporary and might be continuously changing based on how it is used in the programming. VBA Programming is almost impossible without using variables in the code.

For e.g. Let us assume that Range A1 in sheet 1 contain some 500 words of text which we call it as String in programming. Whenever you want to use that string you should specify like Thisworkbook.Sheets(1).Range(“A1”).Value. Instead of doing like this you can assign this value to a variable sFirst using the assignment operator.

sFirst =Thisworkbook.Sheets(1).Range(“A1”).Value

Hereafter, when you want to use that string in any part of your programming you can specify it by variable name sFirst.

 

Rules to Declare Excel VBA variables

There are certain programmer should follow when declaring Excel VBA variables. Declared variable name must follow the below Excel VBA variables rules.

  • Length of the Excel VBA Variables must be less than 255 characters
  • Special characters like (#, $, %, &, or !) are not acceptable
  • Excel VBA Variables must begin with letter or underscore.
  • Excel VBA Variables must not contain period or spaces.
  • Excel VBA Variables name must be unique in the module or procedure where it is used.
  • Excel VBA Variables must not contain any of the Excel VBA reserved keywords.

Excel VBA reserve some of the keywords for internal use For e.g. Dim, For, Next, Do, Until, And, Or, IsNumeric, If, EndIf, Integer, Double, Date, Select, Case, End Select, etc.,

 

How to declare Excel VBA variables?

            The recommended way of declaring Excel VBA variables is using Dim Statement as prefix and followed by its data type(Integer, Double,String etc.,). Even Though Dim statement and data type are optional it’s a best programming practices. Variables can also be declared without using Dim statement and Data type. If you’re using Dim statement then you’re telling the compiler that you’re declaring a variable but still the compiler is good enough to determine the variable without the presence of Dim keyword. Data type tells the compiler the type of data that you’re going to store in the variable. If the data type is not provided then compiler uses the default data type called Variant. Variant can store and handle almost all kind of data but it gradually affects the performance of the code.

 

Syntax

            Dim sName As String

Dim- Short for the word Dimension and it helps to determine the variable data type and other information.

sName- It the variable name where the data gets stored.

String- It defines the type of data stored in the variable.

 

Excel VBA variables Data type

 Excel VBA variables data type are primarily classified into two types:

1) Numeric data type

2) Non-Numeric Data type

 

1) Numeric Data type

            Numeric Data type used to deal with numerical formats such as decimal, fraction, Integer etc,. Below are the list of numeric data types, space it allocates to store the value and the range of values it can handle.

Numeric Data Type

Declare variable using Integer Data Type

Integer Data type used to store values in the range -32768 to 32767 and allocate 2 Bytes of computer space. When you attempt to store value larger than the integer range then compiler throws an error message “Run time error: Overflow”.

 

Option Explicit

Sub Exercise1()

Dim aNum As Integer

aNum = 653564

MsgBox aNum, vbOKOnly, “Verify”

End Sub

Overflow error Message

2) Non Numeric Data Types

Non Numeric data types are used to deals with non numerical data such as byte, logical values (true or false), date, string (text) etc. Below are the list of non-numeric data types, space it allocates to store the value and the range of values it can handle.

Non numeric Data Type

 

Declare variable using Boolean Data type

Boolean data type contains only two values (True or False) and allocates 2 Bytes of computer memory.

Option Explicit

Sub Exercise2()

Dim IsGreater As Boolean

If IsGreater = True Then MsgBox “Boolean Returns True”, vbOKOnly, “Verify”

End Sub

 

Option Explicit

            Option Explicit command is used to force the programmer to declare all Excel VBA variables before you use. Option Explicit command should be added at the top of the code. It is the best way to force user to declare variable and avoid errors due to misspelling of variables. Before execute the code compiler run from top to bottom of the code to check whether all the variables are declared and if any of the variable is not declared then it pop up an error message stating “Compile Error: Variable Not Defined”.

In the below code, I’ve used an option Explicit command at the top , used variable sVar without declaration and assign string “Hello World” to it. When I execute with F5 or Run command, VBA throws an Error message and ask the user to declare variable.

Option Explicit

 

Recommend Books

Excel 2013 Power Programming with VBA

Excel VBA: A Beginners’ Guide

Leave a Reply