The Basic of VBA

The Basics of VBA

Following is a summary of what VBA is all about

·         Code: You perform actions in VBA by executing VBA code. You write (or record) VBA code, which is stored in a VBA module.

·         Module: VBA modules are stored in an Excel workbook file, but you view or edit a module by using the Visual Basic Editor (VBE). A VBA module consists of procedures.

·         Procedures: A procedure is basically a unit of computer code that performs some action.VBA supports two types of procedures: Sub procedures and Function procedures.

Sub: A Sub procedure consists of a series of statements and can be executed in a number of ways. Here’s an example of a simple Sub procedure called Test: This procedure calculates a simple sum and then displays the result in a message box.

Sub Test()

Sum = 1 + 1

MsgBox “The answer is “ & Sum

End Sub.

Function: A VBA module can also have Function procedures. A Function procedure returns a single value (or possibly an array). A Function can be called from another VBA procedure or used in a worksheet formula. Here’s an example of a Function named AddTwo:

Function AddTwo(arg1, arg2)

AddTwo = arg1 + arg2

End Function

·         Objects: VBA manipulates objects contained in its host application. (In this case, Excel is the host application.) Excel provides you with more than 100 classes of objects to manipulate. Examples of objects include a workbook, a worksheet, a range on a worksheet, a chart, and a shape. Many more objects are at your disposal, and you can use VBA code to manipulate them. Object classes are arranged in a hierarchy. Objects also can act as containers for other objects. For example, Excel is an object called Application, and it contains other objects, such as Workbook objects. The Workbook object contains other objects, such as Worksheet objects and Chart objects. A Worksheet object contains objects such as Range objects, PivotTable objects, and so on. The arrangement of these objects is referred to as Excel’s object model.

·         Collections: Like objects form a collection. For example, the Worksheets collection consists of all the worksheets in a particular workbook. Collections are objects in themselves.

·         Object hierarchy: When you refer to a contained or member object, you specify its position in the object hierarchy by using a period (also known as a dot) as a separator between the container and the member. For example, you can refer to a workbook named Book1.xlsx as

Application.Workbooks(“Book1.xlsx”)

This code refers to the Book1.xlsx workbook in the Workbooks collection. The Workbooks collection is contained in the Excel Application object. Extending this type of referencing to another level, you can refer to Sheet1 in Book1 as

Application.Workbooks(“Book1.xlsx”).Worksheets(“Sheet1”)

You can take it to still another level and refer to a specific cell as follows:

Application.Workbooks(“Book1.xlsx”).Worksheets(“Sheet1”).Range(“A1”)

·         Active objects: If you omit a specific reference to an object, Excel uses the active objects. If Book1 is the active workbook, the preceding reference can be simplified as

Worksheets(“Sheet1”).Range(“A1”)

If you know that Sheet1 is the active sheet, you can simplify the reference even more:

Range(“A1”)

·         Objects properties: Objects have properties. A property can be thought of as a setting for an object. For example, a range object has properties such as Value and Address. A chart object has properties such as HasTitle and Type. You can use VBA to determine object properties and also to change them. Some properties are read-only properties and can’t be changed by using VBA.

You refer to properties by combining the object with the property, separated by a period. For example, you can refer

to the value in cell A1 on Sheet1 as

Worksheets(“Sheet1”).Range(“A1”).Value

·         VBA variables: You can assign values to VBA variables. Think of a variable as a name that you can use to store a particular value. To assign the value in cell A1 on Sheet1 to a variable called Interest, use the following VBA statement:

Interest = Worksheets(“Sheet1”).Range(“A1”).Value

·         Object methods: Objects have methods. A method is an action that is performed with the object. For example, one of the methods for a Range object is ClearContents. This method clears the contents of the range. You specify methods by combining the object with the method, separated by a period. For example, to clear the contents of cell A1 on the active worksheet, use

Range(“A1”).ClearContents

·         Standard programming constructs: VBA also includes many constructs found in modern programming languages, including arrays, loops, and so on.

·         Events: Some objects recognize specific events, and you can write VBA code that is executed when the event occurs. For example, opening a workbook triggers a Workbook_Open event. Changing a cell in a worksheet triggers a Worksheet_Change event.

Visual Basic Editor

All your VBA work is done in the Visual Basic Editor (VBE). The VBE is a separate application that works seamlessly with Excel. By seamlessly, I mean that Excel takes care of the details of opening the VBE when you need it.

