Macros and Programming

with VBA Microsoft Excel Course Advanced Level

FOURTH PRACTICE

✓ Identify objects and collections in Excel

OBJECTIVES ✓ Recognize the hierarchy of objects
✓ Describe objects through the Object Inspector.
✓ Referencing objects from the Immediate window.

THE EXCEL OBJECTS

Excel offers a wide variety of objects that allow you to automate and customize daily work.
Among the main objects we have:
• The Application object, which is the top-level object. Example: Excel, Word, PowerPoint, etc.
• The Workbook object refers to the different open workbooks. Example: Book1, Book2, etc. It depends on
Application object
• The Worksheet object refers to the set of sheets in a workbook. For example: Sheet1, Sheet2, Sheet3, etc.
It depends on the Workbook object
• The Range object refers to a cell or a range of cells. Example: A1, A10:A20, etc. It depends on the
Worksheet object.

OBJECT COLLECTIONS
They are the set of similar objects. A collection is a group of the same class and this collection by itself
it is an object. Examples:
• The book called Workbook1 is a Workbook object.
• The book called Libro2 is a Workbook object.
• The set of these objects Workbook makes up a collection called: Workbooks
• The sheet named Sheet1 is a Worksheet object.
• The sheet called Sheet2 is a Worksheet object
• The set of these Worksheet objects forms a collection called: Worksheets.
• An open window is called a window object.
• Another open window is called the window object.
• The set of window objects forms a collection called: Windows
• Charts is a collection of Chart objects.
• Names is a collection of name objects.
In summary, it can be said that Excel offers more than 100 classes of objects.

HIERARCHY OF OBJECTS
Objects are manipulated using VBA code.
Objects can act as containers for other objects.
The basic unit of Excel is the Workbook object.
Therefore, to reference the objects, a hierarchical structure of the objects must be followed, that is,
which object should be referenced first, then who second, and so on. Although,
In some cases, the reference to the superior objects can be omitted; it is advisable to write the reference.
complete.

Examples:
1. Refer to a book called: [Link], in the following way:
[Link]("[Link]")
Explanation: This instruction refers to the book: [Link], from the Workbooks collection that is
find within the Application object of Excel.
2. Refer to the sheet named: Sheet1, of the invoice [Link], in the following way:
[Link]("[Link]").Worksheets("Sheet1")

Institute of Computer Science and Telecommunications Page.~ 1 ~ Lic. Manuel Mendoza M.

Phone. 52 - 583000
 Macros and Programming
with VBA Microsoft Excel Course Advanced Level

3. Delving even further, we can refer to cell A1 of sheet1 of the [Link] workbook like this:
[Link]("[Link]").Worksheets("Sheet1").Range("A1")
As can be seen, to reference a contained object, it is done through a dot to separate.
the containers of the members.

THE OBJECT INSPECTOR

It is a tool that lists all the properties and methods of each available object. The list
The dropdown in the upper left corner of the object examiner includes a list of all libraries.
objects that are accessible: Excel, Office, VBA, etc.
RECOGNIZING OBJECT CLASSES, METHODS, AND PROPERTIES
1. From the standard toolbar, click on the Object Explorer button (or press F2)
2. From the search dropdown, type the object class Sheets and click the Search button.
3. From the Object Explorer window, you can see two lists:
• The one on the left with the names of the object classes
• And from the right with the members (or methods and properties), also constants and/or events

4. Indique cinco métodos y cinco propiedades de la clase objeto:sheets(si hay eventos, indicar unos cuantos)
Methods Property Events
Add Count There isn't
Copy Creator
Delete Item
Move Parent
Select Visible

TASK (Note that there may be methods and properties with the same name)
1. Indicar cinco métodos, cinco propiedades (si hay eventos, mencionarlos) de la clase objeto:Workbook
Methods Property Events

2. Indicar cinco métodos, cinco propiedades (si hay eventos, mencionarlos) de la clase objeto:Worksheet
Methods Property Events

Institute of Computer Science and Telecommunications Page ~ 2 ~ Lic. Manuel Mendoza M.

Phone. 52 - 583000
 Macros and Programming
with VBA Microsoft Excel Course Advanced Level

PRACTICAL ACTIVITIES

IN THE IMMEDIATE WINDOW, EXECUTE THE FOLLOWING INSTRUCTIONS

