Excel Macros/Excel VBA code. Excel VBA Macro Tips for Beginners. Excel Macros Help

The Excel Object Model provides you additional commands than you would normally obtain from the Excel menu of commands and functions. Almost anything you can do with Excel from its user interface, you can do by manipulating its objects through VBA. Microsoft has empowered you with all the objects and methods so you can do anything through VB code that can't be done through the Excel user interface. Examples of objects in the Excel Object Model are Workbooks, Worksheets, Range, Cells, Charts, Pivot and Comments.

Excel is a three-tier application: client services tier, object model, and data services layer. The workbook interface that user usually communicate with is the client services tier. Underneath this lays the object model. Each time you do something on your worksheet, you are issuing commands through the Excel object model. You use the object model to communicate with Excel application by manipulating the properties and methods of the various objects. For example, if you select File/Open from Excel menu, you are using the command Workbooks.Open to open your workbook, Sheets.Add to insert a new worksheet [see example], and add it to the workbooks collection object. Similarly, if you select Tool/Options and in Options dialog box to set Calculation to manual and using F9 to recalculate, you are issuing the command Application.Calculate [see example]. Below the object model sits the data services layer, which hold data in the spreadsheet and is modified by commands from the Excel object model.

Objects explained
An object is a programming structure combining both code and data that can be treated as a single unit. Objects let you declare variables and procedures once and then reuse them whenever needed. An object can be a piece of an application, like Application object, Workbook object, Worksheet object, Range objects, a control or a form. In a way, an object is like a variable in that it can store a value, but is more complex in that it can hold multiple values (properties) and has its own set of defined actions (methods) it can perform. To give you an analogy - you are an object. You have properties like eyes color, hairs color, and you have methods like speak, laugh, etc. A cell is also an object - a Range object. It has properties like Value, Address, HasFormula. It has methods like ClearContents, Copy, Activate.

The following example demonstrates the concept of object. (In fact you do not have to explicitly refer to the Application object as shown in line 2). Cell A1 has a Value property that shows 10. If cell B1 has a formula, a message will display cell B1 address, else it will do nothing. Address property cannot be changed. Cell B1 will have the Address of $B$1 no matter what you do. Value is a read/write property. Cell content of A1 is cleared, and B1 formula will be copied over to A1. The last line makes A1 an active cell.

Dim ad As String
Application.Workbooks(1).Sheets(1).Range(“A1”).Select
Range(“A1″).Value = 10
    ad = Range("B1").Address
    If Range("B1").HasFormula = True Then
        MsgBox ad & " has a formula"
        Else
        Exit Sub
    End If
Workbooks(1).Sheets(1).Range("A1").ClearContents
Workbooks(1).Sheets(1).Range("B1").Copy
Workbooks(1).Sheets(1).Range("A1").PasteSpecial
Workbooks("Book1").Worksheets("Sheet1").Range("A1").Activate

Objects are arranged in hierarchy. For example, at the top of the Excel object model is the Application object, and under which is the Workbook object. Within each Workbook object are the Range objects, Cells objects, and so on. Each object can contain settings or attributes called properties, and actions that can be performed on the object called methods, or both. In example line 1 below, you specify a range of cells A1:A10 in the range property and use the value property to place the text "Welcome to VBA" in these cells. In line 2, you copy the range of cells in the Activecell column and paste it into range B1:B10 in Sheet2.

Worksheets("Sheet1").Range("A1:A10").Value = "Welcome to VBA"
Sheets(1).Range(ActiveCell.Address, Cells(Rows.Count, _
ActiveCell.Column).End(xlUp).Address).Copy Sheets(2).Range("A1").Offset(0, 1)

Excel exposes various other creatable objects, allow you to do other more amazing things with these objects. These are some of the other commonly used key objects:

ActiveWorkbook object represents the active workbook.
ActiveSheet object represents the active worksheet.
ActiveWindow object represents the active window.
Activecell object represents the active cell.

Properties Explained
Property is an attribute of an object such as size, color, screen location, or the state of an object, such as enabled or disabled. Most objects have multiple properties that describe each relevant aspect of the object. Each property has a specific data type for the information it stores. For example, the Workbooks object has a Boolean property named Saved that stores value denoting whether all changes in the workbook have been saved (True) or not (False). The Name property of the Workbooks object is a string property because it needs to contain text for the filename (limit by 255 characters and certain characters like colon). The Visible property of the Worksheet object which can be xlSheetVisible (-1), xlSheetHidden (0), xlSheetVeryHidden (2), indicates whether the worksheet is visible to the user or is hidden.

