VBA: Problem with passing arguments to userform

Question

I have searched and tried for 6 hours now but I can't solve the problem.

My idea: I want to show a userform in Excel-VBA with some radiobuttons, one listelement and one textfield. I want to pass some arguments to the userform to work with it because I don't want to calculate inside the userform-code (which works) I don't want to declare global variables as well.

I tried the following which worked:

calculate directly in the userform
global variables

This hasn't worked:

declared userform as variable and worked with the variable
every possible combination of [Public|Private|Friend] Property Let (ByRef|ByVal)
Property Set doesn't work in any case

The code from my main (module)

Option Explicit

Sub main()
    With New usr_mainInput
        .counter = 3
        .Show
    End With
End Sub

My code in the userform

Option Explicit

Private miCounter As Integer

Property Get counter() As Integer
    counter = miCounter
End Property
Property Let counter(c As Integer)
    Set miCounter = c
End Property

Private Sub userform_initialize()
    Dim i As Integer
    For i = 1 To counter           'miCounter don't work as well
        Debug.Print i
    Next i
End Sub

Private Sub btn_ok_Click()
    Me.Hide
End Sub

Object variable or With block variable not set

If it comes to the Property Let counter() it drops a compile error:

Object required (Error 424)

I think i get it... the moment _initialize is to early, the .counter isn't set at this time and in the Property Let counter() I don't have to Set miCounter = c, that's the cause of the dropped error. With _activate it works. I'll try tomorow in the office with my live-program, this was just a short reconstruction. — Patrick

Mathieu Guindon Mathieu Guindon · Accepted Answer · 2019-09-18T21:01:05

Property Let counter(c As Integer)
    Set miCounter = c
End Property

An object is required, only because of the Set keyword. This isn't a reference assignment, it's a value assignment.

View it like this:

Property Let counter(c As Integer)
    Let miCounter = c
End Property

Just don't actually type up the Let keyword (it would work though), it's obsolete :)

Also note that the implicit modifier for Property Let/Set procedure argument, is always Byval - this is different than anything else in VBA, where thr implicit modifier is ByRef; consider making the ByVal modifier explicit.

Private Sub userform_initialize()
    Dim i As Integer
    For i = 1 To counter           'miCounter don't work as well
        Debug.Print i
    Next i
End Sub

That loop will never iterate anything, because the Initialize handler runs here:

With New usr_mainInput

I mean, it runs when the New usr_mainInput instruction returns, but before the object reference is given to the With block (note that this is true for any class, not just forms) - that's well before the .counter = 3 assignment! Rule of thumb, you want to initialize instance state in that handler, not consume it.

Consider using the Activate handler instead. That one will run immediately after the .Show call.

VBA: Problem with passing arguments to userform

1 Answers