THE BOOK OBJECT AND ITS PROPERTIES
First, we open about three books (for example: Grá[Link], Documentació[Link], and [Link]).
Proceed to open a new blank workbook and while in Workbook1, press Alt+F11 to enter Excel's VBA.

1. How many books are open? Write the command:

? [Link].

The following result is obtained:

2. How to know what those 4 open books are?

To refer to the first of the open books, we write: ? [Link](1).Name

Explanation: Let's read the instruction from right to left.

It is a property for a single book.
unique element of the collection. Workbooks: refers to the collection of books.

Refer to the second open book: ? [Link](2).Name

Refer to the third open workbook: ? [Link](3).Name (View AdvancedFilters)

Refer to the fourth open workbook: ? [Link](4).Name (Visualize Book1)

3. Closing books. While in Book1, close Book3 (note its item number is 3), giving the instruction.
next: Workbooks(3).Close

Before closing a Book, check what item number it occupies in the sheet names part of the book.
What do you want to close with the command [Link]?
4. Another way to close a workbook is by referring to the active workbook: [Link].
In the project explorer, select book6 and in the immediate window type:

THE OBJECT SHEET AND ITS PROPERTIES

1. How do we add a new sheet? Writing: Add Worksheets
Sheet1 by default occupies item 1 or position 1. When adding a new sheet, it
is located at the beginning of the other sheets, therefore the inserted Sheet (Sheet2) occupies item 1 or position 1.
2. How do I view the name of the sheet that is at the beginning or in position 1?
Writing:? [Link](1).Name (Will result in Sheet2)
3. Add three new sheets (the last added sheet is Sheet5, so this sheet occupies position 1).

Institute of Computer Science and Telecommunications Page 3 Lic. Manuel Mendoza M.

Phone. 52 - 583000
 Macros and Programming
with VBA Microsoft Excel Course Advanced Level

4. Activate Sheet1 and execute the other commands.

5. Visualize the first sheet:? [Link](1).Name (Will show Sheet5)
6. How do I view the name of any sheet?
? WorkSheets("Sheet2").Name
7. How do I change the name of a sheet, for example from Sheet4 to 'My sheet'?
First, I check the item number of the sheet. (Note that Sheet4 is in item 2 or position 2)
Worksheets(2).Name = "My sheet"
8. How do I delete a sheet? Suppose you want to delete Sheet5 whose item is 1.
WorkSheets(1).Delete (visualizes a question, proceed to delete by pressing ENTER)
9. How do I select a sheet? For example, the sheet 'My sheet.'
Select Worksheets("My sheet")
10. How many pages are there in total in the active book? ? [Link]

THE RANGE PROPERTY

