To have better understanding in variables lets take look below example:

Option Explicit

Sub example1()


End sub

Sub example2()

Dim myName as String

myName = “Hisyam” ‘Variable declaration


End sub

Open your Excel then VBE and insert module, copy paste the above code into this module and try to run both example1 and 2. There is no different in between because end result is the same. The different example1 we use constants and example2 we use variable to declare our name with myName. If you notice when we use constants we need to use quote before and after but not for variables. Use must remember this rule before get started because variables is more effective in repeating process like looping and for next.

Constant = MsgBox(“Hisyam”) with quote before and after.
Variable = MsgBox(myName) without quote.

Click here if you have any new project! Let us create for u for free.
Good luck

Basically same with Visual Basic when declaring variables, you usually use a Dim statement. A declaration statement can be placed within a procedure to create a procedure-level variable. Or it may be placed at the top of a module, in the Declarations section, to create a module-level variable. Another method is to use Public statement to make this variable available to all procedures in the project.

For Examples:

Dim x as Integer (Commonly use)
Public x as Integer

x is our variables and the data range is in between -32,768 to 32,767 (Integer) which is reserve by computer to store x. Meaning if your data is more or less when you run this macro your computer either ignore or error message to trigger value is out of range.

To have clearer picture lets try below example:

Sub CalculateAndB()

Dim ColA as Integer
Dim ColB as Integer
Dim Result as Integer
ColA = Range(“A1”).Value
ColB = Range(“B1”).Value
Result = ColA*ColB ‘Multiply value of Range A1 and B1
Msgbox (Result)

End Sub

Now copy the above code into your module and try.

1. Input 8 into Range A1 and 100 into Range B1 and Run the macro
Your message box will prompt 800 (No problem because still within integer range)
2. Input 80 into Range A1 and 1000 into Range B1 and Run the macro
You will receive error message “Run time error 6 Overflow”. This is because after multiply result will be 80,000 (Out of integer range)

From the above example you should declare “Result” as Long instead of integer because Long can accept data range in between -2,147,483,648 to 2,147,483,648

Repeat the above testing but this time changed
Dim Result as Integer ---à Dim Result as Long

Sometimes we tend to forgot to declare if we have too many variables. As a reminder you can use “Option Explicit” statement above Sub or 1st line in a module. This will force you declare.

Click here if you have any new project! Let us create for u for free.

The opposite of a variable is a constant. Constants are values that never change. Because of their inflexibility, constants are used less often than variables in programming. Therefore when we need data which always change we need to use variables for example this expression X + Y. X and Y are variables. Normally we use variables when we know the data range only for example user input data, calculation and etc.
Below Table is to show what data type to declare and what range it will be. 1st Table is for Numeric (Numbers) and 2nd Table is for Non Numeric (Other than numbers).

Numeric Data Types

Non numeric Data Types

Every variable has a name, called the variable name, and a data type. A variable's data type indicates what sort of value the variable represents, such as whether it is an integer, a floating-point number, or a character. Variable name is not limit to X and Y only but you can use any name up to 255 characters if I’m not mistaken.

If you go to help in VBE the definition of variable as below:

Variable is a named storage location that can contain data that can be modified during program execution. Each variable has a name that uniquely identifies it within its scope. A data type can be specified or not.
Variable names must begin with an alphabetic character, must be unique within the same scope, can't be longer than 255 characters, and can't contain an embedded period or type-declaration character.
Actually you can use variable without specified data type but you have disadvantages:

  1. If you have very long code then it will be very difficult for you to do the editing because you do not know which variables belong to which function.
  2. Your program will be slower because it will consume your memory. If I’m not mistaken data type will be assume as variant (Highest range).
  3. Affect your program accuracy because sometime you only need only numbers but end result may be not numbers.
Therefore please consider to declare your data type for variables when you start writing.
Good Luck
Click here if you have any new project! Let us create for u for free.

