Microsoft Office Tutorials and References
In Depth Information
Things to Know about Objects
h 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 ).
h You can manipulate objects without selecting them. This idea may be contrary to how
you normally think about manipulating objects in Excel. 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 step isn’t necessary and may actually
make your macro run more slowly.
h 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.xlsx”)
This reference returns an object, which is the workbook with which you’re concerned.
h 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
h You can refer to the same object in many different 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.
Learning more about objects and properties
If this is your first exposure to VBA, you’re probably a bit overwhelmed by objects, properties,
and methods. I don’t blame you. If you try to access a property that an object doesn’t have, you
get a runtime error, and your VBA code grinds to a screeching halt until you correct the problem.
Fortunately, there are several good ways to learn about objects, properties, and methods.
 
Search JabSto ::




Custom Search