Treat Userform as a Function

Question

I have created a VBA UserForm which is essentially a glorified input box

Just like an input box can be employed like this

Dim returnVal As String
returnVal = InputBox("Write some string")

I would like my userform to run like this

Dim returnVal As customClass
Set returnVal = MyUserForm([some arguments])

ie. the MyUserForm() code passes some arguments to the userform, and when the userform is closed, it gets some arguments back (in the form of a custom class rather than a plain string)

What's the best way of structuring my userform to allow this functionality?

Currently, I'm just declaring some variables and the custom class publicly. I'm catching command button clicks and Query_close() events to hide the form, then I read the outputVal and close the form completely. I don't like this because I'd like my form to be totally self contained, and I think the capturing of events is messy.

In simplified code (read/return a string):

Function myUf(inVal As String) As String
Dim frm As New frmTest
frm.inputval = inVal
frm.Init 'sets caption. We cannot rely on userform initialize as this runs before inputval is set
         'We could pass a variable here to save writing to the public variable
frm.Show
myUf = frm.outputVal
Set frm = Nothing
End Function

And in my Userform called frmTest with a textbox called tb1

Public inputval As String
Public outputVal As String

Public Sub Init()
Me.Caption = inputval 'setting caption, but could pass this anywhere
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode <> 1 Then Cancel = 1
outputVal = tb1 'reading value from textbox, but could return anything here
Me.Hide
End Sub

score 2 · Answer 1 · edited Sep 21 '17 at 18:03

You need to find a way to initiate the UserForm from a ClassObject. Then, you can use a simple factory pattern to create the UserForm exactly the way you want.

In general, I have copied a bit of the code of Mat's Mug somewhere in StackOverflow and I wrote an article about the User Forms. If you take a look here (http://www.vitoshacademy.com/vba-the-perfect-userform-in-vba/) you will find a way to initialize the form with Public Sub ShowMainForm() It's possible to add a parameter to the ShowMainForm, then pass it to the initializer of the class.

In general, take the code from the article, make sure it works, and change the ShowMainForm initializer to the following:

Public Sub ShowMainForm(strText As String, strText2 As String)

    If (objPresenter Is Nothing) Then
        Set objPresenter = New clsSummaryPresenter
    End If

    objPresenter.Show
    Call objPresenter.ChangeLabelAndCaption(strText, strText2)

End Sub

Then, if you call like this in the immediate window:

call ShowMainForm("Just","testing")

You will get this:

Which is quite what you need. :)

appreciated your article+. It could be of some interest to have a look at my recently posted question how to "destroy a *modeless* userform instance properly", as I referred to Mat's Mug's MVP concept, too (see https://stackoverflow.com/questions/47357708/vba-destroy-a-modeless-userform-instance-properly). — T.M., Dec 14 '17 at 11:57

score 0 · Answer 2 · answered Jul 04 '17 at 10:50

The basic idea is:

Create a Function in witch you combine your arguments to a string like:

strOpenArg = "param1:=value1;param2:=value2;"

than open the form with the OpenArgs

DoCmd.OpenForm "UserForm", acNormal, , , , acDialog, strOpenArgs

get your value and close the Form

Value= Form_UserForm.Value

DoCmd.Close acForm, "UserForm", acSaveNo

in the UserForm set Form_open. Here you can get your parameters.you can devide this by string splitting. Set also an OK Button, where you make the form just invisible and set the return value

Private Sub Form_Open(Cancel As Integer)
     Dim strParameter as String
     strParameter = Me.OpenArgs 'Here are your parmeters
End Sub

Private Sub ok_Click()
    m_Value = "Your ReturnValue"
    Me.Visible = False
End Sub

Private m_Value As String
Public Property Get Value() As String
    Value = m_msgBoxResult
End Property

This is code for an MSAccess form, not code for a VBA Userform. — jkpieterse, Jul 04 '17 at 11:05

score 0 · Answer 3 · answered Jul 04 '17 at 11:09

There's no way to one-liner the code like you want to, unfortunately. If all your userform code is self-contained then the only way for it to pass values out is to change the values of public variables. Mat's Mug's answer here is the layout I usually use when trying to simulate functions like 'InputBox' but you still can't get it in one line without writing a separate function. Using userform properties allows you to contain more of your code within the form itself.

Treat Userform as a Function

3 Answers3