0

My Task: I have a userform that has various Checkboxes on it. The user should check/uncheck those boxes and press the "Done" button once finished. The pressing of "Done" should initiate an array and fill it with string values according to the code. So far, so good.

My Problem: However, because I need the array in another module, it has to be public. I have declared it public like seen below in the my code. However, it errors. What am I doing wrong?

Thanks!

Option Base 1
Public dimArray(5, 4) As String

Private Sub cmdBtn_Done_Click()

Unload ProductDimension

'1st Dimension
If chk_AbilityOfInteraction = True Then

    dimArray(1, 1) = "Functionality"
    dimArray(1, 2) = "Ability of interaction"

End If

If chk_Performance = True Then

    dimArray(1, 1) = "Functionality"
    dimArray(1, 3) = "Performance"

End If

....
End Sub
Ardnic
  • 51
  • 7
  • 2
    Give [this answer](https://stackoverflow.com/a/47291028/4088852) a read, then read the linked blog post. A `UserForm` is a class (not a module), so you should treat it like one. – Comintern Jan 22 '19 at 03:26
  • Thank you very much, that looks very promising! I will read through it now. Sorry for a potentially trivial thread, I am just starting out.. – Ardnic Jan 22 '19 at 03:31

1 Answers1

1

Since you want the result of the user form in another module you might use the code below in a standard code module. It's a function which returns the array you want. So, in the procedure were you need the array you should have

Dim Arr() As String
Arr = GetArray

and here is the function GetArray:-

Function GetArray() As String()

    ' "UserForm1" must be the name you gave to the UserForm.
    ' It's "UserForm1" by default but you did well if you changed the name.
    Dim MyForm As UserForm1
    Dim Arr(1 To 3) As String

    Set MyForm = New UserForm1              ' this is the actual form's name, too
    With MyForm
        ' although the form isn't visible, you can
        ' set or modify any of its controls:-
        .chk_AbilityOfInteraction.Value = False
        .Show                               ' now MyForm takes over control
                                            ' until "Hide" is encountered in
                                            ' the form's own code.
                                            ' Your "Done" button should trigger
                                            ' this action.
        ' upon "Me.Hide" the code resumes here
        ' The form is only hidden. All it's controls are still in memory.
        Arr(1) = "Functionality"
        If .chk_AbilityOfInteraction.Value = True Then
            Arr(2) = "Ability of Interaction"
        Else
            Arr(3) = "Performance"
        End If
    End With

    Unload MyForm                           ' now the form is killed
                                            ' if not killed you might "Show" it again
                                            ' with all previous values still in it.
    Set MyForm = Nothing
    GetArray = Arr                          ' this sets the function's return value
End Function
Variatus
  • 14,293
  • 2
  • 14
  • 30
  • Thank you very much for your detailed answer! A key element that you mention in your code is "Hide", which I did not know before. Even though your solution is perfect, this actually helped my to find a different, easy solution to my problem: I included the code that populates the array into a normal module instead of the userform. Before with "unload"ing the userform, this did not work, as the Data from the Checkboxes was deleted in that moment. With ".Hide" the array is populated just fine! Let me know if I have overseen a major flaw in my solution. Cheers! – Ardnic Jan 22 '19 at 20:57
  • 1
    No. The solution I offered is an overkill, intentionally. The idea was to provide all the parts for you to understand the system and then pick out what works for you. It seems that you did precisely that. :-) – Variatus Jan 24 '19 at 01:44