Userform for User Defined Function VBA - Numerical inputs

[jdmc45]

Hi all,

I have made a function in VBA that requires a Yes or No answer, and 9 numerical inputs for calculation. I would like to create a nice looking userform so the inputs can be inputted that way, also gives the user information about what each variable is.

Is there a way to create a userform for a UDF? I am having trouble finding any information about it.

Thanks in advance everyone!

 

[jdmc45]

Any assistance is much appreciated!

 

[JoeMo]

Is the UDF intended to be used on worksheets as a function that is entered in a cell?

 

[jdmc45]

Is the UDF intended to be used on worksheets as a function that is entered in a cell?

That's right yes

 

[JoeMo]

I'm under the impression that a UDF used within a worksheet can only return a value. It cannot open a user form to get values for its arguments.

 

[mikerickson]

This can be done. Here's a simple example.

The userform has one textbox and two buttons, butOK and butCancel.
The UF code is this:

Private Sub butCancel_Click()
    Unload Me
End Sub

Private Sub butOK_Click()
    Me.Hide
End Sub

The UDF, in a normal module, is

Function UFValue(Optional Trigger As Range) As String
    Dim dft As String

    If TypeName(Application.Caller) = "Range" Then dft = Application.Caller.Cells(1, 1).Text

    With UserForm1
        .Tag = "x"
        .TextBox1.Text = dft
        .Show
    End With
    
' break point

    If UserForm1.Tag = "x" Then
        Rem OK button pressed
        UFValue = UserForm1.TextBox1.Text
    Else
        Rem Cancel button pressed
        UFValue = dft
    End If
    Unload UserForm1
End Function

It just returns the value entered into the text box.

Put =UFValue(A1) in B1.
The userform will appear. Enter "Smith" into the box and press OK. "Smith" will be seen in B1
change the value in A1. The userform will appear again, with the text box already filled with "Smith"
Type "Jones" into the textbox and press Cancel. "Smith" will be seen in B1.
If you had pressed OK after typing "Jones" in the text box, "Jones" would be in B1.

One key is the Trigger argument. Even though Trigger does not play a part in the calculations, it is necessary to force the UDF to be calculated. Without it, the UDF would run when entered into a cell, but never after that.

The other key is the break point.
The userform refered to before that point might or might not be the same instance of Userform1 as the one after that point, depending on if OK is clicked or Cancel (or the corner X is used)
The "x" in the Useform1.Tag is used to determine which of those options are the case.
Note, setting the "x" cannot be done in the Userform_Initialize event, since that always will run. You could however set it in the Userform_Activate event rather than in the UDF code.

Your code for extracting the proper data from the 8 text boxes on your form would differ, of course.

 

[mikerickson]

On the other hand, cells formulas ususaly respond to cell values. It would be more intuitive, more in line with the rest of Excel, if the UDF got its values either hard-coded or from cells, rather than from a UserForm.

 

[jdmc45]

On the other hand, cells formulas ususaly respond to cell values. It would be more intuitive, more in line with the rest of Excel, if the UDF got its values either hard-coded or from cells, rather than from a UserForm.

Thanks for your posts. From my understanding now a function is not the best way to go about it. Perhaps a subroutine is better.

I would like a userform though, maybe it is possible to press a button to start the sub, then the userform appears, enter the numerical values then press Ok and it returns a price to a cell on the spreadsheet.

Is it possible to input numerical inputs in textboxes of the userform for the function to calculate? Or how else would it be done? I have the function already coded, and the userform designed with all the inputs, how would I turn this into a subroutine?

Cheers!

 

[mikerickson]

It sounds like you have a userform with 9 textboxes that you want to get information from.
This approach follows the practice above, the userform has two command buttons, an OK button and Cancel.

'in the userforms code module

Private Sub butCancel_Click()
    Unload Me
End Sub

Private Sub butOK_Click()
    Me.Hide
End Sub

The sub should be put in a normal module and could be structured like this

Sub mySub()
    Dim value1 As Double, value2 As Double, value3 As Double
    Dim value4 As Double, value5 As Double, value6 As Double
    Dim value7 As Double, value8 As Double, value9 As Double

    With UserForm1: Rem set preliminary values
        .Caption = "Nine data point entry"
    End With

    UserForm1.Show: Rem point A

    With UserForm1: Rem point B
        value1 = Val(.TextBox1.Text)
        value2 = Val(.TextBox2.Text)
        ' ...
        value9 = Val(.TextBox9.Text)
    End With

    Unload UserForm1

    If (value1 = 0) And (value2 = 0) And (value3 = 0) _
        And (value4 = 0) And (value5 = 0) And (value6 = 0) _
        And (value7 = 0) And (value8 = 0) And (value9 = 0) Then

        MsgBox "user canceled"
    Else
        Rem your function code
    End If
End Sub

At line A, and above, the userform referred to in the code is the one that the user sees.

However, the UserForm1 at line B and below, may or may not be that same one.

With a modal userform, the line UserForm1.Show will not finish executing until either the userform is hidden (by the user pressing OK) or unloaded (user presses Cancel).

If the userform is hidden, it is still there, just invisible.
If the userform is unloaded, it is done with, its gone.

At line B, either UserForm1 is the same userform (hidden=OK), the textboxes still have the values that the user entered.
or
if that userform is gone (unloaded=Cancel), UserForm1 at B is a new instance, the textboxes have their default values (normally vbNullString)

Following the code onward, we set value1, value2, ..., value9 to the values entered into the text boxes, using Val to convert from strings to numbers.
Since Val(vbNullString) = 0.
If all those values are 0, that is a hint that the user Canceled, in which case, do nothing.
If not, then process the values.

If all 0 is a possible entry, another way to test whether it was Canceled or OKed is to use the userform's Tag property.

Sub mySub2()
    Dim value1 As Double, value2 As Double, value3 As Double
    ' ...
    Dim cancelFlag as Boolean

    With UserForm1: Rem set preliminary values
        [COLOR="DarkRed"].Tag = "X"[/COLOR]
        .Caption = "Nine data point entry"
    End With

    UserForm1.Show: Rem point A

    With UserForm1: Rem point B
        [COLOR="DarkRed"]cancelFlag = (.Tag = vbNullString)[/COLOR]
        value1 = Val(.TextBox1.Text)
        value2 = Val(.TextBox2.Text)
        ' ...
        value9 = Val(.TextBox9.Text)
    End With

    Unload UserForm1

    If cancelFlag Then
        MsgBox "user canceled"
    Else
        Rem your function code
    End If
End Sub

In this formulation, the Tag of the userform that the user sees ="X".
At point B, an OK UserForm1 (that the user saw) will have .Tag = "X",
while a new UserForm (because of Cancel) will have .Tag = vbNullString, the default value.
That is tested to see which button the user pressed.

I hope this helps.