VBA Variables

In VBA, and in programming in general, variable is a resource for saving information.

    Variable = "An information"

Variables are used to store data, such as numbers or texts.

Before using a variable it is advisable to declare it, indicating its name (and its data type, which will be seen later).

A declaration is made through the word Dim, as in the example below:

    Dim VarText
    Dim VarNumber

    VarText = "I was previously declared"

Naming a Variable

The variable name, which comes after the Dim keyword, must comply with the following rules:

• It must initiate with an alphabetical character
• It must not contain especial characters, such as: #, $, %, &, !
• It must not contain whitespace characters, dots or commas
• It must not be a VBA reserved word like: And, Or, Sub

When naming a variable, give preference to specific names such as DiscountSales instead of generic names like Var1. This will make the code much easier for other users to understand.

    Dim 1variable 'This statement is wrong!

Data Types

After defining the variable name, you should also declare its data type. This tells VBA what data type to expect from each variable, avoiding errors with improper associations and making it possible to optimize the memory allocation of the computer.

    Dim VarText As String   'String is a data type for texts
    Dim VarNumber As Integer 'Integer is a data type for numbers

By default in VBA it is not mandatory to declare variables, but this helps in the code performance and in the accuracy of the results.

The table below shows the main data types along with their data entries, byte allocations, and ranges:

All variables with an undeclared type are considered Variant. This sorting of data requires more of the computer that will run the code.

It is a good practice to accurately declare the data type of all variables, not only for optimization, but also because it is a necessary practice in some other programming languages.

The Decimal type can not be declared, although it exists. You can only access it through an association or conversion (with CDec, which will be seen later).

Dim text As String
Sub Exemple1()
    text = "A text"
End Sub
Sub Exemple2()
    MsgBox text
End Sub

Public globalText as String

Assigning Values - Inputbox

You can assign a value to a variable after declaring it.

In VBA the values are assigned from right to left($\leftarrow$), using the equal sign (=).

    VarText = "SuperExcelVBA"
    VarNumber = 10
    MsgBox VarText
    MsgBox VarNumber

One way to get values from users is through the Inputbox.

  VarName = Inputbox("What is your name?")
  Msgbox VarName

Try to run the code below to see what happens!

  Sub experiment()

      VarNome = Inputbox("What is your name?")
      Msgbox VarNome

  End Sub

Using the same concept of association (from left to right), we can perform operations. Code execution always occurs from top to bottom.

    VarNumber = 5
    VarNumber = VarNumber + 7 
    Msgbox VarNumber

As a result from running the code above we see that VarNumber equals 12.

VBA programming should not be confused with mathematical equations. The example above would be equivalent to VarNumber $\leftarrow$ 7 + VarNumber. Since VarNumber had the value 5 assigned to it, the second assignment of VarNumber would be equivalent to VarNumber = 7 + 5, resulting in 12.

Automatic Data Conversion

It is often possible to associate the content of one data type with another when declaring variables. However, VBA will attempt to convert the content to the declared data type (E.g. a number as text). Failure to do so will return an error.

    Dim VarText As String
    VarText = 1

The VarText above will be a text (and you will not be able to perform mathematical calculations with it).

    Dim VarNumber As Integer
    VarNumber = "Two"

VBA will try to assign "Two" to VarNumber performing the automatic conversion, but it will return an error:

In order for the conversion to work the Assassination should be done with "2" (instead of "Two").

When debugging a code, try to use the Locals Window in which all data types are explicit.

Display Translation

Boolean variables only accept the values True and False. However, if your system language is not English, a translated equivalent may be displayed in message boxes:

Although the MsgBox displays "Verdadeiro" (which is the Portuguese-BR translation for true), in the code the value remains as True.

Consolidating Your Learning

Suggested Exercise

• Previous page
• Next page

• report error/feedback

SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.

Excel ® is a registered trademark of the Microsoft Corporation.

© 2024 SuperExcelVBA | ABOUT