‘ if you close the workbook, you will still be prompted for saving even changes had already been saved
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Workbooks("MyWorkbook").Saved = False
End Sub

‘ you can’t set the Name property of the workbook directly. Name is a property that can only be changed via a method. Change name of workbook via a property, this doesn’t work:
ThisWorkbook.Name = “MyWorkbook.xls”

’ change name of workbook via a method, this does work:
ThisWorkbook.SaveAs “MyWorkbook.xls”

’ this gives the name of the activesheet as “week1”
ActiveSheet.Name = “week1”

‘ This example hides Sheet1
Worksheets("Sheet1").Visible = False

' this example makes every sheet in the active workbook visible
Sub MakeAllSheetsVisible()
Dim sh As Worksheet
    For Each sh In Sheets
        sh.Visible = True
    Next sh
End Sub

‘ this example creates a new worksheet and then sets its Visible property to xlVeryHidden. To use the newSheet object variable in another procedure, you must declare it as a public variable (Public newSheet As Object).
Set newSheet = Worksheets.Add
newSheet.Visible = xlVeryHidden

Properties can be either read-only or read/write. HasPassword property in Workbook shows True or False according to whether the workbook is protected by a password. HasPassword property is read-only as you can’t change it to False to remove password protection without knowing the password. The Count property of the Workbooks object defines how many workbooks are loaded into the Excel application, and is read-only. For example, a workbook contains three worksheets and if you could change the Count property to one sheet, the other two sheets of data would be deleted. If a property is read-write, it means you can substitute values into it to provide different effects, depending on the object.

If ActiveWorkbook.HasPassword = True Then
MsgBox "Please login with your password."
Else
Cancel = True
End If
aw = ActiveWorkbook.Worksheets.Count
MsgBox "number of sheets in this active workbook is: " & aw

Properties can be manipulated by using code at runtime when the program is executing and can be changed at design time using properties window in VBE. The following is an example of changing property at design time. The open Workbook is the object and Saved is the property. There is more than one dot separator because objects can have subobjects and properties can have subproperties. The workbook Saved property is set to True and it means you will not get prompted for saving regardless of whether it has been saved or not. In the example below, you place this code in ThisWorkbook’s SheetChange event, and every time you make changes to the data in the worksheet, the Saved property will be set back to True. When you close the worksheet, there is no prompt to save because the program will check the Saved property of your worksheet called Sheet1 and set it to True and assume that it is already saved.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Workbooks("MyWorkbook").Worksheets("Sheet1").Saved = True
End Sub

Methods explained
Methods are ways of executing actions based on a particular object. One thing a method might do is manipulate the objects fields and properties. For examples, you use the Open method to open an existing workbook, Activate method to make this workbook the current workbook, Delete method on the worksheets collection, specifying which worksheet you want to delete, SaveAs method to save a workbook with a different filename and/or to a different location, NewWindow method to create a new window for the active workbook, Add method to add new worksheet for the active workbook.

‘ this opens MyWorkbook.xls from C directory
Workbooks.Open ("C:\MyWorkbook.xls")

‘ this activates the open “MyWorkbook.xls
Windows("MyWorkbook").Activate

‘ alternatively use this example to activate MyWorkbook.xls
Workbooks("MyWorkbook").Activate

‘ this deletes Sheet2 from the active workbook
Worksheets("Sheet2").Delete

‘ this saves MyWorkbook.xls to a different path in C directory:
Workbooks("MyWorkbook").SaveAs Filename:="C\Data\MyWorkbook.xls"

‘ this creates a new window for the active workbook
ActiveWorkbook.NewWindow

‘ this adds a new worksheet to the active workbook
ActiveWorkbook.Worksheets.Add

Calling Methods – passing by order and passing by name
The following example SaveAs method passes the mandatory parameter “Mybook1.xls”, a password parameter “kuching” and a ReadOnlyRecommended parameter. This is called passing by order because the parameters are being passed in the order in which they are defined in the function, separated by commas which are optional parameters (optional parameters are shown with square brackets [ ] ). Passing by order can sometimes become complicated with many optional parameters even though we use only three of them as in the example below. Optional parameters that are not being used are shown by the null values between the commas.

Workbooks("Book1").SaveAs "C:\Mybook1.xls", , "kuching", ,True

An alternative way is passing by name method. Passing parameters by name enables you to selectively pass arguments without having to specify null values for arguments you don not want to use. In this method, you can pass the parameters in any order. With this method, the preceding example will be re-written as follow:

