CODEWITHSUNDEEP
arraysvbaexceluserform
Fred A. L.
Fred A. L.

Reputation: 15

Reference an array declared in a different userform

I'm not an experienced VBA programmer but I've been trying to create an Excel Spreadsheet that is able to manage a basketball team.

In it I've got a primary userform where I have declared an array, 'selectedPlayers'. This primary userform has a for loop that starts up the secondary userform 'i' times.

I have not been able to access the primary userform's 'i' and 'selectedPlayers' from the secondary one. I've been able to find a workaround the 'i' by creating a non-visible textbox in the first userform, that I'm able to reference from the second one.

I've tried declaring both of them as public, but yet I'm not able to call upon it from the second userform.

part of the code for the first userform:

i = 0
Do While Not i = Int(txtNumberPlayers)
    frmGameDataSecondary.Show
    i = i + 1
Loop

second userform:

Private Sub cmdDone_Click()
    frmGameData.selectedPlayers(frmGameData.i) = lbxPlayer.Value
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    With Me.lbxPlayer

    For Each LR In LO.ListRows
        exitSequence = False

        For k = 1 To Int(frmGameData.txtNumberPlayers)
            If frmGameData.selectedPlayers(k) = blablabla.Value Then
                exitSequence = True
            End If
        Next k

        If !exitSequence Then
            .AddItem blablabla.Value
        End If

    Next LR

    End With
End Sub

Upvotes: 1

Views: 1429

Answers (3)

gembird
gembird

Reputation: 14053

You can declare properties inside of parent form which will manipulate the array from outside. The child form needs to have a reference to parent so it can call this properties. HTH

Parent form

Option Explicit

' I have not been able to access the primary userform's
' 'i' and 'selectedPlayers' from the secondary one
Private selectedPlayers As Variant

Public Function GetMyArrayValue(index) As Variant
    GetMyArrayValue = selectedPlayers(index)
End Function

Public Sub SetMyArrayValue(index, newValue)
    selectedPlayers(index) = newValue
End Sub

Private Sub UserForm_Click()
    Dim i

    i = 0
    Do While Not i = Int(txtNumberPlayers)
        With New secondaryUserForm
            Set .ParentForm = Me
            .SetIndex = i
            .Show
        End With
        i = i + 1
    Loop
End Sub

Private Sub UserForm_Initialize()
    selectedPlayers = Array("A", "B", "C")
End Sub

Child form

Option Explicit

Private m_parent As primaryUserForm
Private m_index As Integer

Public Property Let SetIndex(ByVal vNewValue As Integer)
    m_index = vNewValue
End Property

Public Property Set ParentForm(ByVal vNewValue As UserForm)
    Set m_parent = vNewValue
End Property

Private Sub cmdDone_Click()
    ' frmGameData.selectedPlayers(frmGameData.i) = lbxPlayer.Value
    m_parent.SetMyArrayValue m_index, "lbxPlayer.Value"
    Unload Me
End Sub

Upvotes: 0

Comintern
Comintern

Reputation: 22205

Forms in VBA are Objects, and can be treated like any other Class module. This means that you can add properties to them. If you need to pass information back from a form, all you need to do is grab a reference to it, then Hide it instead of Unload it. Treat it like a dialog and let the calling code handle it's create and destruction (I'm assuming from your code that it is modal).

Something like this:

In the first UserForm:

For i = 0 To 1
    Dim second As frmGameDataSecondary
    Set second = New frmGameDataSecondary
    second.Show
    'Execution suspends until the second form is dismissed.
    selectedPlayers(i) = second.Player
    Unload second
Next i

In the second UserForm:

Private mPlayer As String

'This is where your returned information goes.
Public Property Get Player() As String
    Player = mPlayer
End Property

Private Sub cmdDone_Click()
    mPlayer = lbxPlayer.Value
    'Control passes back to the caller, but the object still exists.
    Me.Hide
End Sub

Upvotes: 0

Doug Coats
Doug Coats

Reputation: 7117

The main problem is that array contents are cleared after the sub is finished.

I was also messing around with this idea and there is a really good thread I started with tons of great information from various awesome people

Calling an Array Upon User Form Terminate/Close VBA

Upvotes: 1

Related Questions