MS Excel VBA Variable

MS Excel VBA Variable Types: Integer, String, Double and Boolean

Variables are the temporary memory blocks reserved by a program to store a general or specific type of value. Like any other programming language, MS Excel VBA also support different variables for data management and manipulation.

In this tutorial, you will observe the implementation of some commonly used variable types in MS Excel VBA.

Declaring a Variable

In order to use a variable, first you need to define a variable or in programming term, “Declare” a variable. All programming languages have different keywords for declaring variables and likewise MS Excel VBA uses the keyword “Dim” for variable declaration.

By only declaring a variable doesn’t work. To make it work, you need to initialize this variable by providing an initial value.

Syntax for declaring a variable and assigning an initial value is as follows:

Dim intAge as Integer

intAge = 10

In the above syntax, the first raw is declaring a variable called as “intAge” which is intended to store the age value. The word Dim is used for declaring the “intAge” variable followed by “as Integer” keyword which defines the type of data that is allowed to be stored in this variable.

In second line of code, an initialization value 10 is assigned to intAge variable.

Note that intAge is user defined and hence it can be any Alphanumeric character set. It is a normal practice of IT Professionals to name variables in a manner that identifies both the type of variable as well as nature of value it is going to store. For instance, in above syntax, intAge identifies that the variable is going to store Age value which will be in integer (numeric) form.

Integer

Integer type variables store whole numbers.

Explanation: On click of command button, the program declared an integer type variable named intAge and assigned an initial value of 10. The event further placed the value intAge into the cell A1 of the active sheet.

String

String variable stores alphanumeric and special characters as value.

Double

Variable type Double is more authentic and accurate in comparison with the variable type Integer. If you enter a fractional value in integer, it will be rounded to nearest whole number and hence results will not be accurate, therefore, to get an accurate fractional value, you will use double as variable type.

Boolean

Boolean type of variables holds either True or False as values.

Explanation: In the above code, two variables, intMarks and bolPass are declared as Integer and Boolean respectively. Next, an initial value is assigned to intMarks followed by an If condition statement which set a True value to bolPass variable if the intMarks is greater than 60.

Now since the value of intMarks is 80 which is greater than 60 therefore, the conditional if statement set the bolPass value to True, which is then displayed in Cell A1.