Type of data variables used in Excel VBA ?

Excel VBA uses variables just like any other programming language. Learn how to declare and initialize an excel vba variable of type Integer, String, Double, Boolean and Date.

We will learn how to declare, initialize and display an Excel VBA variable. A variable is used to store a value. A variable can be of any type. In Excel VBA, we have variables of type Integer to store whole numbers, variables of type Double which can also store numbers after the comma, variables of type String to store text, variables of type Boolean to hold the value True or False and variables of type Date to store dates.

Place a command button on your worksheet and add the code lines described in this chapter. To execute the code lines, click the command button on the sheet.

Variable of type Integer

Integer variables are used to store whole numbers.
Code:
Dim x As Integer
x = 6
Range("A1").Value = x

Result:


1. The first code line 'Dim x As Integer' declares an Excel VBA variable with name x of type Integer. Letting Excel VBA know we are using a variable is called declaring a variable.

2. Next, we initialize the variable. In Excel VBA (and in many other programming languages), initializing simply means assigning a beginning (initial) value to a variable. This is done by adding the line 'x =6'.

3. Finally, we place the value assigned to the variable x into cell A1. This can be done by adding the line: 'Range("A1").Value = x'.


Variable type - String

String variables are used to store text.

Code:
Dim book As String
book = "History"
Range("A1").Value = book

Result:

1. The first code line 'Dim book As String' declares an Excel VBA variable with name book of type String.

2. Next, we initialize the variable. For example, add the line: book = "History". Always use apostrophes to initialize String variables.

3. Finally, we place the text assigned to the variable book into cell A1. You can achieve this by adding the line 'Range("A1").Value = book'

Variable type  - Double

A variable of type Double is more accurate than a variable of type Integer and can also store numbers after the comma.

Code:

Dim x As Integer

x = 5.5

MsgBox "value is " & x

Result:

But that is not the right value! We initialized the variable with value 5.5 and we get the value 6. What we need is a variable of type Double.

Code:

Dim x As Double

x = 5.5

MsgBox "value is " & x 

Result:

You might think, why we should use Integer variables, if you could use the more accurate Double variables. That is because Double variables, and even more accurate variables, need more space and as a result your code will run slower (as program size increases). Apart from this, you will see that errors are easier to find when you use variables of the right type.

Variable type  - Boolean

In Excel VBA, you can use Boolean variables to hold the value True or False. 

Code:

Dim continue As Boolean

continue = True

If continue = True Then 

MsgBox "Boolean variables are Great"

Result:

1. The first code line declares a variable of type Boolean.

2. Next, we initialize the Boolean variable with value True.

3. Finally, we can use the Boolean variable to only display a MsgBox if the variable holds the value True.

Variable type - Date

To declare a date, we use the Dim statement. To initialize a date, we use the DateValue function.

String Manipulation in Excel VBA: 

There are many functions in Excel VBA we can use to manipulate strings. Below are few of the most important functions.

Place a command button on your worksheet and add the code lines described in this chapter. To execute the code lines, click the command button on the sheet.

Join Strings

We use the & operator to concatenate (join) strings.

Code:

Dim text1 As String, text2 As String

text1 = "Hello "

text2 = "Ravi"

MsgBox text1 & text2 

Result:

Left

To extract the leftmost characters from a string, use Left.

Code:

Dim text As String

text = "Innovatively done"

MsgBox Left(text, 4)

Result:

Right

To extract the rightmost characters from a string, use Right. We can directly insert text in a function as well.

Code:

MsgBox Right("Innovatively done", 4)

Result:

Len

To get the length of a string, use Len.

Code:

MsgBox Len("Innovatively done")

Result:

Position 17 in above text i.e. space is included in the count!

Instr

To find the position of a substring in a string, use Instr.

Code:

MsgBox InStr("Innovatively done", "vat")

Result:

string "vat" is found at position 5 in the above sample text.

Mid

To extract a substring, starting in the middle of a string, use Mid.

Code:

MsgBox Mid("Innovatively done", 14, 4)

Result:

The text started at position 14 with length 4 in above sample text.