What are Excel Objects

Macros are an effective way to automate. It allows you to program specific operations. For example, they allow you to display certain information in a cell based on what content is in another. Of course, similar functionality is also available in the set of standard Excel functions, but it all depends on the complexity of the actions to be performed.

And for the implementation of this functionality, it is important to familiarize yourself with the concept of “objects”, which is key when programming spreadsheets.

This term in Excel means a set of parts and functional elements that allow you to create a table. This list includes sheets, ranges as a whole, and each row or column individually. In addition, the document itself, and even the Windows application running on the user’s computer, serves as an object.

All of them have a set of characteristics, such as the name, security, visibility, scroll area, and many others. They are called properties. With their help, it is possible to change the characteristics of objects, which can be very useful while the macro is running.

By changing them, you can influence the features of user interaction with the document. So, by adjusting the Visible property, you can make the table invisible.

In addition, the language VBA provides an idea, every object is a collection. As a quick-witted reader guesses, this is a collection of several objects. For example, Rows is an object that contains all the lines of a document that a person can use or not use.

Getting access to objects

To program a macro to call the desired object, it is important to call the parent first. For example, to access a specific work document, you should refer to the Workbooks collection. It includes a set of both currently used by the user and not.

Here is a table with the most frequently encountered objects. The full list can be found on the official Internet resource of Microsoft Office Developer (information is provided in English).

It is important to note that if the developer did not specify the second cell in the link, then it will be considered that the user wrote down only one.

This table shows what needs to be done to access a particular object. To do this, you need to access it through the parent collection. So, to record a range, you need to type the following code snippet.

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

How to specify an object as the value of a variable

Among other things, it is possible to assign a specific object to a variable. To do this, you must use the word Set. We give an example for better understanding.

Dim DataWb As Workbook

Set DataWb = Workbooks(«Data3.xlsx»)

What is the difference between an active object and a normal one?

Excel always considers one of the documents as active because the person is working on it. Likewise, there are active objects of other types. Therefore, any of them can be accessed through the ActiveWorkbook, ActiveSheet operator. If it is necessary to call an active object of type Range, then Selection must be specified.

If VBA needs to reference an object without specifying the specific collection it belongs to, Excel automatically uses the active one. The situation is similar with the absence of references to a specific document or set of sheets. If you don’t specify exactly which object to work with, Excel automatically uses the active one to perform its operations.

This feature of macros is very convenient to use in practice. It is enough to accurately describe only one object, and Excel will automatically search for it in active collections. For example, yes.

Range(«A1:B10»)

Changing the current active object

If during the execution of the code it is necessary to change the current active object, it is necessary to use the Activate or Select method, as shown in the example.

Workbooks(«Book123.xlsm»).Activate

Worksheets(«Data5»).Select

Range(«A10», «B20»).Select

We will describe in more detail below what these methods are used for.

Materials

Each of the collections in the VBA programming language has a specific set of properties. So, a book can have a name, cells, and others. To change or get the values ​​of certain properties, you must write a dot immediately after the name of the corresponding object. So, to get the name of the current active book, you need to write a line of code ActiveWorkbook. Name. Therefore, to pass the title of the book, you must specify the following code:

Dim wbImya As String

wbImya = ActiveWorkbook.Name

Previously, we have already demonstrated the method of working with an Excel workbook sheet through a line of code:

Workbooks(«WB1»).Worksheets(«WS1»)

There is no contradiction here, since the Worksheet object is, in parallel, a property of the Workbook object.

Some of the properties cannot be changed because they are read-only. But some of them are subject to change. For example, if you want to change the name of this sheet to “Sheet1450”, you must use the following code to assign this name to this property.

ActiveSheet.Name = «Sheet 1450»

Methods: what is it?

Another concept that characterizes objects is methods. This term refers to the actions that an application can perform. From the point of view of the VBA language, methods are procedures associated with certain collections. Thus, the Workbook object performs many actions, among which are the methods “Activate”, “Close”, “Save”.

To perform any of these procedures, you must specify its name through a dot in the same way as with a property. For example.

ActiveWorkbook.Save

