CODEWITHSUNDEEP
excelvbaactivex
Martin J
Martin J

Reputation: 13

Compiler Error when creating and editing OLEObject in same function

When running the function it seems like it doesn't create the DropDown menu before it tries to add items, and therefore it runs into the compile-time error:

Method or data member not found

Highlighting the SomeNameToo identifier.

screenshot of compile error

If I put the two "AddItem" lines into another function and run that afterwards, then there's no problem, and the two lines are added.

Is there a way of making it create the object before running the following code? This saves me having a lot of functions.

Sub hey()

ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, Left:=0, Top:=0, Width:=100, Height:=20).Name = "SomeNameToo"

Sheet1.SomeNameToo.AddItem "Item 1"
Sheet1.SomeNameToo.AddItem "Item 2"

End Sub

Upvotes: 1

Views: 232

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71157

The object doesn't exist at compile-time; SomeNameToo doesn't exist when the code is being compiled, only after the OLEObjects.Add method has executed.

The Add method returns a reference to the object being created.

You are accessing that object, once - here:

.Name = "SomeNameToo"

...and then the object is up in the air, with nothing to hold on to it in that procedure's scope.

Declare an OLEObject variable to hold that reference:

Dim oleControl As OLEObject
Set oleControl = ActiveSheet.OLEObjects.Add(...)

Then use that object:

oleControl.Name = "SomeNameToo"

Or, capture the returned reference using a With block - then you don't need a local variable:

With ActiveSheet.OLEObjects.Add(...)
    .Name = "SomeNameToo"
End With

Now, you can't invoke AddItem against that object, because an OLEObject isn't a ComboBox and doesn't know anything about an AddItem method. You want to work with the wrapped MSForms ComboBox control - that's OLEObject.Object:

With ActiveSheet.OLEObjects.Add(...)
    .Name = "SomeNameToo"
    With .Object
        .AddItem "Item 1"
        .AddItem "Item 2"
    End With
End With

Or, with local variables:

Dim oleControl As OLEObject
Set oleControl = ActiveSheet.OLEObjects.Add(...)
oleControl.Name = "SomeNameToo"

Dim msFormsControl As MSForms.ComboBox
Set msFormsControl = oleControl.Name
msFormsControl.AddItem "Item 1"
msFormsControl.AddItem "Item 2"

Note that without local variables, you're coding against Object, which means everything is late-bound and can't be validated at compile-time: a typo will result in error 438.

With local variables and declared types, everything is compiler-validated and you get IntelliSense to assist as you type.

Upvotes: 2

Related Questions