variable holds information recognized by system
Variable is a named memory location used to hold a value that can be changed during the script execution. Following are the basic rules for naming a variable.
In VBA, you need to declare the variables before using them.
types of variable
integer, string, boolean, variant, date
1 Byte= 8 bit
All information on computer system is saved in the form of 0 and 1, this is called bit. A bit could either have 1 or 0
1 byte=8 bits, so 1 byte can have 2 raise to power 8 =256 combination of 0 and 1
2 byte=16 bits can have s raise to power 16 =65536 combinations of 0 and 1
So for Integer has 2 bytes in above table, which means it will have 2 raise to power 16 combinations , ie 65536 combinations which is divided in equal distribution of negative and positive integers.
So if you try to store more than 32767 in an integer variable it will error out because it can only store the max 32767. If you need more than this use long variable
subroutines or procedures
SCOPE of module level VARIABLES
Module level variables are declared at the top (declaration) section.
Private varaibles are accessible from any sub inside a module. These are not accessible from another module or from other sheet modules. In beow example we have psv and piv as private module level varaibles. Sub ps2 can use these variables since they are in same module.
[using private module variable outside its module ]
In below example we are using these variables from a different module, there fore it shows error
SCOPE of module level sub procedures
Private sub declared inside a module can be called from other’ sub’s as shown in below example
Since Private sub can only be called from its module only, if you call from outside its module it shows error like below. We have called it from Sheet module.
SCOPE of project level variables and sub procedures
A public variable or public sub can be used from any module sheet or form. In below example we can use public variables or sub outside its modules.
Note writing just a sub name is same as writing it wih public keyword INSIDE A STANDARD MODULE. for example
IS SAME as
Public sub test()
Local variables can only be used inside a sub. If used in different sub, it will show error.
In VBA, we can use a variable without declaring (we declare a variable with dim statement) . For example in the below code, we are showing variable var1 in message box. This variable is not declared inside sub. If you run this program it will run correctly
HOWEVER if you use option explicit at the top of a module, you must declare a variable to use it, in the below image we are using variable var1 without using it, so it shows error
In the below image, the program runs fine because we have declared variable var1
Most of the programming languages today are Object Based Or Object Oriented Programming Languages. Although Excel VBA is not a truly object oriented programming language, it does deal with objects.
VBA object is something like a thing that has certain functions, properties, and can contain data or child objects.
In real world everything is an object. For example, House is an Object, Windows and Doors,etc… are child objects of the House. And House is having some characteristics or properties such as Color, Height, Number of Floors,etc.., and it also have some Events, such as Door Open, Door Close, etc….
Similarly, An Excel Worksheet is an object, and a Range or Cells in a worksheet are child objects of worksheet, Worksheet contains several Properties, Methods and Events.
You can go to the code window to view the VBA objects, the upper left drop-down list of the code window contains the list of objects and the right side drop-down list contains the associated objects.
Properties are the characteristics of an Objects which can be measured and quantified, in the above example House is having properties like Width, Height, Color, etc…
Similarly, Excel Objects are having several properties which can be measured and quantified
or example, a Range Objects is having Properties like Value,Font.ColorIndex, Interior.ColorIndex,etc…
Range(“A1”).Value = 25
Range(“A1”).Interior.ColorIndex = 5
Methods are the actions that can be performed by an an Objects or on an Object. In the above Hose example, paintaing is a Method, building a new room is a method.
Similarly, if you want to select a range, you need Select method. If you want to copy a range from one worksheet to another worksheet you need Copy method to do it.
The following example Copies the data from Range A1 to B5.
Sub sbExampleRangeMethods()Range(“A1”).SelectSelection.CopyRange(“B5”).SelectActiveSheet.PasteEnd Sub
Workbooks, worksheets, ranges all are object variables. These varaibales are declared like normal varaibles, however to use them we have to always use a Set keyword, because we have to set a reference to objects.
Dim wb as workbook
Dim ws as worksheet
Dim r as range
Set wb= thisworkbook
(this workbook is the current workbook you work with, which contains code)
Set ws= wb.Sheets(“sheet1”) (this sets the reference of ws variable to Sheet1 in our current workbook
Set r= ws.range(“A1”) (this sets the reference of r variable to range in current workbooks, Sheet1
Setting References to different Sheets
Dim r1 As Range
Dim r2 As Range
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = ThisWorkbook.Worksheets(“Sheet1”) ‘Reference to Sheet1
Set r 1= ws.Range(“A1:A3”) ‘Reference to Range in Sheet1
Set ws2 = ThisWorkbook.Sheets(“Sheet2”) ‘Reference to Sheet2
‘Copying r1(range1) to range c1 in Sheet2
‘r1.Copy Destination:=ws2.Range(“c1”) ‘Reference to Range in Sheet1
‘Copying range A1:A3 from sheet2 to range cc in Sheet1