VBA Objects

Excel VBA objects refer to single “entities” made up of code and data. The Excel application itself is an object, as are workbooks, worksheets, cell ranges, and shapes. Every object has associated properties, and methods. Objects can also contain other objects and the collections object is used to refer to a group of the same Excel objects.

In this tutorial, we are going to look at some commonly used Excel Objects.

Application Object

The Application Object refers to the entire Excel application. The Application object contains the workbook object.

The following code uses the WindowState property of the Application object to set the Excel window to the maximum size available:

Sub MaximizingTheExcelWindow()

Application.WindowState = xlMaximized

End Sub

Workbooks Object

The Workbooks object refers to the collection of all the currently open Excel workbooks.

The following code uses the Workbooks.Add method to create a new workbook and add it to the collection:

Sub AddingANewWorkbookToTheWorkbooksCollection()

Workbooks.Add

End Sub

You can access an individual workbook in the Workbooks collection through its index number or name. So you could refer to a Workbook called ExcelWb, by using Workbooks(“ExcelWB”).

Workbook Object

The workbook object is part of the Workbooks collection. The workbook object contains the worksheets collection (worksheets) and the sheets collection (worksheets, chart sheets, and macrosheets). The ActiveWorkbook object refers to the workbook that is active.

The following code uses the ActiveWorkbook.Save method to save the current active workbook:

Sub SavingTheWorkbook()

ActiveWorkbook.Save

End Sub

Sheets Object

The sheets object refers to the collection of all the worksheets, chart sheets and macrosheets in a workbook. The following code uses the Sheets.Add method to add a new worksheet called ExtraSheet, after the last worksheet in the workbook:

Sub AddingANewSheet()

ActiveWorkbook.Sheets.Add(After:=ActiveWorkbook.Worksheets(Worksheets.Count), Count:=1, _
Type:=xlWorksheet).Name = "ExtraSheet"

End Sub

Note the syntax of the Sheets.Add method is:
Sheets.Add(Before, After, Count, Type) where:

-Before is optional and specifies that the new sheet should be added before an existing sheet.

-After is optional and specifies that the new sheet should be added after an existing sheet.

-Count is optional and specifies the number of sheets to add.

-Type is optional and specifies the sheet type. xlWorksheet would add a new worksheet, xlChart would add a new chart sheet, and xlExcel4MacroSheet or xlExcel4IntlMacroSheet would add a new macrosheet. If blank the default xlWorksheet is used.

You can access an individual sheet in the Sheets collection through its index number or name. So you could refer to a Worksheet called SheetOne, by using Sheets(“SheetOne”).

Worksheets Object

The Worksheets object refers to the collection of all the worksheets in a workbook. The following code uses the Worksheets.Add method to add a new worksheet:

Sub AddingANewSheet()

Worksheets.Add

End Sub

You can access an individual sheet in the Worksheets collection through its index number or name. So you could refer to a Worksheet called SheetTwo, by using Worksheets(“SheetTwo”).

Worksheet Object

The worksheet object is part of the Worksheets collection. The worksheet object contains the range object and other objects. The ActiveSheet object refers to the sheet that is active.

The following code changes the page orientation of the active sheet to landscape:

Sub ChangingOrientationToLandscape()

ActiveSheet.PageSetup.Orientation = xlLandscape

End Sub

Note the Sheet object contains the PageSetup object and its orientation property is set to xlLandscape.

Range Object

The Range object can refer to a single cell or a set of cells in a worksheet. The following code shows you how to use the Range.Select method to select cells A1:B1:

Sub SelectingARange()

Range("A1:B1").Select

End Sub

Shapes Object

The Shapes object refers to the collection of all the shapes in a worksheet. The following code would select all the shapes on the ActiveSheet:

Sub SelectingAllTheShapes()

ActiveSheet.Shapes.SelectAll

End Sub

Shape Object

The Shape Object is part of the Shapes collection. The following code would create a rounded rectangle shape and then set the name property of the shape object:

Sub UsingTheShapeObject()

With Worksheets(1).Shapes.AddShape(msoShapeRoundedRectangle, _
200, 100, 80, 80)
.Name = "A Rounded Rectangle"

End With

End Sub

Excel VBA Object Model

Excel’s VBA Object model describes the hierarchy of all the objects you can use in Excel. For example, you can use the Workbooks object to refer to all the other objects indirectly or directly. The following code shows you how to select cell A1, using the hierarchical structure:

Sub UsingTheHierachicalStructure()

Workbooks("Book1").Worksheets("Sheet1").Range("A1").Select

End Sub

Declaring and Assigning an Object Variable

You can declare and assign an object to a variable by using the Dim and Set keywords.

For example:

Dim ws as worksheet
Set ws = ActiveWorkbook.ActiveSheet

The following code shows you how to declare and assign a Range object to a variable:

Sub AssigningARangeToAVariable()

Dim rngOne As Object
Set rngOne = Range("A1:C1")

rngOne.Font.Bold = True
With rngOne
.Font.Bold = True
.Font.Name = "Calibri"
.Font.Size = 9
.Font.Color = RGB(35, 78, 125)
.Interior.Color = RGB(205, 224, 180)
.Borders(xlEdgeBottom).LineStyle = xlContinuous
End With

End Sub

The result is:

It’s essential to understand how objects work to master VBA. You can learn more with our Interactive VBA Tutorial.