How to Declare Variable in Excel VBA

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 some of the types of variable are. Finally, we are going to learn how to initialize and use variables in Excel VBA.

Follow the steps to declare a variable in Excel VBA.

The ribbon

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

An option dialog box will open. Click on the DEVELOPER check box (it is under “Customize the Ribbon Main Tabs”).

Add Developer Tab

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

The code

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

New VBA Code

The declaration

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

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

See also  Select Case Statement in Excel Vba

Dim myword As Integer

Variable Declaration Integer

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 variables.

Few Mywords

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

Dim myword As Integer
Dim myword2 As String

Few Mywords Plus Types

(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