VBA UserForm Object

Question

Hi, I'm really struggling with something here. I have a class module, let's call it FormMan which has a bunch of methods relating to the large number of userforms I have in my project. One particular method is to be called from a lot of different places and is pretty simple - it simply adds a user defined number of controls to a form and extends the forms height to accommodate these new controls.

The user passes the number of controls and the userform.

oF.AddControlsToForm iNumberOfControls,frmTest

In FormMan class module:

Public Sub Addcontrols(iNum as integer, oForm as userform)

//stuff happens here, oForm is used extensively

oForm.Height = i  //object does not support this property or method
frmTest.Height = i //works

oForm.Show //object does not...
frmTest.show  //works

end sub

In the Locals window, oForm does not have a height property, so fair enough. But oForm has been defined as frmTest. I can say oForm.BackColor = vbred, and I can set ctl = oform.TextBox1 for example

This is meant to be a generic procedure that can add a bunch of controls to whatever form. I've tried loading and showing the form before assigning it to oForm.

Why are height and show properties and methods of userforms but not of objects declared as userforms? What am I doing wrong?

Really appreciate any help.

Answer 1

The problem here is that the UserForm and frmTest objects are not of the same type. In fact, frmTest is a subtype of UserForm that extends it by adding the Height property, amongst other members.

You'll probably have to resort to declaring your function parameter as Object.

Public Sub Addcontrols(iNum as integer, oForm as Object)

This should work as you desire, although unfortunately you'll be sacrificing type safety. Most OO concepts tend to fall apart in the context of VBA.

Answer 2

The thing first and foremost to remember about VBA is that it's quasi-OO, not full-OO. I've run into a lot of wacky problems around this issue and so I now tend to avoid relying entirely on VBA's consistency.

That being said, try this out:

Dim fname = oForm.Name (or whatever the property is for this subobject)
With VBA.UserForms.Add(fname)
   .Height = x
   .Show
End With

Isn't it so pleasantly bizarre?!?! I don't have a code example ready and waiting, so I can't guarantee success, but this feels like the right approach.