Essential concepts to remember
In this section, I add some more concepts that are essential for would-be VBA gurus. These concepts 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 objects, however, share some properties (for example, Name) and some methods (such as Delete).
♦ You can manipulate objects without selecting them.
This might be contrary to how you normally think about manipulating objects in Excel, especially if you've programmed XLM macros. The fact is that it's usually more efficient to perform actions on objects without selecting them first. When you record a macro, Excel generally selects the object first. This is not necessary and may actually make your macro run slower.
♦ 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 that it's in. For example, to access a Workbook object named Myfile, reference the Workbooks collection as follows:
Workbooks("Myfile.xls")
This reference returns an object, which is the workbook with which you are 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:
Range("A1").Font.Bold = True
♦ There can be many different ways to refer to the same object.
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.xls").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, more than one workbook is open, the second or third method is not 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.
Average user rating: 5 stars out of 1 votes
Post a comment