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.
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. It must be less than 255 characters
- 2. Blank Spaces and special characters are not allowed
- 3. It must not begin with a number (it should begin with a character or an underscore)
- 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:
intSal
Company_Salary
int_Salary
Sal_Dept_88
Some Invalid Example of Variable Names:
int Sal
Company&Salary
int.Salary
88Sal_Dept
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
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