When you’re working in Excel, you can switch to the VBE by using either of the following techniques:

·         Press Alt+F11.

·         Choose Developer➜Code➜Visual Basic.

In addition, you can access two special modules as follows.

·         Right-click a sheet tab and choose View Code, which takes you to the code module for the sheet.

·         Right-click a workbook’s title bar and choose View Code, which takes you to the code module for the workbook. If the workbook window is maximized in Excel, the workbook window’s title bar is not visible.

Let’s play with an example

Insert a VBA module into a project and then enter the following procedure into the Code window of the module:

Sub SayHello()

Msg = “Is your name “ & Application.UserName & “?”

Ans = MsgBox(Msg, vbYesNo)

If Ans = vbNo Then

MsgBox “Oh, never mind.”

Else

MsgBox “I must be clairvoyant!”

End If

End Sub

To execute the SayHello procedure, make sure that the cursor is located anywhere within the text that you typed. Then do any of the following:

·         Press F5.

·         Choose Run➜Run Sub/UserForm.

·         Click the Run Sub/UserForm button on the Standard toolbar.

If you entered the code correctly, the procedure executes, and you can respond to a simple dialog box.

This simple procedure uses the following concepts:

·         Declaring a procedure (the first line)

·         Assigning a value to variables (Msg and Ans)

·         Concatenating strings (using the & operator)

·         Using a built-in VBA function (MsgBox)

·         Using built-in VBA constants (vbYesNo and vbNo)

·         Using an If-Then-Else construct

·         Ending a procedure (the last line)

The macro recorder

Another way to get code into a VBA module is to record your actions by using the Excel macro recorder. Recording macros is very useful, but it has some limitations. In fact, when you record a macro, you almost always need to make adjustments or enter some code manually.

This next example shows how to record a macro that simply changes the page setup to landscape orientation. If you want to try these, start with a blank workbook:

1.                   Activate a worksheet in the workbook (any worksheet will do).

2.                   Choose Developer➜Code➜Record Macro.

Excel displays its Record Macro dialog box.

3.                   Click OK to accept the default setting for the macro.

Excel automatically inserts a new VBA module into the workbook’s VBA project. From this point on, Excel converts your actions into VBA code. Notice that Excel’s status bar displays a blue square. You can click that control to stop recording.

4.                   Choose Page Layout➜Page Setup➜Orientation➜Landscape.

5.                   Select Developer➜Code➜Stop Recording (or click the blue square in the status bar).

Excel stops recording your actions.

To view the macro, activate the VBE (pressing Alt+F11 is the easiest way) and locate the project in the Project Explorer window. Double-click the Modules node to expand it. Then double-click the Module1 item to display the code window. (If the project already had a Module1, the new macro will be in Module2.) The code generated by this single Excel command is shown below

Sub Macro1()

'

' Macro1 Macro

'

'

    With ActiveSheet.PageSetup

        .PrintTitleRows = ""

        .PrintTitleColumns = ""

    End With

    ActiveSheet.PageSetup.PrintArea = ""

    With ActiveSheet.PageSetup

        .LeftHeader = ""

        .CenterHeader = ""

        .RightHeader = ""

        .LeftFooter = ""

        .CenterFooter = ""

        .RightFooter = ""

        .LeftMargin = Application.InchesToPoints(0.7)

        .RightMargin = Application.InchesToPoints(0.7)

        .TopMargin = Application.InchesToPoints(0.75)

        .BottomMargin = Application.InchesToPoints(0.75)

        .HeaderMargin = Application.InchesToPoints(0.3)

        .FooterMargin = Application.InchesToPoints(0.3)

        .PrintHeadings = False

        .PrintGridlines = False

        .PrintComments = xlPrintNoComments

        .PrintQuality = 600

        .CenterHorizontally = False

        .CenterVertically = False

        .Orientation = xlLandscape

        .Draft = False

        .PaperSize = xlPaperA4

        .FirstPageNumber = xlAutomatic

        .Order = xlDownThenOver

        .BlackAndWhite = False

        .Zoom = 100

        .PrintErrors = xlPrintErrorsDisplayed

        .OddAndEvenPagesHeaderFooter = False

        .DifferentFirstPageHeaderFooter = False

        .ScaleWithDocHeaderFooter = True

        .AlignMarginsHeaderFooter = True

        .EvenPage.LeftHeader.Text = ""

        .EvenPage.CenterHeader.Text = ""

        .EvenPage.RightHeader.Text = ""

        .EvenPage.LeftFooter.Text = ""

        .EvenPage.CenterFooter.Text = ""

        .EvenPage.RightFooter.Text = ""

        .FirstPage.LeftHeader.Text = ""

        .FirstPage.CenterHeader.Text = ""

        .FirstPage.RightHeader.Text = ""

        .FirstPage.LeftFooter.Text = ""

        .FirstPage.CenterFooter.Text = ""

        .FirstPage.RightFooter.Text = ""

    End With