Workbooks("Book1").SaveAs Filename:="C:\Mybook1.xls", _
Password:="kuching", ReadOnlyRecommended:=True

Collections explained
Many types of objects in Excel come in groups. There may be multiple workbooks open in Excel at a given time, and these open workbooks in the Excel application are grouped together into the Workbooks collection. All the workbooks are like objects because they have the same properties and methods. Each workbook may have multiple worksheets and each worksheet can have multiple charts. These multiple objects are gathered together in what’s called a collection. The collection itself is an object, with its own set of properties and methods. Worksheets collection has different properties and methods and so it can not be part of the Workbooks collection. The same is for the Charts collection which has different properties and methods.

To explain in another way, the Application object has a property called Workbooks, which represents a Workbooks Collection object. It includes a single Workbook object for each workbook currently open in Excel. Similarly, the Workbooks object has a property called Worksheets which is a Worksheets Collection object, including one Worksheet object for each worksheet in the workbook. Collections also have their own properties and methods apart from the objects that they hold. For example, Collections have a Count property that represents the number of objects within the collection, and also have an Add method that adds a new object into the collection.

Referencing Items in a Collection
In Excel, all objects are either singular objects referenced by name, or objects in a collection reference by index or by name. It is easier to reference an object in a collection is by number. All objects in a collection have a numeric index, starting with 1. To reference an item in a collection by number, simply put the number in parentheses after the collection object. Try the following example.

Option Explicit
Sub ShowNamesOfSheets()
Dim shCount As Integer
Dim iSheet As Integer
    shCount = ActiveWorkbook.Worksheets.Count
    For iSheet = 1 To shCount
        Worksheets(iSheet).Activate
        Range("A1") = Worksheets(iSheet).Name
    Next iSheet
End Sub

The above example shows that collections can be cycled through. Cycling is the best term to describe what happens in a For Each..Next loop as you will see under Looping methods. We set up a variable iSheet to store integer values that represent active workbook’s worksheets count. The code cycles through the worksheets collection, get the name property of each worksheet in turn, then activate each worksheet to store the worksheet names in cell A1 of each worksheet.

Remember this: Objects are things in Excel; Properties are characteristics that define those objects; and Methods are actions that the objects take and that change a few properties along the way. The structure of Excel object model is complicated and you should not be expected to remember every object, collection, property and method within it. If you do not use the automatic list boxes that appear as you type object code in, you will have to constantly refer to Object Browser in order to know what command to use next. In the Object Browser, click a class (which is an object) to see all the collection, property and method within it in the Members of Lists box to the right of class. Methods have a green icon (clicking a method display the syntax for parameters, which ones are optional and mandatory), and properties have a grey icon with a hand symbol holding it (clicking a property will show whether it is read-only or if you can write to it).

You can get an overview of the Microsoft Excel Object Model by going to VBE, choose Help/ Microsoft Visual Basic Help. Type excel object model in search box, press Enter. In the search result, click Microsoft Excel Object Model, and you will able to see an overview screen of the Excel Object Model. The yellow shaded boxes are objects that are organized into collections, while the blue shaded boxes are objects that do not have collections. You can click a box to display the Help screen for an individual object or collection including a schematic of the related objects. For example, click on the blue shaded Workbook object will display the related properties.

Main Objects of the Excel Objects Model
There are other collection objects such as Windows, Charts, Borders, PivotTables, Validation but Application, Workbooks, Worksheets, and Range objects are the main ones.

Application Object
Properties: Workbooks, ActiveWindow, ActiveWorkbook, ThisWorkbook, ActiveSheet, ActiveCell, Caption, Selection, Charts,
ScreenUpdating, DisplayAlerts, UserName, CutCopyMode…
Methods: CheckSpelling, Run, Quit…
Collections: Rows, Columns, Sheets…

‘ActiveCell property represents the active cell the cursor is currently at
Application.ActiveCell.Address

‘ActiveWindow property represents the active window in Excel
Application.ActiveWindow.Caption

‘ Caption property holds the caption that is found in the window bar for Excel, example “Book1.xls”
Application.Caption

‘ this changes the caption “Microsoft Excel = Book 1” to “MyExcel – Book1”
Application.Caption = "MyExcel" ' put in null value will reset it

‘ ActiveWorkbook property displays the name of the active workbook
Application.ActiveWorkbook.Name

‘ ThisWorkbook property represents where the current macro is executing
Application.ThisWorkbook.Name

‘ ActiveSheet property represents the active worksheet being displayed
Application.ActiveSheet.Cells(1, 2).Select

