Excel VBA UserForm Parameters: Pass and Return Values

To pass parameters to an Excel VBA UserForm, create an instance of the form, call a public setup procedure with the values, and then show it; read the user’s answers from public properties after the form closes. A UserForm’s Show method does not accept custom arguments, so a setup procedure and result properties provide a clear, reusable pattern.

What “parameters” mean in a UserForm

The word “parameters” can describe values going in to a form, values entered by a user, or both. For example, a report form might receive a customer name and a starting date, let the user change them, and return the final choices to a macro. It helps to distinguish these directions: inputs passed by the calling procedure are initial values, while values read back after the user responds are results.

A UserForm is a VBA class module with a visual interface. Its text boxes, combo boxes, check boxes, and buttons are controls; its code-behind contains event procedures and any public methods or properties you define. This makes it possible to treat a form as a small object with a setup method, a display step, and a result-reading step.

There is no built-in syntax such as frmParameters.Show customerName, amount. The supported Show method displays the form and can specify whether it is modal or modeless, but it is not a general-purpose argument list. Passing data through a custom public procedure avoids that misconception and makes the data flow explicit.

Create a UserForm and controls

In Excel for Microsoft 365 on Windows, enable the Developer tab through File > Options > Customize Ribbon and select Developer. Open the VBA editor with Developer > Visual Basic or Alt+F11. In the editor, select Insert > UserForm, then use the Properties window to name it frmParameters.

Add two text boxes named txtCustomer and txtAmount, plus buttons named cmdOK and cmdCancel. Set their captions to something understandable, such as “OK” and “Cancel.” Control names matter because the code refers to them directly. A label beside each text box also helps users understand what to enter; the label’s name does not affect the example.

Form: frmParameters
Text boxes: txtCustomer and txtAmount
Buttons: cmdOK and cmdCancel

These steps apply to desktop Excel. Excel for the web cannot create or run VBA macros. Excel for Mac has a VBA editor and supports UserForms in desktop versions, but control and feature compatibility can differ from Windows. Keep a form simple, avoid Windows-only API calls or assumptions about ActiveX controls, and verify it on the Mac if the workbook must work there. The VBA editor’s menus and keyboard shortcuts also differ on Mac; use the Mac application’s Visual Basic Editor command or menu rather than assuming the Windows ribbon path.

Pass values into the form with a setup method

Put the following code in the frmParameters code module. The public Configure procedure receives initial values, while private fields retain the answers and cancellation state. The form starts in the cancelled state so that an unconfirmed or dismissed form is not mistaken for a successful response.

Option Explicit

Private mCustomer As String
Private mAmount As Currency
Private mCancelled As Boolean

Public Sub Configure(ByVal initialCustomer As String, _
                     ByVal initialAmount As Currency)
    ' Load starting values and require an explicit OK click.
    mCustomer = initialCustomer
    mAmount = initialAmount
    mCancelled = True

    Me.txtCustomer.Value = initialCustomer
    Me.txtAmount.Value = CStr(initialAmount)
End Sub

Public Property Get WasCancelled() As Boolean
    WasCancelled = mCancelled
End Property

Public Property Get Customer() As String
    Customer = mCustomer
End Property

Public Property Get Amount() As Currency
    Amount = mAmount
End Property

Private Sub cmdOK_Click()
    ' Do not accept a blank customer or a nonnumeric amount.
    If Len(Trim$(CStr(Me.txtCustomer.Value))) = 0 Then
        MsgBox "Enter a customer name.", vbExclamation
        Me.txtCustomer.SetFocus
        Exit Sub
    End If

    If Not IsNumeric(Me.txtAmount.Value) Then
        MsgBox "Enter a numeric amount.", vbExclamation
        Me.txtAmount.SetFocus
        Exit Sub
    End If

    mCustomer = Trim$(CStr(Me.txtCustomer.Value))
    mAmount = CCur(Me.txtAmount.Value)
    mCancelled = False
    Me.Hide
End Sub

Private Sub cmdCancel_Click()
    ' Keep the instance available so the caller can inspect WasCancelled.
    mCancelled = True
    Me.Hide
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    ' Treat the title-bar X like Cancel instead of unloading the instance.
    If CloseMode = vbFormControlMenu Then
        Cancel = True
        mCancelled = True
        Me.Hide
    End If
End Sub

Configure is deliberately separate from UserForm_Initialize. The Initialize event is useful for defaults that do not depend on the caller, such as filling a list with fixed options. It has no custom parameter list. A caller-specific value belongs in a public setup procedure or property, which the calling code can set before showing the form.

Show the form and read its results

Put this procedure in a standard module, created with Insert > Module in the Visual Basic Editor. It creates a named form instance, supplies initial values, displays the form modally, and then checks whether the user confirmed the entries.

Option Explicit

Public Sub AskForParameters()
    Dim inputForm As frmParameters
    Dim customerName As String
    Dim orderAmount As Currency

    ' Start with illustrative values; replace these with workbook data.
    Set inputForm = New frmParameters
    inputForm.Configure "Northwind", 125.5
    inputForm.Show vbModal

    If Not inputForm.WasCancelled Then
        customerName = inputForm.Customer
        orderAmount = inputForm.Amount

        MsgBox "Customer: " & customerName & vbCrLf & _
               "Amount: " & FormatCurrency(orderAmount)
    End If

    Unload inputForm
    Set inputForm = Nothing
End Sub

