© Roy Cox (2005 - 2020)

Related articles Option Explicit

Declaring Variables in Excel VBA

You will probably ask what declaring (or dimming) your variables does. The Dim statement was introduced in the early days of programming and stands for Dimension. You are basically telling Excel to put some memory aside to use for a variable, you can also specify what type of variable it is intended for. Variables are used to hold a value that you can change within the code and continue to use it in that session of the code. Many coders do not bother declaring their variables. The code will still work but there are many advantages to declaring them. This Microsoft documentation gives a handy summary of the variables and the data they can hold - see Data type summary . Some of the more frequently used variables are: String – used to store text values. Boolean – used to store TRUE or FALSE values. Integer – used to store whole number values(-32,768 to 32,767) Long - used to store larger numbers Double – used to store numbers with decimals. Date – used to store dates. There are many other types of variables available. Different variable types take up different amounts of memory, although this is less of an issue with the amount of RAM on modern computers.

Quick Overview of Variables

A variable is defined as a storage location in the computer memory that holds temporary information. The main types of variable data types include numerical and non-numerical data types. The advantage of using a variable rather than a constant is that users can change the value of the variable while the code is in progress.

Some advantages of always declaring your variables

If you use the Option Explicit Statement at the top of your Module then Excel will also check that you have dimmed ALL your variables. It won’t run the code until all variables are declared. When you have declared an object variable e.g. you are using several worksheets in your code. If you prefix each worksheet with ws then as soon as you as you have typed ws you can press Ctrl + Space to autocomplete. You will see all those variables listed and choose the one you want – this saves typing and possible typos. Another good trick is to type your variables using at Ieast one uppercase character. Once you type the name, Excel will convert the name to match the case of the one that you dimmed. If you type the variable in lowercase and it is still in all lowercase you have made a typing mistake and you can correct it immediately.

Points to remember when naming your variables.

You cannot start a variable name with a number, although they can contain numbers. Spaces should not be used in the variable name. Use an underscore character to separate values and make them readable instead. There are certain reserved keywords in VBA such as Dim, Private, Function, Loop and others that you will use in your code but you cannot name a variable after a keyword. See here for a comprehensive list of keywords. You cannot use special characters such as !, @, &, ., # when naming your variables. The name of your variable cannot be more than 255 characters in length, although I would suggest keeping variable names short and meaningful. VBA is not case-sensitive. You can use mixed case to make variables readable, even though VBA will consider all cases similar.

Public, Private, Global and Static Variables.

These are the other four keywords you can use when declaring your variables.

Public variables:

For a variable to be Public, i.e. available to all Sub Procedures and Functions within the Module. You need to place your variable in the Declarations section of your VBA code below the Option Explicit statement, outside of any of your Sub Procedures or Functions and you also have to use the Public keyword. Used like this you do not need to use Public, Excel will understand that the variable is public to that module. If you want the variable to be usable by all Modules then use Public although it is probably easier to do this in an empty module used for your public variables.

Global variables:

Public and Global are nearly identical in their use, however Global can only be used in standard modules, whereas Public can be used in all modules, classes, controls, forms, etc. Global comes from older versions of VB and was probably retained for backwards compatibility, but it is now sufficient to only use Public.

Private Variables:

All constants and variables are private by default. You actually override the default by declaring them as public, provided that the declaration is inside a standard module rather than in a UserForm or procedure. It is best not to declare a variable as a constant or public if it is only going to be used by one procedure. A constant or variable declared inside a procedure using the Const or Dim statement is considered local and has a lifetime that lasts only while that procedure is running.

Constants:

These are different to variables because their value that cannot be changed during the execution of the code. If a user tries to change a Constant value, the script execution ends up with an error. Constants are declared the same way the variables are declared using the keyword Const.

Points to be aware of

1. You might see variables declared like this- Dim x,y,z as Integer This is incorrect. Only z will be recognised by Excel as an integer. The other two, x and y, will be Variants because they have not been explicitly declared as Integer, the correct way is: Dim x as Integer, y as Integer, z as Integer 2. There is no specific place to declare your variable inside a Sub Procedure, they can be placed anywhere within that Procedure. However, I prefer to list them at the start of the procedure, I also group them together by type. This makes it easier to check what you have declared.

Page Title

Making Excel work for you.
If you find this article useful You can help maintain my site by donating. Simply click the image below
Buy me a coffeeBuy me a coffee
Option Explicit Dim iX As Integer Sub Test1()     iX = 10     MsgBox iX End Sub Sub Text2()     For iX = 1 To 5         MsgBox iX     Next iX End Sub Declare a Public Variable

