VBA Wrong number arguments userform code, transfering variables?

Question

Trying to create a budgeting spreadsheet where expenditure is read in from a csv file, I get to allocate it to categories (via a user form) and for this bit, incoming funds get a separate userform where I can put a short description in a text box which then gets recorded next to the value in the output sheet.

Used this site loads to work out how to do it all - but stuck here.

macro works well, but drops out at the user form code with "Wrong number of arguments or invalid property assignment" and highlights the indescript(x) bit from the expend.Caption line.

Original sub is

Public x, xmax As Integer
Public incmng(1 To 100) As Variant
Public incdescript(1 To 100), inctot As String

' add in the incoming payments with user entered short description
    inctot = ""
    For x = 1 To xmax
        indes.Show
        inctot = incdescript(x) & "; " & inctot
        Cells(r - 2, 7) = inctot
    Next x

the user form (named indes) code is

Private Sub UserForm_Initialize()
    ' Load the expenditure details
    expend.Caption = incdescript(x) & ": " & incmng(x)
    incans.Value = "change me :-)"
End Sub

Private Sub OKButton_Click()
    ' Allow user to put in a short description
    incdescript(x) = incmng(x) & " " & incans.Text
Unload Me
End Sub
Private Sub CancelButton_Click()
    Unload Me
    End
End Sub

It's almost as if it can't transport the values for "x" between the subs, as in the former it is showing as value = 1 in watch, in the userform code it shows as unable to compile.

thanks for any advice!

Answer 1

Even if this did work, you don't want to use variables like that. They should be given the narrowest possible scope to avoid the possibility that you forget (or someone else maintaining your code forgets) that they are declared globally.

As mentioned in the comments, declarations separated by commas like this...

Public x, xmax As Integer

...are only strongly typed for the ones that have an As {Type} after them. In this case, xmax is an Integer, and x is implicitly a Variant because no type has been declared.

UserForms are classes in VBA, and as classes they can be extended with public methods and properties. The problem is that you are using the default instance of the form instead of creating new instances within your loop. When you call Unload from inside the form, you do just that, and all of the information is simply lost.

The solution is to explicitly create instances of your forms. That way, you can either configure them with custom intializers or properties, and pass information back and forth from the caller easily. The form code would look something like this:

'indes
Option Explicit

Private incdescript As String
Private incmg As String
Private userEntry

Public Sub LoadValues(descript As String, incoming As String)
    expend.Caption = descript & ": " & incoming
    incdescript = descript
    incmg = incoming
End Sub

Public Property Get UserDescription() As String
    UserDescription = incdescript = incmg & " " & incans.Text
End Property

Private Sub OKButton_Click()
    Me.Hide
End Sub

A couple things to note - there is nothing in this form that is dependent on global variables. All of the configuration is handled by passing parameters to the LoadValues procedure, and the "return value" is exposed through a custom UserDescription property.

Also, notice that the OK button does not Unload the form. That's important because you're going to use that to pass information back to the caller.

The calling code would look more like this:

Dim x As Integer, xmax As Integer
Dim incmng(1 To 100) As Variant
Dim incdescript(1 To 100) As String, inctot As String

' add in the incoming payments with user entered short description
inctot = ""
Dim paymentDialog As indes
For x = 1 To xmax
    Set paymentDialog = New indes
    paymentDialog.LoadValues incdescript(x), incmng(x)
    paymentDialog.Show vbModal
    inctot = paymentDialog.UserDescription & "; " & inctot
    Unload paymentDialog
    Cells(r - 2, 7) = inctot
Next x

You create a new instance of the form, pass the values the form needs to it before you show it. When the form calls Me.Hide, control passes back to the calling procedure, and then you ask the form for what the user input was. Then the calling procedure unloads it.

Note that this is only a rough sketch of what I'd consider "best practices" for handling a UserForm. For a much more detailed description, I'd take a look at this answer by @MathieuGuindon and read his blog post here.

Answer 2

So decided to simplify and use a "pass" variable, building the info I want to put in the user form's caption outside the user form, send it in using pass, display, capture the user's input using the same variable (could be different but no need) and then build my strings back in the core module.

' add in the incoming payments with user entered short description
    Cells(r - 2, 5) = inctot
    For m = 1 To mmax
        pass = incdescript(m) & ": " & Incmng(m)
        indes.Show
        Inctotdes = Incmng(m) & " " & pass & "; " & Inctotdes
        Cells(r - 2, 7) = Inctotdes
    Next m

Oh and I realised x was a pre-defined variant. not that changing that made a difference anyway. I never normally used it (for that reason I now recall - it being the best part of 10 years since I used VBA!) but my preferred letter for counts was already in use.

Private Sub UserForm_Initialize()
    ' Load the expenditure details
    expend.Caption = pass
    incans.Value = "change me :-)"
End Sub

Private Sub OKButton_Click()
    ' Allow user to put in a short description
    pass = incans.Text
Unload Me
End Sub
Private Sub CancelButton_Click()
    Unload Me
    End
End Sub

Solution works, but as to why passing the variable arrays didn't work I'm still stuck. I use the same strategy on other user forms of creating captions from arrays in public memory. Although the difference is the userform code doesn't manipulate them as much and just passes an answer back as I've modified this to do. I guess that's where it failed.