VBA set an object property by name instead of index

Question

I'm not sure how to ask this question so I don't know how to search for the answer. This is my first time playing with OOP and I'm in over my head as usual.

So, In VBA Excel you can reference a sheet by index which you can do in two ways, right?

Sheets (1)
    'or
Sheets ("Sheet1")

I am trying to do the same with my objects and collections instead of referencing an index property and I don't know where to begin. Example:

    Set objCar("MyCar").color = "Blue" 'from a collection of Cars

I'm sure I have a knowledge deficit, I'm still trying to wrap my head around whats going on with oop.

http://stackoverflow.com/questions/1309689/hash-table-associative-array-in-vba — Kirill Leontev, Sep 09 '13 at 02:35

score 1 · Accepted Answer · answered Sep 09 '13 at 00:27

Almost..............You use Set to create a specific instance of the object. You can then color that instance. Say the object is a Shape:

Sub dural()
    Dim S As Shape
    Set S = ActiveSheet.Shapes(1)
    S.Fill.ForeColor.RGB = RGB(128, 0, 0)
    '
    '  Or
    '
    S.Name = "MyCar"
    ActiveSheet.Shapes("MyCar").Fill.ForeColor.RGB = RGB(0, 100, 0)
End Sub

VBA set an object property by name instead of index

1 Answers1