Variables and Data Types in Excel VBA

Home/Excel VBA/Variables and Data Types in Excel VBA

Variables can store the information required to use in our programming. When we are working with data we deal with different type of data,so we need required different types of variables to store the data. In this session will discuss how to declare a variable and different types of variables available in VBA.



50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates

How to Declare a Variable

We need to use Dim or Dimension statement to declare a variable in VBA as shown below:

Dim <Variable Name> as <Data Type>

Variable Name: Name of the variable which we want to declare, follow the following rules while choosing a variable name:

  1. 1. It must be less than 255 characters
  2. 2. Blank Spaces and special characters are not allowed
  3. 3. It must not begin with a number (it should begin with a character or an underscore)
  4. 4. You can not use reserved keywords:

And As Boolean ByRef
Byte ByVal Call Case
CBool CByte CDate CDbl
CInt CLng Const CSng
CStr Date Dim Do
Double Each Else ElseIf
End EndIf Error FALSE
For Function Get GoTo
If Integer Let Lib
Long Loop Me Mid
Mod New Next Not
Nothing Option Or Private
Public ReDim REM Resume
Select Set Single Static
Step String Sub Then
To TRUE Until vbCrLf
vbTab With While Xor
Some Valid Example of Variable Names:


Some Invalid Example of Variable Names:

int Sal

Tip! Variable name should convince the data and data type, so that user can quickly understand the variable

Data Type: We can devide the data types in to 2 parts for our understanding purpose.

1. Numeric Type:

The following are the data types to deal with numeric data (byte type data,integer data,double data, etc…)

Data Type Name Type Data Range and Remarks
Byte Numeric Whole number between 0 and 255.
Integer Numeric Whole number between -32,768 and 32,767.
Long Numeric Whole number between – 2,147,483,648 and 2,147,483,647.
Currency Numeric Fixed decimal number between -922,337,203,685,477.5808 and 922,337,203’685,477.5807.
Single Numeric Floating decimal number between -3.402823E38 and 3.402823E38.
Double Numeric Floating decimal number between -1.79769313486232D308 and 1.79769313486232D308.

2. Non-Numeric Type

The following are the data types to deal with non-numeric data (string type data,date type data,time type data, etc…)

Data Type Name Type Data Range and Remarks
String Text Text.
Date Date Date and time.
Boolean Boolean True or False.
Object Object Microsoft Object.
Variant Any type Default type if the variable is not declared with any data type. It will accept any kind of data.

And the variant data type will accept any type of data:

Data Type Name Type Data Range and Remarks
Variant Any type Default type if the variable is not declared with any data type. It will accept any kind of data.
Variables and Data Types in Excel VBA – Example Programs

The following example adds two integers:

Sub sbAddTwoValues()
'Variable Declaration
Dim intValuA As Integer
Dim intValuB As Integer
Dim intValuSum As Integer
'Initiating the Values
intValuA = 5000
intValuB = 10000
'Calculating the Total
intValuSum = intValuA + intValuB
'Showing the result in the message box
MsgBox intValuSum
End Sub

The following example accepts the user name (string) and show it in the message box

Sub sbStringExample()
'Variable Declaration
Dim strUserName As String
'Accepting the data from the user
strUserName = InputBox("Enter Your Name")
'Showing it again in the message box
MsgBox "Hello!" & strUserName
End Sub
By |July 14th, 2013|Excel VBA|1 Comment

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

One Comment

  1. shubam April 24, 2015 at 1:24 PM - Reply

    when I trying to retrieve data from 1000th cell and used long datatype then why my excel sheet hanged and not responding
    for example
    dim I as long
    for I = 1 to 1000

Leave A Comment

Related pages

gantt chart templates excelexcel formula for difference between two datesaccess vba delete recordsql pivot table tutorialexcel advanced learningvba code in excel examplescheckmarks in excelbreak excel password protectioncreate checklist in excelwindows.activate vbadelete cells vbadashboard microsoft excelloop through worksheets vbaexcel vba write to text filesas analytics tutorial pdfexcel remove repeatsauto filter definitionexcel unhide all columnsmsgbox vbcriticaliferror excel 2010excel data validation if statementexcel adding rows formularefresh data in pivot tablehow to create check boxes in excelcreate a checkbox in excelremove empty cells in exceleasy vlookup examplevba combobox selected itemexcel macro for dummiesexcel spreadsheet dashboardvlookup formulaswizard in excelexcel vba delete columnsredim preserve array vbawhat if statements in excel 2010excel data mining tutorialmerging 2 columns in excelexcel spreadsheet test for interviewvba workbook activateexcel macro if cell containshow to change width of a column in excelexcel substitute formulaexcel vba workbook openvba word examplesvb interview questions and answersadodb connection vbaddl and dml commandsvlookup excel 2003 examplebasics of sas programminglen function excelsample gantt chart templatedonut chart generatorchange rows to columns in excelshortcut key to delete sheet in excelvlookup simplebest excel dashboard templatesexcel vba commandscapacity planning template in excel spreadsheetvba instr exampleexcel charts examplesshow developer tab excel 2010how to select multiple rows in excelvba activex controlsmessage box vbams access vba tutorial pdfhow to add the developer tab to the ribbonunlock worksheetlistbox columnmacro recorder excel 2007gantt project excel templateconvert from excel to xmlvba simple examplesdownload adodbsample mpp project plantcs c interview questions and answers pdfmacro to insert rows between dataexcel unhide first columnvba code for excelchange width of column excelhyperlink to excel cellv lookup tutorial