Excel vba variables - How to use variables in excel vba

A Variable is a small chunk of computer’s memory used to store a value.

You can name variables with any valid name you wish. For Example you could name a variable "David" and then declare it as any one of the data types shown below. However, it is good practice to formalize some sort of naming convention. This way when reading back your code you can tell at a glance what data type the variable is. An example of this could be the system I use! If you were to declare a variable as a Boolean (shown in table below) I may use: bIsOpen I might then use this Boolean variable to check if a Workbook is open or not. The "b" stands for Boolean and the "IsOpen" will remind me that I am checking if something is open. 

Why we use variables

Excel will still allow us to run our code without using variables, it is not a must! But having said this it is very bad programming to not use variables. You could quite easily just assign a value, string or whatever each time you need it, but it would mean: 

1) Your code would become hard to follow (even for yourself) 
2) Excel would constantly need to look for the value elsewhere. 
3) Editing your code would become awkward. 

Variables can be declared as any one of the following data types:

1.Integer

2.String

3.Boolian

4.Single

Example: Integer

1.Integer can store numeric value that are assigned (-32768 to 32768).

Option Explicit

Sub Button1_Click()

Dim I As Integer

I = 100

Range("A1:A10").Value = I

End Sub

Out put.

Example: String

1.string are used to store characters or text.

Option Explicit

Sub Button1_Click()

Dim I As String

I = "Hellow world"

Range("A1:A10").Value = I

End Sub

Out put.

Example: Boolian

Boolian always output two value i.e True or False

Option Explicit

Sub Button1_Click()

Dim I As Boolean

I = True

If I = True Then

MsgBox "Welcome to Myworkbook.in"

Else

MsgBox "Please try again"

End If

End Sub

Out put

Explanation: the first code line declares a variable with name continue of type Boolean. Next, we initialize continue with the value True. Finally, we use the Boolean variable to only display a MsgBox if the variable holds the value True.

Example:Single

A Single variable can hold a number between 1.401298e^–45 and 3.402823e³⁸. for negative values or between 1.401298e^–45 and 3.402823e³⁸ for positive values.

try this code.

Option Explicit

Sub Button1_Click()

Dim I As Single

I = 10 / 3

MsgBox I

End Sub

Out put.

if you use integer instead of single u see the different

Option Explicit

Sub Button1_Click()

Dim I As Integer

I = 10 / 3

MsgBox I

End Sub

Out put

Do you like this page.? Please register your Email Id for News Letter..!!