‘ Selection property holds the current selection object. The Address property let you can get the cell address
Application.Selection.Address

‘ The Worksheet property tells you which sheet the selection is currently on
Application.Selection.Worksheet.Name

‘ UserName property returns the name of the user currently logged on to the Windows system
Application.UserName

‘ this Rows collection selects row 2
Application.Rows(2).Select

‘ this Columns collection selects column 1
Application.Columns(1).Select

‘ this Calculate method forces recalculation of the entire spreadsheet similar to pressing F9
Application.Calculate

‘ Calculation property sets the method of calculation used in Excel. Here it sets to Manual mode
Application.Calculation = xlCalculationManual

‘ this property sets to the default automatic calculation
Application.Calculation = xlCalculationAutomatic

‘ set DisplayAlerts property to False if you don’t want to see prompts and alert messages while macro is running
Application.DisplayAlerts = True

' set ScreenUpdating property to False to speed up your macro code, and you won't be able to see what the macro is doing
Application.ScreenUpdating = True

’ Sheets collection represents all the worksheets within the current workbook. PrintPreview method let you preview
Application.Sheets(1).PrintPreview

' Sheets collection creates a new worksheet and places a list of the active workbook's sheet names in the first column
Set newSht = Sheets.Add(Type:=xlWorksheet)
For i = 1 To Sheets.Count
newSht.Cells(i, 1).Value = Sheets(i).Name
Next i

You can download as a demo workbook from here

Workbook Object
Properties: Worksheets, ActiveSheet, ActiveChart, Name, Path, FullName, FileFormat, HasPassword…
Methods: Activate, Save, SaveAs, Close, Protect, PrintOut, PrintPreview…

‘ Activate method activates the workbook specified in the Collection object
Workbooks("MainWorkbookObjects").Activate

‘ Worksheets collection represents all the worksheets within the workbook object, here is represents Sheet2
ActiveWorkbook.Worksheets("Sheet2").Select

‘ ActiveSheet property references the active worksheet within the workbook
ActiveWorkbook.ActiveSheet.Name

' FullName property is same as Path property. It returns the name of the object, including its path on disk, as a string.
ActiveWorkbook.FullName

‘ HasPassword returns True or False, depends on whether the workbook is protected by a password
Workbooks("MainWorkbookObjects").HasPassword

‘ Protect method protects the particular workbook with a password called ‘william”
Workbooks("MainWorkbookObjects").Protect Password:="william"

‘ PrintPreview method provides a print preview on the active sheet
Workbooks("MainWorkbookObjects").PrintPreview

‘ PrintOut method prints out the active sheet of the referenced workbook to the printer
Workbooks("MainWorkbookObjects").PrintOut

‘ Save method saves your workbook as the same name
Workbooks("MainWorkbookObjects").Save

‘ SaveAs method save your workbook to a different filename
ActiveWorkbook.SaveAs FileFormat:=xlNormal, Filename:="C:\MainWorkbookObjects.xls"

‘ Saved property is True or False. If your workbook has been saved and no further changes have been made, it displays True. If changes are made and have not been saved, it displays False.
Workbooks("MainWorkbookObjects").Saved

' To exit a worksheet without saving any changes that are made to it
ActiveWorkbook.Saved = True 'or, ActiveWorkbook.Close SaveChanges:=False
Application.Quit

‘ Close method close the workbook in the same way as you select from Excel menu File/Close
ActiveWorkbook.Close

You can download as a demo workbook from here

Worksheet Object
Properties: Name, Index, Cells, Columns, Rows, Type, Visible…
Methods: Activate, Select, Calculate, Copy, Add, Delete, Move, PrintOut, Protect, Unprotect, SaveAs…
Collections: Comments…

' this Activate method makes the 1st worksheet the active worksheet
Worksheets(1).Activate

' Name property changes the name of the active worksheet
ActiveSheet.Name = "mySheet"

' Index property displays the tab number of the sheet that you specified
Worksheets("mySheet").Index

' Calculate method calculates the first worksheet
Worksheets(1).Calculate

' this example Comments collection shows how many comments there are in 1st sheet
Worksheets(1).Comments.Count

' this Cells property displays 65535, which denotes the number of rows in a worksheet
Cells.Rows.Count

' the Cells property cycles through a range of cells using For Next loop, and substitute variables for the index numbers
    Dim Counter As Integer
    For Counter = 1 To 12
        Worksheets(1).Cells(Counter, 2).Value = Counter
    Next Counter
        Cells(1, 2).Select     ‘ this selects cell B1