© Ut duis incididunt ex officia

Related articles Option Explicit

Declaring Variables in Excel VBA

You will probably ask what declaring (or dimming) your variables does. The Dim statement was introduced in the early days of programming and stands for Dimension. You are basically telling Excel to put some memory aside to use for a variable, you can also specify what type of variable it is intended for. Variables are used to hold a value that you can change within the code and continue to use it in that session of the code. Many coders do not bother declaring their variables. The code will still work but there are many advantages to declaring them. This Microsoft documentation gives a handy summary of the variables and the data they can hold - see Data type summary . Some of the more frequently used variables are: String – used to store text values. Boolean – used to store TRUE or FALSE values. Integer – used to store whole number values(-32,768 to 32,767) Long - used to store larger numbers Double – used to store numbers with decimals. Date – used to store dates. There are many other types of variables available. Different variable types take up different amounts of memory, although this is less of an issue with the amount of RAM on modern computers.

Quick Overview of Variables

A variable is defined as a storage location in the computer memory that holds temporary information. The main types of variable data types include numerical and non- numerical data types. The advantage of using a variable rather than a constant is that users can change the value of the variable while the code is in progress.

Some advantages of always declaring your variables

If you use the Option Explicit Statement at the top of your Module then Excel will also check that you have dimmed ALL your variables. It won’t run the code until all variables are declared. When you have declared an object variable e.g. you are using several worksheets in your code. If you prefix each worksheet with ws then as soon as you as you have typed ws you can press Ctrl + Space to autocomplete. You will see all those variables listed and choose the one you want this saves typing and possible typos. Another good trick is to type your variables using at Ieast one uppercase character. Once you type the name, Excel will convert the name to match the case of the one that you dimmed. If you type the variable in lowercase and it is still in all lowercase you have made a typing mistake and you can correct it immediately.

Points to remember when naming your variables.

You cannot start a variable name with a number, although they can contain numbers. Spaces should not be used in the variable name. Use an underscore character to separate values and make them readable instead. There are certain reserved keywords in VBA such as Dim, Private, Function, Loop and others that you will use in your code but you cannot name a variable after a keyword. See here for a comprehensive list of keywords. You cannot use special characters such as !, @, &, ., # when naming your variables. The name of your variable cannot be more than 255 characters in length, although I would suggest keeping variable names short and meaningful. VBA is not case-sensitive. You can use mixed case to make variables readable, even though VBA will consider all cases similar.

Public, Private, Global and Static Variables.

These are the other four keywords you can use when declaring your variables.

Public variables:

For a variable to be Public, i.e. available to all Sub Procedures and Functions within the Module. You need to place your variable in the Declarations section of your VBA code below the Option Explicit statement, outside of any of your Sub Procedures or Functions and you also have to use the Public keyword. Used like this you do not need to use Public, Excel will understand that the variable is public to that module. If you want the variable to be usable by all Modules then use Public although it is probably easier to do this in an empty module used for your public variables.

Global variables:

Public and Global are nearly identical in their use, however Global can only be used in standard modules, whereas Public can be used in all modules, classes, controls, forms, etc. Global comes from older versions of VB and was probably retained for backwards compatibility, but it is now sufficient to only use Public.

Private Variables:

All constants and variables are private by default. You actually override the default by declaring them as public, provided that the declaration is inside a standard module rather than in a UserForm or procedure. It is best not to declare a variable as a constant or public if it is only going to be used by one procedure. A constant or variable declared inside a procedure using the Const or Dim statement is considered local and has a lifetime that lasts only while that procedure is running.

Constants:

These are different to variables because their value that cannot be changed during the execution of the code. If a user tries to change a Constant value, the script execution ends up with an error. Constants are declared the same way the variables are declared using the keyword Const.

Points to be aware of

1. You might see variables declared like this- Dim x,y,z as Integer This is incorrect. Only z will be recognised by Excel as an integer. The other two, x and y, will be Variants because they have not been explicitly declared as Integer, the correct way is: Dim x as Integer, y as Integer, z as Integer 2. There is no specific place to declare your variable inside a Sub Procedure, they can be placed anywhere within that Procedure. However, I prefer to list them at the start of the procedure, I also group them together by type. This makes it easier to check what you have declared.
How to Install an Addin

Page Title

Logotype