Programming Excel with VBA and .NET

Activate Objects: Get Names and Values

You can use the Activate method to set focus on the following objects:

Chart

ChartObject

OLEObject

Pane

Range

Window

Workbook

Worksheet

Usually, you’ll use Activate in combination with one of the collection methods to set focus on a member of the collection. For example, the following line sets focus on the first sheet in a workbook:

    Sheets(1).Activate

Similarly, the following line sets focus on the last sheet:

    Sheets(Sheets.Count).Activate

As mentioned previously, not all objects have a Name property. In those cases, the Caption or Address property is sometimes equivalent. The following procedure uses exception handling to return the name of an object of any type:

    Function GetName(obj) As String
        Dim res As String
        ' Use exception handling in case object
        ' doesn't support Name property 
.
        On Error Resume Next
        res = obj.Name
        If Err Then res = obj.Address
        If Err Then res = obj.Caption
        If Err Then res = obj.Index 
 
 
 

        If Err Then res = "no name"
        On Error GoTo 0
        GetName = res
    End Function

Some objects (such as points within a chart series) don’t have any identifiers. In that case, GetName returns no name. The Range and Hyperlink objects use the Address property, and the following objects have Caption properties:

1 These objects are part of the Microsoft Forms object library that ships with Excel.
Application	AxisTitle	Characters
ChartTitle	CheckBox 1	CommandButton 1
DataLabel	Frame 1	Label 1
OptionButton ...