Contents
Term Excel Objects (understood broadly as the Excel object model) includes the elements that make up any Excel workbook. These are, for example, worksheets (worksheet), terms (Rows), columns (Columns), cell ranges (Ranges) and the Excel workbook itself (Workbook) including. Every Excel object has a set of properties that are integral to it.
For example, an object spreadsheet (worksheet) has properties Name (name), Protection (Protection), Visible (visibility), Scroll Area (scroll area) and so on. Thus, if during the execution of a macro it is required to hide the worksheet, then it is enough to change the property Visible this sheet.
There is a special type of objects in Excel VBA − collection. As the name suggests, a collection refers to a group (or collection) of Excel objects. For example, collection Rows is an object containing all the rows of the worksheet.
All core Excel objects can be accessed (directly or indirectly) through the object workbooks, which is a collection of all currently open workbooks. Each workbook contains an object sheets is a collection that includes all worksheets and chart sheets in a workbook. Every object spreadsheet consists of a collection Rows – it includes all the lines of the worksheet, and collections Columns – all columns of the worksheet, and so on.
The following table lists some of the most commonly used Excel objects. A complete list of Excel VBA objects can be found on the Microsoft Office Developer site (in English).
| Object | Description |
|---|---|
| Application | Excel application. |
| workbooks | A collection of all currently open workbooks in the current Excel application. A particular workbook can be accessed through an object workbooks using the numeric index of the workbook or its name, for example, Workbooks(1) or Workbooks(“Book1”). |
| Workbook | Object Workbook is a workbook. It can be accessed through the collection workbooks using a numeric index or workbook name (see above). To access the currently active workbook, you can use ActiveWorkbook. From object Workbook can access the object sheets, which is a collection of all workbook sheets (worksheets and charts), plus the object worksheet, which is a collection of all worksheets in an Excel workbook. |
| sheets | Object sheetsis a collection of all sheets in the workbook. It can be either worksheets or charts on a separate sheet. Accessing an individual sheet from a collection sheets can be obtained using the numerical index of the sheet or its name, for example, Sheets(1) or Sheets(«Sheet1»). |
| worksheet | Object worksheet is a collection of all worksheets in a workbook (that is, all sheets except charts on a separate sheet). Accessing an individual worksheet from a collection worksheet can be retrieved using the numeric index of the worksheet or its name, for example, Worksheets(1) or Worksheets(“Sheet1”). |
| spreadsheet | Object spreadsheet is a separate worksheet in an Excel workbook. It can be accessed using the worksheet’s numeric index or worksheet name (see above). In addition, you can use ActiveSheet to access the currently active worksheet. From object spreadsheet can access objects Rows и Columns, which are a collection of objects —, referring to the rows and columns of the worksheet. You can also access a single cell or any range of adjacent cells on a worksheet. |
| Rows | Object Rows is a collection of all rows in the worksheet. An object —, consisting of a single worksheet line, can be accessed by that line number, for example, Rows(1). |
| Columns | Object Columns is a collection of all worksheet columns. An object —, consisting of a single worksheet column, can be accessed by that column number, for example, Columns(1). |
| — | Object — is any number of contiguous cells on a worksheet. It can be one cell or all cells of the sheet. A range consisting of a single cell can be accessed through an object spreadsheet using the property Cells. Cells.For example, Worksheet.Cells(1,1). In another way, a reference to a range can be written by specifying the addresses of the starting and ending cells. They can be written with a colon or a comma. For example, Worksheet.Range(«A1:B10») or Worksheet.Range(«A1», «B10») or Worksheet.Range(Cells(1,1), Cells(10,2)). Please note if the address — the second cell is not specified (for example, Worksheet.Range(«A1») or Worksheet.Range(Cells(1,1)), then a range consisting of a single cell will be selected. |
The table above shows how Excel objects are accessed through parent objects. For example, a reference to a range of cells can be written like this:
Workbooks("Book1").Worksheets("Sheet1").Range("A1:B10")Assigning an object to a variable
In Excel VBA, an object can be assigned to a variable using the keyword Set:
Dim DataWb As Workbook Set DataWb = Workbooks("Книга1.xlsx")active object
There is an active object at any given time in Excel Workbook is the workbook currently open. Similarly, there is an active object spreadsheet, active object — and so on.
Refer to active object Workbook or Sheet in VBA code, you can either ActiveWorkbook or ActiveSheet, and on the active object — – How on Selection.
If a reference to a worksheet is written in the VBA code, without specifying which workbook it belongs to, then Excel refers to the active workbook by default. Similarly, if you refer to a range without specifying a specific workbook or sheet, Excel will default to the active worksheet in the active workbook.
So to refer to a range A1: B10 on the active worksheet of the active workbook, you can simply write:
Range("A1:B10")Changing the active object
If during the execution of the program it is required to make another workbook active, another worksheet, range, and so on, then for this you need to use the methods Activate or Select like this:
Sub ActivateAndSelect() Workbooks("Книга2").Activate Worksheets("Лист2").Select Worksheets("Лист2").Range("A1:B10").Select Worksheets("Лист2").Range("A5").Activate End SubObject methods, including the methods just used Activate or Select, will be discussed in more detail below.
Object properties
Each VBA object has properties set for it. For example, an object Workbook has properties Name (name), RevisionNumber (number of saves), sheets (sheets) and many others. To access the properties of an object, write the name of the object followed by a dot followed by the name of the property. For example, the name of the active workbook can be accessed like this: ActiveWorkbook.Name. So to assign to a variable wbName the name of the active workbook, you can use the following code:
Dim wbName As String wbName = ActiveWorkbook.Name
Earlier we showed how the object Workbook can be used to access an object spreadsheet using this command:
Workbooks("Book1").Worksheets("Sheet1")This is possible because the collection worksheet is a property of the object Workbook.
Some object properties are read-only, meaning that the user cannot change their values. At the same time, there are properties that can be assigned different values. For example, to change the name of the active sheet to “My worksheet“, it is enough to assign this name to the property Name active sheet, like this:
ActiveSheet.Name = "My Worksheet"
Object Methods
VBA objects have methods to perform certain actions. Object Methods are procedures attached to objects of a certain type. For example, an object Workbook has methods Activate, Fermer, Save and many more others.
In order to call a method on an object, you need to write down the name of the object, a dot, and the name of the method. For example, to save the active workbook, you can use the following line of code:
ActiveWorkbook.Save
Like other procedures, methods can have arguments that are passed to the method when it is called. For example, the method Fermer object Workbook has three optional arguments that specify whether the workbook should be saved before closing, etc.
To pass arguments to a method, you must record the values of these arguments separated by commas after calling the method. For example, if you want to save the active workbook as a file . Csv with the name “Book2”, then you need to call the method SaveAs object Workbook and pass the argument Filename value Book 2, and the argument FileFormat – meaning xlCSV:
ActiveWorkbook.SaveAs "Книга2", xlCSV
To make the code more readable, you can use named arguments when calling a method. In this case, first write the name of the argument, then the assignment operator “:=” followed by a value. Thus, the above method call example SaveAs object Workbook can be written differently:
ActiveWorkbook.SaveAs Filename:="Книга2", [FileFormat]:=xlCSV
In the Object Browser The Visual Basic Editor shows a list of all available objects, their properties and methods. To open this list, start the Visual Basic Editor and click F2.
Let’s look at a few examples
Example 1
This VBA code snippet can serve as an illustration of the use of a loop For Each. In this case, we’ll use it to demonstrate object references. worksheet (which is taken from the active workbook by default) and links to each object spreadsheet separately. Note that the property is used to display the name of each worksheet. Name object spreadsheet.
'Scroll through all the worksheets in the active workbook 'and display a message box with the name of each worksheet Dim wSheet As Worksheet For Each wSheet in Worksheets MsgBox "Found worksheet: " & wSheet.Name Next wSheet
Example 2
This VBA code example shows how worksheets and cell ranges can be accessed from other workbooks. In addition, you will make sure that if a reference to a specific object is not specified, then active Excel objects are used by default. This example demonstrates the use of the keyword Set to assign an object to a variable.
In the code below, for an object — method is called PasteSpecial. This method passes an argument paste value xlPasteValues.
'Copy a range of cells from sheet "Sheet1" of another workbook (named Data.xlsx) 'and paste only the values into sheet "Results" of the current workbook (named CurrWb.xlsm) Dim dataWb As Workbook Set dataWb = Workbooks.Open( "C:Data") 'Note that DataWb is the active workbook. 'Therefore, the following action is performed on the Sheets object in DataWb. Sheets("Sheet1").Range("A1:B10").Copy 'Paste the values copied from the range of cells into the 'Results' worksheet of the current workbook. Note that the CurrWb.xlsm workbook is not 'active' and therefore must be referenced. Workbooks("CurrWb").Sheets("Results").Range("A1").PasteSpecial Paste:=xlPasteValuesExample 3
The following VBA code snippet shows an example of an object (collection) Columns and demonstrates how it is accessed from an object spreadsheet. In addition, you will see that when referring to a cell or range of cells in the active worksheet, you don’t have to refer to that worksheet. Revisiting the keyword Set, with which the object — assigned to a variable Collar .
This VBA code also shows an example of property access Value object — and changing its value.
'Using a loop, we look at the values in column A on sheet "Sheet2", 'perform arithmetic operations with each of them and write the result 'in column A of the active worksheet (Sheet1) Dim i As Integer Dim Col As Range Dim dVal As Double 'Assign variable Col column A of worksheet "Sheet2" Set Col = Sheets("Sheet2").Columns("A") i = 1 'Looking through all the cells of column Col until 'an empty cell is encountered Do Until IsEmpty(Col. Cells(i)) 'Perform arithmetic operations on the value of the current cell dVal = Col.Cells(i).Value * 3 - 1 'The following command writes the result to column A 'of the active sheet. There is no need to include the sheet name in the link, 'because this is the active sheet in the workbook. Cells(i, 1).Value = dVal i = i + 1 Loop