This method provides the ability to save the current active Excel workbook. Like other procedures, they can have arguments that are used when they are called. Thus, the “Close” method provides three additional parameters that make it possible to pass several types of information to it, such as the path to save the book when it is closed, as well as others.

Arguments are passed to the method by specifying the necessary parameters. They are separated by commas. So, if the task is to save this active book to a .csv file with the name “Very important book”, then this can be done using the SaveAs method, in which the arguments are as follows – “Very important book” and “xlCSV”. When specifying the arguments, it is very important to follow the correct sequence. First of all, you need to write the name of the file, and secondly, its format.

In practice, it will look like this.

ActiveWorkbook.SaveAs “Very Important Book”, xlCSV

To improve code readability, it is important to use named arguments every time you want to call a method. This, of course, is inconvenient, but it will make it possible to save a huge amount of time in the future. If it was decided to use named arguments, you must first write their name, then the := icon, and then write the corresponding parameter. And an example to demonstrate how it works.

ActiveWorkbook.SaveAs  Filename:=»Book2″,  [FileFormat]:=xlCSV

To expand your knowledge regarding the objects, properties and methods available in the macro language, you can open a special window “Object browser” of the development environment. To do this, press the F12 function key on the keyboard in the top row. On some laptops, you may also need to press the Fn key.

Examples

To better understand the principles that we talked about above, we propose to consider 3 illustrative examples.

Example 1

This piece of code was first used to show beginners how the loop works. But to demonstrate how a Worksheet can be referenced, it can also be cited. In this case, the code calls a specific sheet of the current active document.

It is important to remember that the “Name” property is common to all worksheets and is recommended.

‘ Loop through all sheets in the active workbook

‘ and displays its name in the dialog

Dim wSheet1 As Worksheet

For Each wSheet1 in Worksheets

MsgBox «Found object: » & wSheet.Name

Next wSheet

Example 2

This fragment clearly shows what needs to be done to work with the information contained in various documents. In addition, it shows how a default object can be invoked when there is no specific object to refer to by the developer.

‘ Copy a set of cells from Sheet10 of another document named “Info.xlsx”

‘ and inserting the achieved totals into the “Vyvody” sheet of the active document with the name

‘ «ActWb.xlsm»

Dim ActWb As Workbook

Set ActWb = Workbooks.Open(«C:Info»)

‘ Don’t forget that ActWb is an active workbook.

‘ Therefore, the following code calls the ‘Sheets’ object in this active workbook.

Sheets(«Sheet10»).Range(«A10:B20»).Copy

‘ The results are pasted from the copied range into the “Vyvody” sheet

‘ active workbook. Important, ActWb is not currently the active workbook, so it needs to be specified.

Workbooks(«ActWb»).Sheets(«Vyvody»).Range(«A1»).PasteSpecial  Paste:=xlPasteValues

Example 3

The following code snippet demonstrates how to work with the Columns object from the current Worksheet object. Among other things, it demonstrates how references to the active object can be omitted by specifying a specific cell or a set of them within it.

This example also demonstrates the use of the Set keyword, which is used here to assign a range to the “Col” variable.

This code shows, among other things, how to edit the contents of the cells.

‘ Loop through the cells in column A of “Sheet2”,

‘ performs arithmetic operations on each value and indicates the result in column A of the current active sheet (“Sheet1”)

Dim i As Integer

Dim Col As Range

Dim dVal As Double

‘ Assigning the ‘Col’ variable to column A of sheet 2

Set Col = Sheets(«Sheet2»).Columns(«A»)

i = 1

‘ Loop through each cell in the ‘Col’ column until

‘ will not find an empty cell

Do Until IsEmpty(Col.Cells(i))

‘ Perform arithmetic operations on the value in the current cell

dVal = Col.Cells(i).Value * 3 — 1

‘ This command copies the result to column A

‘ of the current active sheet – no need to specify which

Cells(i, 1).Value = dVal

i = i + 1

loop

Thus, objects allow you to programmatically perform a wide variety of actions with spreadsheets. This can be used in different situations. For example, when it is necessary to adapt a table to the needs of a particular company, and the existing functionality is not enough. Also, such macros can be used to automate a large number of accounting transactions that are repeated every period (month, quarter or year).