Skip to main content

New Article

How to SUM by matching partial text in Excel

Variables in Excel VBA

First of all you need to clear what is Variable? Lets see from your end. In formula while you use =A1, then A1 is the variable name which contains some values. In computer programming sometimes you need to tell the computer to keep a calculated value in memory for later use. This calculated value your computer will store in a variable name which you will mention with a Dim command generally. Dim is the abbreviation of "Dimension". For example:

Sub keepinMemory()
    Dim ValueInMemory as Text
    ValueInMemory = "Hello World"
End Sub

This command will set a value "Hello World" in ValueInMemory word. If you write ValueInMemory, then computer will read "Hello World". This is the example of Variable.

Image 1: Variables and Values


Types of Variable?

Variable are different types, such as String, Integer, Long, Date, Double, Object etc. It depends on which thing you want to add in memory. If it is a text, then use String, if it is a number then use Integer or Long etc. These are generally classified are into 2 main groups. Numerical and Non-Numerical. Below table shows the different types of Numerical Variables and their memory range:

Image 2: Different types of Numerical Variable

Below table shows the different types of Non-Numerical Variables and their memory range:

Image 3: Different types of Numerical Variable



Rules of writing variable names:

Simple you just know below 3 things:
  • You must use alphabetical characters first then numbers or underscore. If you start naming by using first number or underscore character then it will detect as wrong.
  • You are not allowed any wildcard characters.
  • Maximum 40 characters are allowed to naming a variable.
  • You are not allowed to use any space or full stop.
  • Do not use number and wild card characters in the begining (Example: "$vname", "3vname")

Example of Variable:

To store the values in a variable first declare the variable name with a DIM statement. It is highly recommended that use DIM statement to declare the variable name. You can also declare a variable by not using DIM command. In below example I'v written simple codes, that will help you about variable declaring procedures and obviously how to call the variables to get the values from it:

Sub txt1()
Dim variabletest As String
     variabletest = "Learn Variable to grow up your Excel VBA skill"
     Worksheets(1).Range("D5").Value = variabletest
End Sub

The above code saved under txt1 macro. Dim declared a variable name variabletest. Then a long text value within "" (double quotation mark) is stored under variabletest variable. Finally in Worksheet1 D5 cell the value will paste which is set under variabletest.


Why should use DIM statement before declaring Variable?

To declare the variable, you should use DIM statement or if you wish you do not need to use DIM statement. Obviously you can use with DIM statement as below:

Sub txt1()
     variabletest = "Learn Variable to grow up your Excel VBA skill"
     Worksheets(1).Range("D5").Value = variabletest
End Sub

Obviously the above Variable declaring method without DIM statement will also work. But the benefit of using DIM statement is, you are telling the computer about variable name and what types of data will store under this variable such as Date, Integer, Long, String etc. which can calculate faster.


About Option Explicit:

At the beginning of your VBA programming if you use this Option Explicit code, then you must declare all the variables name with a Dim statement on that module. This is a good habit to use this Option Explicit code to resolve errors while running this code. For example, see the below image:

Image 4: Option Explicit

Option Explicit code was already in the top and only one variable has declared vbname. But while vbx used as a variable but not declared yet, then the Option Explicit detected Error while running it.

Image 5: Error while running a variable without Dim statement

The error message informing us that an undeclared variable found somewhere in your code, a variable not set up with the Dim keyword. So it's good to type Option Explicit at the top of your code window in order to prevent variables being set up accidentally.

Popular posts from this blog

How to display an image in worksheet based on a List or based on IF condition?

Excel can show image on worksheet based on a specific IF condition. So, how to do it? Simple follow the below steps: Step by Step: Step 1: Insert images in your Excel Worksheet. Here I've inserted 5 different types of balls, Football, Cricket, Pool, Basketball, Tennis ball. Note that, All balls are placed into different cell. These are placed in Picture sheet. Image 1: 5 balls placed in 5 different cells and covered photo's wide and height Step 2: In the report sheet, design the report as you wish. I've designed in my way like below: Image 2: Kids asking to Donald Duck, which ball need to throw now Step 3: Make a drop down list "Games" in E6 cell in Report sheet from Data Validation. which is as below: Football Cricket Pool Basketball Tennis You can do an IF function here in E6 in Report sheet, which will meet a certain condition and returned Football, Cricket, Pool, Basketball or Tennis. Step 4: Now the tricky part is...

Cumulative Closing Balance like a Bank Statement in PivotTable

A Bank Employee in many case needs to calculate the Closing Balance after each transaction in PivotTable like a Bank Statement . But with the help of Calculated Field of a PivotTable , you can only calculate Field with Field . It's not possible to use a formula in Calculated Field where you can mention a single cell with Relative Reference Cell . Because a PivotTable acts like a Table format where you can work with Field or Column Name . A Helping Column (A regular column, that helps to get a partial result where formula applied) can be a good idea. But there is an option in PivotTable by which you can Calculate Cumulative . In this article I will show you, how you can do it. Calculating with Formula: Assume that, you have an Excel file with Debit and Credit transaction of an Account . Image 1: Sample Bank Statement In a Bank Statement , Month wise Cumulative Balance is important. In the Excel sheet, as above, it is very easy to use a formula and calc...

Value Paste and Formula Paste

In many case, in our professional life, we need to do Copy and Value Paste or Copy and Formula Paste in all most always. Those employees, who works under MIS Department, have to do it lots of time in a single day to prepare reports. It is very time costing task in Office. Many of us use Excel Menubar to do it, others are using Keyboard Shortcut by pressing ALT key (like for Value Paste Press ALT+H+V+V and for Formula Paste Press ALT+H+V+F ). But did you noticed that, these two ways strongly need your attention to do this task. More clearly, if you choose Value Paste from Menubar , then what you need to do? First you should take the mouse in Hand Wheel the mouse in Home menu Then Click on Paste Then you pressure your eyes to find the Value Paste icon And then Click on it to paste it These all steps can take more than 1 second. Am I right? Below is an image of this process: Image 1: Value Paste from Home Menu On the other hand, if you choose the shortcut f...