Pass userform reference to public function

I am setting up some vba userforms for grabbing info in Microstation and storing it in an access database.  Since the forms will be doing the same thing for different data, I wanted to write public functions for the form actions (clear form, check for duplicate records, etc)  So I tried to pass the "Me" keyword to the function with:

Public function clearform( frm as form)

for each ctl in frm.controls



end function

in the forms button I would have

clearform(me)  'to call the funtion.

I am getting type mismatch error.

I don't know if I am missing a reference,  I've tried different variations like "frm as userform"

If anyone had an example I would greatly appreciate it

Thanks in advance


  • This is a tough one.
    The UserForm class is a base class from which your implemented form is a descendant.
    VBA creates your userform as a seperate class with its own interface behind the scenes.
    Unfortunately, they did not expose the interfaces you would expect for a UserForm.
    Actually, I think the hidden "must inherit" class is _Form which inherites from UserForm.
    Why they did it this way is beyond me since so many other aspects of VBA have been made so very user friendly.

    You could pass it simply as an Object and hope that late binding works.
    This is how / why RobertArnold's example works.

    As usual, I can't just leave it there and will continue on with possibly more info than you cared for :-) ...

    If you wish to use early binding, there is another practical option but maybe not as straight forward as we feel it "should" be.
    Since VBA does not expose the desired interface, you could just create your own and implement it.
    VBA does not support inheritance or creating true interfaces, however it is very good at polymorphism, which is just a fancy way to say that it fakes it really well.

    Create your Interface as a class with only methods that will be common to all your forms.
    In this case I created a class call IMyStandardDataForm:

    ' IMyStandardDataForm
    Public Function GetControls() As Controls
        'Interface Only, no code
    End Function

    Now in the code for each of your forms you can add a line at the top:

    Implements IMyStandardDataForm

    This tells VBA you plan to implement this interface; which of course you must now do by adding a method similar to:

    Private Function IMyStandardDataForm_GetControls() As MSForms.Controls
        Set IMyStandardDataForm_GetControls = Me.Controls
    End Function

     You can then create a public method in a module designed to accept objects that support this interface. For example:

    Public Sub PrintFormControlNames(ByVal frm As IMyStandardDataForm)
        Dim ctl As Control
        For Each ctl In frm.GetControls
            Debug.Print ctl.Name
    End Sub

    Now from within any of your forms that have implemented the IMyStandardDataForm interface as above, you can now call this shared public method as desired:

        PrintFormControlNames Me

    You might find all that to be more trouble than it's worth, but it works quite well and the way I usually do it.


Reply Children
No Data