Variables declaration

In this article we are going to learn how to declare a variable in VBA Excel. First we are going to learn what a variable is and then what are some of the types of variable and finally we are going to learn how to initialize and use variables in Excel VBA.

 

How to declare variables?

A variable is simply used to store temporary information that is used in the execution of a process, procedure, workbook or a module.

There are a few rules you have to keep in mind when naming your variables:

  1. Variable name must start with a letter and not a number (number can be used but not as first letter in a variable name)
  2. Variable name must not be a keyword(predefined word)in Excel VBA
  3. Variable name must be <250 characters always

Now let’s see some of the types of variables

A variable can be of these types

  • Integer (positive numbers and negative numbers)
  • String (numbers, letters, special characters like @)
  • Char (letters only)

Now let’s learn how to declare a variable. Follow the steps to declare a variable in Excel VBA.

Note: you can skip STEP1 and STEP2 if the developer tab is already open.

STEP1

First you need to open the developer tab. To do this right click anywhere on the ribbon and select "Customize the Ribbon" from the drop down menu.

Customize The Ribbon

STEP2

An option dialog box will open. Click on the DEVELOPER check box (it is under "Customize the Ribbon Main Tabs") and press ok.

Add Developer Tab

STEP3

The Developer tab is now open and is present next to the view.Click on the developer tab and select "View Code" from the ribbon.

View Code

STEP4

A new window will open (Visual Basic Editor) which would have a dialogbox in the center.You will declare the variable in this dialogbox

New VBA Code

STEP5

Now you can declare a variable. To declare a variable use the keyword "Dim" (short for dimension) followed by a variable name then the word "As" followed by a variable type. For example:

 Dim myword As String 

Note: dim is a keyword used to declare the variable. "myword" is the name of my variable. "String" is the type of my variable

IMPORTANT: ALL VARIABLES MUST BE DECLARED BETWEEN Private Sub and End Sub. OTHERWSE IT WILL NOT WORK

Variable Declaration

STEP6

You can also declare a variable of type int. Like this:

Dim myword 
As Integer 

Variable Declaration Integer

STEP7

You can also declare multiple variables. Like this:

Dim myword,myword2,myword3 as Integer

Note: "myword" is the name of my first variable, "myword2" is the name of my second variable and "myword3" is the name of the third variable. "String" is the type of all these variable.

Few Mywords

STEP8

You can also declare multiple variables of different types. Like this:

Dim myword As Integer 
Dim myword2 As String 

Few Mywords Plus Types

STEP9

(optional) You can use the "Public" keyword to declare public module-level variable.

 Public myword As Integer

Note: you have replaced the keyword "Dim" with the keyword "Public".

Public variables can be used in any of the Excel VBA procedure

Declare Public Keyword