Home | Excel VBA | VBA Coding – Declaring Variables & Constants

VBA Coding – Declaring Variables & Constants

You should know how to declare, initialize and display variables and constants in VBA. To declare a variable you need to use the format as Dim variable_name as data_type. For example, if you have a statement Dim x as String, it means that you declare a VBA variable with name x of type String. To initialize variable, you just need to specify as x = “hello”. Here, you assign “hello” as the initial value of variable x. Later, you can change the value of this variable whenever it is required. Constants are also declared in the same way. Const Pi = 3.1419 means that Pi would always have the value 3.1419. Anyhow, even if you do not declare a variable before using it, you would not get any error because all variables are assumed to be of Variant data type by default and you can assign a number or text to a variable though you forget to declare.

Anyhow, it is not a good programming practice to use variables without declaring it. First of all, all undeclared variables would take Variant data type which requires more memory. It could affect the performance of your program adversely. You could face Typo bugs also because if you used a variable named ‘tax’ once and later misspelled it as ‘tax1’, then VBA would not consider this as a mistake. Instead it would create another variable named tax1. Thus, you also could not prevent or identify typo bugs. If you wish VBA to force you regarding declaration, you can include the statement ‘Option Explicit’ at the top of your code. So, if you try to use any variable without declaring, you will get an error during compilation.

If you declare a variable within a function or subroutine, its scope is only within the function. You cannot get its value if you try to access it from outside the function. If you declare a variable at the top of a module, then its scope is for all different functions. So, if you assign the value 5 to a variable x within Function1 and if you try to access x from Function2, it will have value 5.

Write the code given below in the code window of any of your worksheet.

Sub myVariable()

Dim x As String

x = “Hello, My First Variable”

Range(“A1″).Value = x

MsgBox (x)

End Sub

Now, if you run this procedure, and check in Excel, you could find the value “Hello, My First Variable” in cell A1 and a message box with the same message.

Try the given below code to understand the difference between double and integer.

Sub testIntAndDouble()

Dim x As Integer

Dim y As Double

Dim z As Double

x = 5.5

y = 6.5

z  = 7

MsgBox (“Value of x is “& x & ” and value of y is ” & y & ” and value of z is ” & z)

End Sub

If you run this procedure, you would get the message box as given below.

Have you assigned the value of x as 6? No. But, as you declared x as an Integer and did not assign a whole number to x, it got converted to an integer and displayed as 6. As you declared y as double itself, it value is shown correctly. Anyhow, never try to declare double for variables which you want to store integers. It is true that you won’t get any error because of this.  The issue is that Double variables require more space which will make your code run slower. Moreover, you would be able to locate errors easily if you use variables of correct type. Message boxes are usually used to display messages. These are used widely during testing to check the logical flow of the code. Here, ampersand (&) sign is used to concatenate values displayed in the message box.

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>