In this Article
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.
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
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”).
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
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”).
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”).
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.
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
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
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.
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.