Microsoft Office Tutorials and References
In Depth Information
Properties
When you add an object to your workbook with VBA — for example, if you run a macro that
creates a chart — VBA is at work behind the scenes, storing information about that Chart object, and
assigning default values to its properties that were not specified in the macro. I mention this as a
piece of good news, because with VBA filling in the blanks as it does, it’s that much less about VBA
you need to learn in order to start writing advanced macros. This advantage will become clearer as
you progress into more complex programming techniques.
properties
As noted earlier, VBA objects have inherent qualities, called properties , similar to any objects you may
deal with in the real world. Properties define what the object looks like and how it acts. If you own
a red bicycle, you can change its Color property by painting the bicycle a different color. For a Cell
object on a worksheet, you can change its color property by formatting the cell with a different color.
In VBA code, you refer to the property of an object by first referring to the object, then the property,
separated by a dot. Following are examples of a few of the many properties belonging to the Cell ,
Worksheet , and Workbook objects:
This line of code would format the active cell’s Locked property:
ActiveCell.Locked = True
The Name property of the Worksheet object represents the worksheet’s tab name. For
example, this expression in the Immediate window would return the name of the active worksheet:
? ActiveSheet.Name
This expression would change the Name property of the active worksheet to “Hello,” and
when executed would result in “Hello” being the active worksheet’s new tab name:
ActiveSheet.Name = “Hello”
This expression will change the Color property of the active worksheet’s tab to yellow:
ActiveSheet.Tab.Color = vbYellow
Workbooks have a Saved property that indicates if the workbook has been saved since its
most recent change. For example, if you save your workbook and then enter the following
expression in the Immediate window, VBA will return True:
? ThisWorkbook.Saved
If you were to make some change to the workbook, such as entering a number in a cell, and
immediately re-evaluate the expression ? ThisWorkbook.Saved , False would be returned
because VBA knows that the workbook has not been saved since it was last changed.
Search JabSto ::




Custom Search