Here, the form initially displays “Northwind” and 125.5. If the user leaves those entries unchanged and clicks OK, the caller receives the same customer and amount. If the user edits them, the edited values are returned instead. Clicking Cancel or the title-bar X leaves WasCancelled true, so the caller skips the success action.

In the code block, & represents VBA’s string-concatenation operator in HTML display; in the VBA editor, write the operator as a plain ampersand. More generally, copy VBA code as code rather than copying HTML entity text from a rendered page. Save a copy of the workbook before running a macro: VBA actions cannot be undone with Ctrl+Z.

Why use a named form instance?

VBA lets code refer to a UserForm by its default instance name, such as frmParameters.Show. That can be convenient for a simple form, but it hides when the object is created and makes it easier to retain stale values between uses. A variable declared as Dim inputForm As frmParameters makes the instance explicit and allows the caller to configure, display, read, and unload that particular object.

Creating a new instance is especially useful when the same form might be open more than once or when code needs to keep a form’s state separate. For an ordinary modal input dialog, one instance is usually enough. Unloading it after reading the results releases the form; hiding it alone keeps the instance loaded, which is why the example explicitly calls Unload inputForm after checking its properties.

Use vbModal when the calling procedure should wait until the user responds. This is generally the right choice for collecting inputs before continuing a macro. A modeless form, shown with vbModeless, allows the user to interact with Excel while the form remains open. That changes the control flow: the macro does not simply wait and read the results on the next line, so a modeless design needs event-driven handling.

Validate input and choose suitable data types

Text boxes return text, even when a user types digits. Convert and validate that text before using it as a number, date, or other typed value. The example uses IsNumeric before CCur to avoid converting obviously invalid text. It also trims the customer name and rejects a blank entry. Real forms should validate the rules that matter to their task, such as a permitted range, required format, or a choice from an approved list.

Choose types based on the value being represented. String suits names and identifiers; Long suits whole-number counts; Date suits dates; and Currency is designed for fixed-point monetary values. Do not use a numeric type for an account number or postal code just because it contains digits: leading zeroes and nonnumeric characters may be meaningful, so store identifiers as text.

Numeric interpretation can depend on regional settings. A decimal comma or currency symbol may be accepted differently on different computers. If users enter money, state the expected format in the form, use an appropriate locale-aware conversion strategy, and test with the regional settings that matter. IsNumeric alone does not enforce business rules: a negative number or an unexpectedly large value may still be numeric.

For dates, use a date-specific validation approach rather than assuming every string can be interpreted consistently. A combo box or date-selection control can reduce ambiguous typing, but available controls and behavior vary. Regardless of the control, validate the final value before writing it to a worksheet or using it in calculations.

Common mistakes and how to avoid them

Trying to pass arguments to Show: Show is not a custom parameterized method. Call a public setup procedure or set public properties before showing the form.
Reading a control after unloading: Unloading destroys the form instance. Save its result values first, or expose properties and read them while the instance is still available.
Using Initialize for caller-specific data: Initialize does not accept arguments. Use it for general defaults, then call Configure with values from the caller.
Ignoring Cancel: An empty text box or the old value still present in a control does not tell you whether the user approved it. Track cancellation explicitly and check that state.
Relying on the default instance: Default instances can conceal object lifetime and may preserve unexpected state. A declared form variable is easier to reason about and clean up.
Converting without validation: A conversion such as CCur can fail for invalid text. Check the input first and show a useful message rather than letting a runtime error interrupt the macro.

Another useful distinction is between hiding and unloading. Me.Hide closes the form visually but leaves its object and values accessible. Unload removes the form instance. The example hides on OK, Cancel, or the title-bar X so the caller can inspect the result state, then unloads after it has read the properties.

Alternatives for simpler input tasks

A UserForm is appropriate when you need several related fields, validation, instructions, or a controlled workflow. For a single short response, VBA’s built-in InputBox function may be simpler, though it offers fewer layout and validation options. Excel’s worksheet cells can also serve as an input interface when users need to see data alongside the rest of the workbook; data validation can constrain entries without a custom VBA dialog.

Another option is to expose separate public properties rather than a single Configure method. That can suit a form with many optional values, but callers must set each required property correctly. A single setup method makes the required inputs visible in one call and gives you a central place to initialize controls. For a small form, prefer the pattern that makes required values and return behavior easiest to understand.

For reusable code, keep the form responsible for collecting and validating input, and let the calling procedure decide what to do with accepted values. This separation means the same form can support different actions without embedding worksheet-writing logic in every button event. It also makes it easier to test validation and change the workbook workflow later.

Frequently Asked Questions

Can I pass parameters directly to UserForm.Show?
No. The standard Show method does not take arbitrary values for your form. Create an instance, call a public setup procedure or set properties, and then call Show.

Can UserForm_Initialize accept arguments?
No. It is an event procedure with a fixed event signature, not a custom method you call with values. Use Initialize for general setup and a separate public procedure such as Configure for values supplied by the caller.

How do I return more than one value from a UserForm?
Expose a public property for each result, or use a public result object or data structure for a more complex form. Read the results after a modal form is hidden and before you unload its instance.

How do I tell whether the user clicked Cancel or the X?
Maintain a Boolean cancellation flag. Set it to true before showing the form, set it to false only after validation succeeds on OK, and handle the title-bar close event if it should count as Cancel.

Can a UserForm run in Excel for the web?
No. Excel for the web does not run VBA macros. Use desktop Excel to run a VBA UserForm, or consider a worksheet-based input design or another web-compatible workflow.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.