VBA – 1. variables, modules, objects, methods Copying Pasting

variable

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.

Syntax

In VBA, you need to declare the variables before using them.

types of variable

integer, string, boolean, variant, date

vbaDT

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

Object Variables

subroutines or procedures

VBA Module?

Standar modules

sheets modules

form modules

class modules

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.

1

[using private module variable outside its module ]

In below example we are using these variables from a different module, there fore it shows error

2

SCOPE  of module level sub procedures

Private sub declared inside a module can be called from other’ sub’s as shown in below example

3

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.

4

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.

5

Note writing just a sub name is same as writing it wih public keyword INSIDE A STANDARD MODULE. for example

Sub test()

End sub

IS SAME as

Public sub test()

END SUB

LOCAL VARIABLES

Local variables can only be used inside a sub. If used in different sub, it will show error.

6

OPTION EXPLICIT

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

7

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

8

In the below image, the program runs fine because we have declared variable var1

9

Object Variables

what are Objects?

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.

Wha are Properties?

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…

Sub sbExampleRangeProperties()

Range(“A1”).Value = 25

Range(“A1”).Interior.ColorIndex = 5

End Sub

What are Methods?

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

Declaring object variables

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

ws2.Range(“A1:A3”).Copy Destination:=ws1.Range(“C2”)

Leave a Reply