As you know recording function in excel have limitation. Therefore you must learn Visual Basic programming language and the most importance part in programming is variables because we can do almost everything by applying these variables in programming. Without variables for sure your programming code will be very long and speed will be very slow. The definition of variables is a way of referring to a memory location used in a computer program. This memory location holds values- perhaps numbers or text or more complicated types of data like a payroll record.
In Operating Systems programs load into different parts of RAM so there is no way of knowing exactly which memory location will hold a particular variable before the program is run. By giving a variable a symbolic name like "employee_payroll_id" the compiler or interpreter can always work out where to store the variable in memory. Every variable has a name, called the variable name, and a data type. A variable's data type indicates what sort of value the variable represents, such as whether it is an integer, a floating-point number, or a character. The above definitions actually was taken from another website but it seem to complicated to understand. Don't worry about that because we will understand better when you practice more and more.
Basically we have two type of variables

  1. Numeric (Numbers)
  2. Non Numeric (Other than numbers)
We will explain in detail later in our next post.
Good Luck
Click here if you have any new project! Let us create for u for free.

Actually the best training tools so far is record function in Excel. You can use this record function and playback again and again. After recording as you know you can view recorded code inside Visual Basic Editor Module. Therefore what you do is to record, view code and base on your recorded step understand these codes. If you kept trying and do trial and error of course one day you’re able to write your own code with simplicity compare with your recorded code because after recorded you’re are able to remove which code need or not. Now let try below code. Open your Visual Basic Editor and insert Module copy below code and paste inside.

Sub Format1()
' Format1 Macro
' Macro recorded 8/9/2010 by
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
ActiveWindow.SmallScroll Down:=18 ‘To scroll down
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
ActiveWindow.SmallScroll Down:=-30 ‘To scroll up
End Sub

Actually the above code was recorded base on the step below:

  1. Select Range (B2 and D4)
  2. Fill this range with yellow colored
  3. Scroll down
  4. Select Range (B20 and D22)
  5. Fill this range with red colored
  6. Scroll up
  7. Select Range A1
What do you think about your recorded code? Why we have so many scroll down, what happen if we delete? The answer if we delete nothing happen because scroll down is not compulsory step to completed the process. Therefore to simplify just remove scroll down code but end result remain the same.
Good Luck
Click here if you have any new project! Let us create for u for free.

The smallest component in excel is cell follow by range, worksheet and workbook but the most importance is cells itself. If you notice in office 2003 excel column is limit to 256 and row limit to 65536 but more in office 2007. Meaning every sheets we only have 256 x 65536 cells. For column number started from 1 to 256 from left to right but by default A to IV unless you tick R1C1 reference style under Tools->Option->General Tab and for row number start from 1 to 65536 from top to bottom. Therefore before trying to write your own code please remember this otherwise you get stuck in your programming due to this limitation.
The question now how to select this cells or range effectively using VBA? Actually there are many ways to apply depending on your program requirement. The methods you can apply as below:

  1. Single cell
    • Range("A1").Select
    • Cells(1,1).Select
  2. Range (Multiple cells)
    • Range("A1:B2").Select
    • Range(Cells(1,1),Cells(2,2)).Select
  3. Rows
    • Rows("1:2").Select
    • Rows(2).Select
  4. Columns
    • Columns("A:B").Select
    • Columns(2).Select

After select (Method if you still remember) then you can change this cells or range properties depending on your program requirement for examples border, fill color and etc.

Click here if you have any new project! Let us create for u for free.

The definition given by Microsoft for:

  1. Object
    An object represents an element of Excel for examples workbooks, worksheets, range, cells and etc.Visual Basic actually object orientation programming language, nearly everything you do in Visual Basic involves modifying objects. There are four Excel objects you will work with more than any others: the Application object, the Workbook object, the Worksheet object, the Range object. The above sequences actually top to down object hierarchy.
  2. Properties
    A property is an attribute of an object or an aspect of its behavior, for example when we refer to Range object it should be border, fill color and etc.
  3. Methods
    A method is an action that an object can perform, for example when we refer to Range object action can perform should copy, cut and paste, delete and etc.
I think the above explanations is clear and easy to understand but don’t worry you will be able to understand when you continue try and error. The more you record the more you understand the coding. To be more clear understand refer below code and try by yourself.

Sub SelectOjectAndChangeProperties ()

Selection.Interior.ColorIndex = 3

End sub

The above code will select (Method) Range A1 and A2 (Object) and change Interior color index (Property) to red.
Copy the above into your modules and try it but don’t forget to enable your macro and set your security to medium.

Click here if you have any new project! Let us create for u for free.