End Sub

After Deleting unwanted line I have shorten the code as shown below

Sub Macro1()

With ActiveSheet.PageSetup

.Orientation = xlLandscape

End With

End Sub

Objects and Collections

When you work with VBA, you must understand the concept of objects and Excel’s object model. It helps to think of objects in terms of a hierarchy. At the top of this model is the Application object — in this case, Excel itself. But if you’re programming in VBA with Microsoft Word, the Application object is Word.

The object hierarchy

The Application object (that is, Excel) contains other objects. Here are a few examples of objects contained in the Application object:

·         Workbooks (a collection of all Workbook objects)

·         Windows (a collection of all Window objects)

·         AddIns (a collection of all AddIn objects)

Some objects can contain other objects. For example, the Workbooks collection consists of all open Workbook objects, and a Workbook object contains other objects, a few of which are as follows:

·         Worksheets (a collection of Worksheet objects)

·         Charts (a collection of Chart objects)

·         Names (a collection of Name objects)

Each of these objects, in turn, can contain other objects. The Worksheets collection consists of all Worksheet objects in a Workbook. A Worksheet object contains many other objects, which include the following:

·         ChartObjects (a collection of ChartObject objects)

·         Range

·         PageSetup

·         PivotTables (a collection of PivotTable objects)

Collections

A collection is a group of objects of the same class, and a collection is itself an object. Please note Workbooks is a collection of all Workbook objects currently open. Worksheets are a collection of all Worksheet objects contained in a particular Workbook object. You can work with an entire collection of objects or with an individual object in a collection. To reference a single object from a collection, you put the object’s name or index number in parentheses after the name of the collection, like this:

Worksheets(“Sheet1”)

Referring to objects

When you refer to an object using VBA, you often must qualify the object by connecting object names with a period (also known as a dot operator). What if you had two workbooks open and they both had a worksheet named Sheet1? The solution is to qualify the reference by adding the object’s container, like this:

Workbooks(“Book1”).Worksheets(“Sheet1”)

Without the workbook qualifier, VBA would look for Sheet1 in the active workbook. To refer to a specific range (such as cell A1) on a worksheet named Sheet1 in a workbook named Book1, you can use the following expression:

Workbooks(“Book1”).Worksheets(“Sheet1”).Range(“A1”)

The fully qualified reference for the preceding example also includes the Application object, as follows:

Application.Workbooks(“Book1”).Worksheets(“Sheet1”).Range(“A1”)

Properties and Methods

Object properties

Every object has properties. For example, a Range object has a property called Value. You can write VBA code to display the Value property or write VBA code to set the Value property to a specific value. Here’s a procedure that uses the VBA MsgBox function to pop up a box that displays the value in cell A1 on Sheet1 of the active workbook:

Sub ShowValue()

Msgbox Worksheets(“Sheet1”).Range(“A1”).Value

End Sub

if you want to change the Value property? The following procedure changes the value displayed in cell A1 by changing the cell’s Value property:

Sub ChangeValue()

Worksheets(“Sheet1”).Range(“A1”).Value = 123.45

End Sub

Object methods

In addition to properties, objects also have methods. A method is an action that you perform with an object. Here’s a simple example that uses the Clear method on a Range object. After you execute this procedure, A1:C3 on Sheet1 is empty, and all cell formatting is removed.

Sub ClearRange()

Worksheets(“Sheet1”).Range(“A1:C3”).Clear

End Sub

Most methods also take arguments to define the action further. Here’s an example that copies cell A1 to cell B1 by using the Copy method of the Range object. In this example, the Copy method has one argument (the destination of the copy). Notice that I use the line continuation character sequence (a space followed by an underscore) in this example. You can omit the line

continuation sequence and type the statement on a single line.

Sub CopyAtoB()

Worksheets(“Sheet1”).Range(“A1”).Copy _

Worksheets(“Sheet1”).Range(“B1”)

End Sub

Comment property

