UserForm variable scope: transfer 2D array values from userform2 to userform1

Question

I have a problem transferring a 2D array between two userforms.

When I click on a CommandButton in userform1, it will open userform2. Then I click on CommandButton in userform2 for creating a 2D array. After this I terminate userform2 and want to transfer my 2D array into userform1.

My best try is calling a function in userform1 click event. I put this function into the userform2 module. But my userform2's function doesn't see 2D array from another subs in userform2. Private Sub userform_terminate() can see this 2D-array which was created in Private Sub CommandButton1_Click() but my function doesn't.

userform1:

Private Sub CommandButton1_Click()
    dim results()     
    results = userform2.get2dArray()
End Sub

userform2:

Private myArray()

Private Sub CommandButton1_Click()
    ReDim myArray(1 To 2, 1 To 2)
    myArray(1, 1) = "arg1"
    myArray(2, 1) = "arg2"
    myArray(1, 2) = "arg3"
    myArray(2, 2) = "arg4"
End Sub

Private Sub userform_terminate() 
   'here i can see every args in myArray
   ...
end sub

Function get2dArray() 
    'that function I called from userform1

    userform2.show vbModal
    get2dArray = myArray 'but myArray is empty
End Function

I want to transfer myArray from userform2 back to the main form userform1.

The main problem is userform2.get2dArray doesn't see the private variable myArray in userform2 module.

Making myArray global is also impossible.

Answer 1

Use a public function in a standard module (not in a userform) which takes an optional parameter (your 2D array).

The parameter is then stored in the function as a static variable. The next time the function is called, if the parameter is missing, then return the stored static variable. Here is the example:

Public Function store2DArray(Optional my2DArray As Variant) As Variant
    Static storedArray As Variant

    If IsMissing(my2DArray) Then
        store2DArray = storedArray
    Else
        storedArray = my2DArray
    End If
End Function

The usage would then be like this to store the array:

Sub Userform2Button1_Click()
     store2DArray myArray
End Sub

This is how you would retrieve the array:

Sub Userform1Button2_Click()
    myArray = store2DArray
End Sub