Читать книгу Excel 2019 Power Programming with VBA - Michael Alexander, Dick Kusleika - Страница 120

In this section, we cover some additional essential concepts for would-be VBA gurus. These concepts will become clearer when you work with VBA and read subsequent chapters:

 Objects have unique properties and methods. Each object has its own set of properties and methods. Some properties and methods are common to various objects. For example, many objects in Excel have a Name property and a Delete method.

 You can manipulate objects without selecting them. This idea may be contrary to how you normally think about manipulating objects in Excel. After all, to work with an object in Excel, you have to select that object manually first, right?Well, this is not so when using VBA. It's usually more efficient to perform actions on objects without selecting them first.However, when you record a macro, Excel records every step you take, including selecting objects before you work with them. These are unnecessary steps that may make your macro run more slowly. You can generally remove the lines of code in your recorded macro that selects objects.

 It's important that you understand the concept of collections. Most of the time, you refer to an object indirectly by referring to the collection in which it's located. For example, to access a Workbook object named Myfile, reference the Workbooks collection as follows: Workbooks("Myfile.xlsx")

This reference returns an object, which is the workbook with which you're concerned.

 Properties can return a reference to another object. For example, in the following statement, the Font property returns a Font object contained in a Range object. Bold is a property of the Font object, not the Range object.

Range("A1").Font.Bold = True

 You can refer to the same object in many ways. Assume that you have a workbook named Sales, and it's the only workbook open. Then assume that this workbook has one worksheet, named Summary. You can refer to the sheet in any of the following ways:

Workbooks("Sales.xlsx").Worksheets("Summary") Workbooks(1).Worksheets(1) Workbooks(1).Sheets(1) Application.ActiveWorkbook.ActiveSheet ActiveWorkbook.ActiveSheet ActiveSheet

The method that you use is usually determined by how much you know about the workspace. For example, if more than one workbook is open, the second and third methods aren't reliable. If you want to work with the active sheet (whatever it may be), any of the last three methods would work. To be absolutely sure that you're referring to a specific sheet on a specific workbook, the first method is your best choice.