A Range object has a property named Comment. If the cell contains a comment, the Comment property returns a Comment object. For example, the following code refers to the Comment object in cell A1:

Range(“A1”).Comment

To display the comment in cell A1 in a message box, use a statement like this:

MsgBox Range(“A1”).Comment.Text

If cell A1 doesn’t contain a comment, this statement generates cryptic error message:

Object variable or With block variable not set.

To determine whether a particular cell has a comment, you can write code to check whether the Comment object is Nothing. (Yes, Nothing is a valid keyword.) The following statement displays True if cell A1 doesn’t have a comment:

MsgBox Range(“A1”).Comment Is Nothing

Full Example given below

Sub Coment()

If Not Range("A1").Comment Is Nothing Then _

MsgBox Range("A1").Comment.Text

End Sub

Adding a new Comment object

You may have noticed that the list of methods for the Comment object doesn’t include a method to add a new comment. This is because the AddComment method belongs to the Range object. The following statement adds a comment (an empty comment) to cell A1 on the active worksheet:

Range(“A1”).AddComment

The AddComment method takes an argument that represents the text for the comment. Therefore, you can add a comment and then add text to the comment with a single statement:

Range(“A1”).AddComment “This is the Actual Comment.”

The AddComment method generates an error if the cell already contains a comment. To avoid the error, your code can check whether the cell has a comment before adding one.

Useful Application Properties

Property	Object Returned
ActiveCell	The active cell.
ActiveChart	The active chart sheet or chart contained in a ChartObject on a worksheet. This property is Nothing if a chart isn’t active.
ActiveSheet	The active sheet (worksheet or chart).
ActiveWindow	The active window.
ActiveWorkbook	The active workbook.
Selection	The object selected. (It could be a Range object, Shape, ChartObject, and so on.)
ThisWorkbook	The workbook that contains the VBA procedure being executed. This object may or may not be the same as the ActiveWorkbook object.

Range Objects

There are three ways of referring to Range objects in your VBA code:

·         The Range property of a Worksheet or Range class object

·         The Cells property of a Worksheet object

·         The Offset property of a Range object

The Range property

The Range property returns a Range object. If you consult the Help system for the Range property, you learn that this property has two syntaxes:

object.Range(cell1)

object.Range(cell1, cell2)

The Range property applies to two types of objects: a Worksheet object or a Range object. Here, cell1 and cell2 refer to placeholders for terms that Excel recognizes as identifying the range (in the first instance) and delineating the range (in the second instance). Following are a few examples of using the Range property.

Worksheets(“Sheet1”).Range(“A1”).Value = 12.3

The Range property also recognizes defined names in workbooks. Therefore, if a cell is named Input, you can use the following statement to enter a value into that named cell:

Worksheets(“Sheet1”).Range(“Input”).Value = 100

The example that follows enters the same value into a range of 20 cells on the active sheet. If the active sheet isn’t a worksheet, the statement causes an error message:

ActiveSheet.Range(“A1:B10”).Value = 2

Cells property

Another way to reference a range is to use the Cells property. You can use the Cells property, like the Range property, on Worksheet objects and Range objects. The Cells property has three syntaxes:

object.Cells(rowIndex, columnIndex)

object.Cells(rowIndex)

object.Cells

Here is  examples demonstrate how to use the Cells property. The first example enters the value 9 into cell A1 on Sheet1. In this case, I’m using the first syntax, which accepts the index number of the row (from 1 to 1048576) and the index number of the column (from 1 to 16384):

Worksheets(“Sheet1”).Cells(1, 1) = 9

Here’s an example that enters the value 7 into cell D3 (that is, row 3, column 4) in the active worksheet:

ActiveSheet.Cells(3, 4) = 7

Offset property

The Offset property, like the Range and Cells properties, also returns a Range object. But unlike the other two methods that we discussed, the Offset property applies only to a Range object and no other class. Its syntax is as follows:

object.Offset(rowOffset, columnOffset)

The Offset property takes two arguments that correspond to the relative position from the upper-left cell of the specified Range object. The arguments can be positive (down or to the right), negative (up or to the left), or zero. The example that follows enters a value of 12 into the cell directly below the active cell:

ActiveCell.Offset(1,0).Value = 12

The next example enters a value of 15 into the cell directly above the active cell:

ActiveCell.Offset(-1,0).Value = 15

If the active cell is in row 1, the Offset property in the preceding example generates an error because it can’t return a Range object that doesn’t exist. The Offset property is quite useful, especially when you use variables within looping procedures.