Returns a Range object. It applies to two types of objects: a Worksheet object or a Range object.
The Range property can also work as a Range object (in this case, the Range object is the cell)
Active). That is, it treats the activated cell object as if it were cell A1. See the examples in section III.5.
I. WAYS TO SELECT AND/OR ACTIVATE A CELL IN ACTIVE SHEET
Referencing fully from the Excel object or application
[Link]("book1").Worksheets(1).Range("A5").Activate
(From the Excel application it refers to a workbook called 'book1' and from this object it does
reference to a sheet object ("My sheet" which was selected earlier) and that is located in the
first position of the sheets and from that sheet through the property Range, refers to the range
or cell A5 making it active to that cell through the Activate method.

2. Complete reference from the book object

Select Range A6 in Worksheet hoja1 of Workbook libro1.
From the book called 'libro1', it refers to the sheet called 'hoja1' and from this, through the
property Range refers to the range or cell A6 and selects it through the Select method.

3. Complete reference from the sheet object

Activate cell A10 on the worksheet 'My sheet'.
(From the active book, it refers to the sheet called 'sheet1' and from this, it refers to the object
range or cell A10 and activates it through the Activate method.

4. Reference from the active sheet from the range object.

Select cell B1
(From the active workbook and the active sheet, it refers to the range or cell B1 and selects it).

5. Other ways to select and/or activate a cell in the same workbook and same sheet.
worksheets(1).cells(4,2).activate
worksheets(1).cells(8,1).activate
activate cells(10,3)
select cells(6,1)
select cells(8,2)
select cells(15,3)
select cells(6,1)
[Link](0,2).Select
Select the cell that is 3 rows below the active cell.
Select the cell that is 5 rows down from the active cell.
[Link](0,-1).Select
Select the cell that is 5 rows above the active cell.
As can be seen, if you know the sheet and the book you are working in, you can start omitting the
reference to these objects (application, workbooks, and worksheets). But if you are going to work with different sheets and
different books, will have to reference the hierarchy of all objects.

Institute of Informatics and Telecommunications Page 4 Lic. Manuel Mendoza M.

Phone. 52 - 583000
 Macros and Programming
with VBA Microsoft Excel Course Advanced Level

II. WAYS TO SELECT A RANGE OF CELLS IN THE ACTIVE SHEET

worksheets(1).range("B1:B10").select
2. worksheets(1).range("A1:A10").select
Select range("b5:b10")
4. Select range("b11,b15")

III. WAYS TO ASSIGN VALUE TO A SPECIFIC CELL

Complete reference from the Excel object
[Link]("Book1").Worksheets(1).Range("A4").Value = 20
(From the Excel application, it refers to an object called 'Workbook1' and this refers to the
first position of the sheets and from that sheet it refers to the range object A4 and through the
propertyValue, assigns the value of 20 to cell A4.
2. Complete reference from the book object
workbooks("Book1").Worksheets(1).range("A4") = 30
3. Full reference from the sheet object
worksheets("My sheet1").range("A4") = 18
4. References to a cell from the range property
range("A4").Value=100
range("A4") = 200
range("A4").Value = datevalue("15/02/2012")
range("A4") = datevalue("20/10/2012")
5. Reference from an activated Range object (Be careful with this notation form)
For example, activate cell B3; this cell is the active cell and will be treated as if it were A1.
[Link]("C3") = 10. (The value 10 is assigned in cell D5)
[Link]("E5") = 15. (The value 15 is assigned in cell F7)

THE PROPERTY CELLS and OFFSET

I. Ways to assign value to a specific cell
References from the property: Cells
cells(4,1)=500
Advanced Excel
cells(4,1).value = date
datevalue("21/06/2011")

2. Ways to assign value to an active cell

(First, click on cell B4, this will be the active cell)
[Link]=300
activecell.FormulaR1C1 = 500
activecell = 1000
[Link](0,0).value = 250
My name is Carlos
[Link](0,0) = datevalue("25/12/2012")
activecell(1,1) = 20

3. Ways to assign value in any cell from the active sheet and active cell. Click on Sheet1.
[Link](3,4) = 50. (Enter the value in cell D3, that is, row 3, column 4).
[Link](6,4) = 50
[Link](8,2)=11
[Link](10,3)=30
worksheets("Sheet1").cells(7,1) = 25
worksheets("Sheet1").cells(5,2) = 30

4. Ways to assign a value to a range of cells

First proceed to delete all the data from sheet1
range("a6") = 1003 : range("b6") = "bio limon" : range("c6") = 2.00 : range("d6") = "12/05/2011"
[Link]

Institute of Computer Science and Telecommunications Page ~ 5 ~ Lic. Manuel Mendoza M.

Phone. 52 - 583000
 Macros and Programming
with VBA Microsoft Excel Course Advanced Level

PROPERTIES OF ROWS AND COLUMNS

The property Columns returns a collection of columns
Select all cells in a spreadsheet 2. Select an entire column

3. Select column B 4. Select columns B to D

The property Rows returns a collection of rows

Select all cells in a sheet 2. Select rows 3, 4, and 5

OTHER PROPERTIES OF THE RANGE OBJECT. WORKING WITH TABLE REGIONS

First proceed to open the file [Link] and go to the orders sheet.

Activate cell B3 and then select the entire region of that table only.

The result will be the entire region of the table:

2. Select cell D3 and then select the entire column D

The result of the selected column is displayed in this way:

Institute of Computer Science and Telecommunications Page 6 Lic. Manuel Mendoza M.

Tel. 52 - 583000
 Macros and Programming
with VBA Microsoft Excel Course Advanced Level

3. Select the entire region of the table through a variable

END OF THE PRACTICE

Institute of Informatics and Telecommunications Page 7 Lic. Manuel Mendoza M.

Phone. 52 - 583000