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)

Answer 1

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.