' this Columns property formats column 1 with Bold font
Worksheets(1).Columns(1).Font.Bold = True

' this Rows property deletes row 1
Worksheets(1).Rows(1).Delete

' this Add method and Type property add two chart sheets to the active workbook, placing them after sheet two
Sheets.Add Type:=xlChart, Count:=2, after:=Sheets(2)

' this Add method and Type property give the same result as above
ActiveWorkbook.Sheets.Add Type:=xlChart, after:=Sheets(Sheets.Count - 2), Count:=2

' Protect method protects Sheet1 in "MainWorksheetObjects" workbook with password 'william". Unprotect, the opposite
Workbooks("MainWorksheetObjects").Worksheets(1).Protect Contents:=True, Password:="william"
Workbooks("MainWorksheetObjects").Sheets(1).Unprotect Password:="william"

' this example Delete method deletes the 4th worksheet
Worksheets("Sheet4").Delete

' these 2 methods to preview or print out the active worksheet
Worksheets(1).PrintPreview
Worksheets(1).PrintOut

' Move method let you move Sheet2 after Sheet3 in the active workbook
Worksheets("Sheet2").Move after:=Worksheets("Sheet3")

' the Visible property hides Sheet3. set it to True will unhide the sheet
Worksheets("Sheet3").Visible = False

' Select method selects a particular worksheet. It select Sheet2
Worksheets("Sheet2").Select

' SaveAs method saves the workbook under a different filename
Worksheets("Sheet2").SaveAs ("C:\MyWorksheetObjectsFile")

' this Copy method example let you copy the first Sheet to a new workbook
Worksheets(1).Copy

You can download as a demo workbook from here

Range Object
Properties: Count, Column, Columns, Row, Rows, Cells, Address, Value, ColumnWidth, RowWidth,UsedRange…
Methods: Activate, Select, Find, Copy, PasteSpecial, Replace, Clear, ClearContents, ClearComments,ClearFormats…

' Value property input values to the range A1:C4
Worksheets("Sheet1").Range("A1:C4").Value = "Mycells"

' Cells collection displays how many cell are within the specified range
Worksheets("Sheet1").Range("A1:C4").Cells.Count

' Address property display different representations of the same cell A1 on Sheet1 Set Addr = Worksheets("Sheet1").Cells(1, 1)
MsgBox Addr.Address()                              ' it displays $A$1
MsgBox Addr.Address(RowAbsolute:=False)            ' it displays $A1
MsgBox Addr.Address(ReferenceStyle:=xlR1C1)        ' it displays R1C1

'this Copy and PasteSpecial methods copy and paste a range of cells, from 1st to 2nd sheet
Worksheets(1).Range("A1:C4").Copy
Worksheets(2).Range("D1").PasteSpecial

'this does the same trick as above by skipping the PasteSpecial method
Worksheets(1).Range("A1:C4").Copy Worksheets(2).Cells(1, 4)

' this method activates a range of cells to make into active cells
Worksheets("Sheet1").Range("A1:C4").Activate

' Columns and Rows property display the number of columns & rows respectively
Worksheets(1).Range("A1:E8").Columns.Count
Worksheets(1).Range("A1:E8").Rows.Count

' Column & Row property return the 1st column number or the 1st row number within the range
Worksheets(1).Range("B4:E8").Column
Worksheets(1).Range("B4:E8").Row

' ColumnWidth and RowHeight properties sets the width of columns & height of rows
Worksheets(1).Range("A1:E8").ColumnWidth = 6
Worksheets(1).Range("A1:E8").Rowheight = 14

' replace method replaces a specified character found within the range with another one
Worksheets(1).Range("A1:C4").Replace "My", "Testing"

' this property sets whether text will wrap within a cell (True) or stay on one line (False)
Worksheets(1).Range("A1:C4").WrapText = True

' ClearContents method clears content of a range of cells, but does not clear the format or border
Worksheets("Sheet1").Range("A1:C4").ClearContents

' Clear method clears everything - content, formats, borders
Worksheets("Sheet2").Range("A1:C4").Clear

' the Find method looks for 1905 in formulas in cells after the active cell, without searching for its
' cell format. In fact you can skip all the optional parameters to just Cells.Find(What:=1905).Activate
Cells.Find(What:="1905", After:=ActiveCell, LookIn:=xlFormulas, _
   LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
   MatchCase:=False, SearchFormat:=False).Activate

' UsedRange Property represents the used range on the active worksheet
ActiveSheet.UsedRange.Select

You can download as a demo workbook from here

Return To Top

This site was created in Feb.2007
by William Tan