Having a variable reference an object in VBA

Question

I'm writing some VBA code to dynamically position some elements on a form. What I would like to do is create a two dimensional array of references to 11 rows of label and text box objects, then step through the array and position each "row" differently based on the properties of the above rows.

Unfortunately, I cannot seem to find a way to store the references! The following code produces the error: "Object doesn't support this property or method"

Dim fields(10,10) 
fields(0,0) = txtFirstName

If I assign an object to a single variable like so

Dim field 
field = txtFirstName
MsgBox field.Top

Then I get no error message from the assignment, but the reference to field.Top gives me the error "Object Required".

Please, what am I doing wrong here. I'm new to stack overflow and VBA coding so forgive me if this is an easy fix, but I've leveraged my google-fu and I'm just not finding a solution. Many thanks to anyone who can help.

You should declare your variables with specific type, e.g., Dim field As Object, and then per @Tom Cannaert's response, use the Set keyword when assigning an object variable. — David Zemens
– David Zemens, Commented May 3, 2013 at 16:45

Tom Cannaerts · Accepted Answer · 2013-05-03 16:44:34Z

3

You need to add the Set keyword, since you are dealing with object references.

Set fields(0,0) = txtFirstName

The reason you get the error, is because VBA will default to the text property of the textbox, giving you a string instead of the textbox object.

answered May 3, 2013 at 16:44

Tom Cannaerts

6481 gold badge5 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Having a variable reference an object in VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related