Results 1 to 10 of 10

Thread: User forms and keeping array variables
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Dec 2018
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default User forms and keeping array variables

    Hello,

    I am making a user form and I'd like it to contain: a drop down list, and 4 labels + 4 text boxes.

    The idea is after a choice is made in drop down list, 4 label values are changed depending on array position, and 4 text boxes can update the values of the array.

    So i fill up my array and then
    Call DisplayFormEditLoc(allData())
    Code:
    Sub DisplayFormEditLoc(allData() As Variant)
        Dim myInitFrm As DisplayFormEditLocs
        Dim strCaption As String, i As Long, posSize As Long, posWeight As Long
        Set myInitFrm = New DisplayFormEditLocs
        posSize = 1
        posWeight = 1
        With myInitFrm
            .Caption = "Editing measurements"
            .CommandButtonConfirmation.Caption = "Edit"
            .CommandButtonCancellation.Caption = "Cancel"
            For i = 1 To UBound(allData(), 1)
            If allData(i, 2) <> Empty Then .ChoiceComboBox.AddItem allData(i, 2)
            Next i
            .ChoiceComboBox.ListIndex = 0
            .Show
        End With
    End Sub
    After filling up my combobox i display it. Then i was hoping to call a sub in my main module that has the array as a public:

    Code:
    Sub ChoiceComboBox_Change()
    Dim indexer As Integer
    indexer = ChoiceComboBox.ListIndex
    Call ChoiceComboBoxChange(indexer)
    End Sub
    In ChoiceComboBoxChange sub id just update the values of all labels and since the change event triggers at start, it would also load the initial values.

    But when i call ChoiceComboBoxChange it has none of the public variables.

    I could of course do all the changes in the sub ChoiceComboBox_Change, but i can't figure out how to get the array values to it either.

    How can this be done?

  2. #2
    Board Regular
    Join Date
    Dec 2018
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: User forms and keeping array variables

    Well apperently i redimed my array inside a sub. Now it's actaully global and is reachable by class:

    Call ChoiceComboBoxChange(indexer)

    Code:
    Sub ChoiceComboBoxChange(indexer)
    DisplayFormEditLocs.Llabel.Caption = "1"
    DisplayFormEditLocs.DLabel.Caption = "2"
    DisplayFormEditLocs.HLabel.Caption = "3"
    DisplayFormEditLocs.WLabel.Caption = "4"
    End Sub
    These are just default values for checking if the function works, but it's assigning values to something else than the labels in my form.
    In the DisplayFormEditLoc sub i assign it the value .DLabel.Caption = "D" and then once i reach the sub ChoiceComboBoxChange i can see that
    DisplayFormEditLocs.DLabel has no value assigned to it

    After moving my DisplayFormEditLoc and ChoiceComboBoxChange subs into 1 module and making DisplayFormEditLocs global i was able to make it work. But that is just bad, how could i have dealt in a better way?
    Last edited by Lavina; Sep 12th, 2019 at 06:01 AM.

  3. #3
    Board Regular Kyle123's Avatar
    Join Date
    Jan 2012
    Location
    Leeds, UK
    Posts
    2,642
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    2 Thread(s)

    Default Re: User forms and keeping array variables

    It's very hard from your post to understand exactly what code you have where and it matters are we're looking at scope issues.

    Please post all the code from the module that is initializing the form, the code form the form itself and any other associated code with the relevant module names

  4. #4
    Board Regular
    Join Date
    Dec 2018
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: User forms and keeping array variables

    Module 1:
    Code:
    Sub DisplayFormEditLoc(allData() As Variant, weightData() As Variant)
        Dim myInitFrm As DisplayFormEditLocs
        Dim strCaption As String, i As Long, posSize As Long, posWeight As Long
        Set myInitFrm = New DisplayFormEditLocs
        posSize = 1
        posWeight = 1
        With myInitFrm
            .Caption = "Editing measurements"
            .CommandButtonConfirmation.Caption = "Edit"
            .CommandButtonCancellation.Caption = "Cancel"
            For i = 1 To UBound(allData(), 1)
            If allData(i, 2) <> Empty Then .ChoiceComboBox.AddItem allData(i, 2)
            Next i
            .ChoiceComboBox.ListIndex = 0
            .Show
        End With
    End Sub
    Form:
    Code:
    Sub ChoiceComboBox_Change()
    Dim indexer As Integerindexer = ChoiceComboBox.ListIndex
    Call ChoiceComboBoxChange(indexer)
    End Sub
    Module 2:
    Code:
    Sub ChoiceComboBoxChange(indexer)
        Dim I As Long
        myInitFrm.Llabel.Caption = allData(indexer, 3)
        myInitFrm.DLabel.Caption = allData(indexer, 4)
        myInitFrm.HLabel.Caption = allData(indexer, 5)
        mainIndexer = indexer
        If allLocationData(indexer, 7) = "P" Then
            For I = 0 To UBound(weightData, 1)
                If weightLocationData(I, 4) = "P" Then
                    myInitFrm.WLabel.Caption = weightData(I, 2)
                    maxWeightLoc = weightData(I, 2)
                End If
            Next I
        End If
        If allLocationData(indexer, 7) = "S" Then
            For I = 0 To UBound(weightLocationData)
                If weightLocationData(I, 4) = "S" Then
                    myInitFrm.WLabel.Caption = weightData(I, 2)
                    maxWeightLoc = weightData(I, 2)
                End If
            Next I
        End If
    End Sub

    I want my array allData() received in Sub DisplayFormEditLoc to get thrown over to the sub ChoiceComboBox_Change(), but since it’s initialized as an event I cant throw parameters at it, or am I wrong?
    Last edited by Lavina; Sep 12th, 2019 at 08:31 AM.

  5. #5
    Board Regular Kyle123's Avatar
    Join Date
    Jan 2012
    Location
    Leeds, UK
    Posts
    2,642
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    2 Thread(s)

    Default Re: User forms and keeping array variables

    Why wouldn't the code in Module 2 be in the userform?

  6. #6
    Board Regular
    Join Date
    Dec 2018
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: User forms and keeping array variables

    I moved it out because i was not able to to pick up allData() once i reach the event:ChoiceComboBox_Change()

    I'm not sure how public variables work but i dim them before all the subs:

    Public allLocationData() As Variant
    Public weightLocationData() As Variant

    But whenever i move to a another module all the values are not visible, why?
    Last edited by Lavina; Sep 12th, 2019 at 08:42 AM.

  7. #7
    Board Regular Kyle123's Avatar
    Join Date
    Jan 2012
    Location
    Leeds, UK
    Posts
    2,642
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    2 Thread(s)

    Default Re: User forms and keeping array variables

    It's because you aren't passing allData to the userform - you just need to pass it as a property (probably along with WeightData) - something like:

    Form:
    Code:
    Private p_allData() As Variant
    Private p_weightData() As Variant
    
    Public Property Let AllData(data() As Variant)
        p_allData = data
    End Property
    
    Public Property Let WeightData(data() As Variant)
        p_weightData = data
    End Property
    
    Sub ChoiceComboBox_Change()
        ChoiceComboBoxChange Me.ChoiceComboBox.ListIndex
    End Sub
    
    Sub ChoiceComboBoxChange(indexer)
        
        Dim i As Long
        Me.Llabel.Caption = p_allData(indexer, 3)
        Me.DLabel.Caption = p_allData(indexer, 4)
        Me.HLabel.Caption = p_allData(indexer, 5)
        
        mainIndexer = indexer
        
        If allLocationData(indexer, 7) = "P" Then 'Where does this variable come from?
            For i = 0 To UBound(p_weightData, 1)
                If p_weightData(i, 4) = "P" Then
                    Me.WLabel.Caption = p_weightData(i, 2)
                    maxWeightLoc = p_weightData(i, 2)
                End If
            Next i
        End If
        If allLocationData(indexer, 7) = "S" Then  'Where does this variable come from?
            For i = 0 To UBound(weightLocationData) 'Where does this variable come from?
                If weightLocationData(i, 4) = "S" Then
                    Me.WLabel.Caption = p_weightData(i, 2)
                    maxWeightLoc = p_weightData(i, 2)
                End If
            Next i
        End If
    End Sub
    Module1:
    Code:
    Sub DisplayFormEditLoc(allData() As Variant, weightData() As Variant)
        Dim myInitFrm As DisplayFormEditLocs
        Dim strCaption As String, i As Long, posSize As Long, posWeight As Long
        Set myInitFrm = New DisplayFormEditLocs
        posSize = 1
        posWeight = 1
        With myInitFrm
            .Caption = "Editing measurements"
            .CommandButtonConfirmation.Caption = "Edit"
            .CommandButtonCancellation.Caption = "Cancel"
            .AllData = allData
            .WeightData = weightData
            For i = 1 To UBound(allData(), 1)
            If allData(i, 2) <> Empty Then .ChoiceComboBox.AddItem allData(i, 2)
            Next i
            .ChoiceComboBox.ListIndex = 0
            .Show
        End With
    End Sub
    Last edited by Kyle123; Sep 12th, 2019 at 08:43 AM.

  8. #8
    Board Regular Kyle123's Avatar
    Join Date
    Jan 2012
    Location
    Leeds, UK
    Posts
    2,642
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    2 Thread(s)

    Default Re: User forms and keeping array variables

    Don't use public variables if you can avoid it, pass variables to where they are needed

  9. #9
    Board Regular
    Join Date
    Dec 2018
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: User forms and keeping array variables

    Thank you for your input, but the let statement leaves my data empty in any way i try to write it.
    Since this is a setter, do i need an initial getter to pick the data up?


    I tried to avoid using globals as much as possible, but i've seen people state that they use a single module to track their globals.

    That does not work for me. Do they need to be included somewhere or?

    Code:
    Option Explicit
    Public functionChoice As String
    Public confirmation As Boolean
    Public updatedValuesArray() As Variant
    If i were to make a module that contained only that, none of these variables are seen in my initiation sub, am i doing something wrong?
    Last edited by Lavina; Sep 12th, 2019 at 09:44 AM.

  10. #10
    Board Regular Kyle123's Avatar
    Join Date
    Jan 2012
    Location
    Leeds, UK
    Posts
    2,642
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    2 Thread(s)

    Default Re: User forms and keeping array variables

    You're going about it the wrong way, don't use the global/public variables at all.

    Let's fix the issue, not look for a workaround and take this back to basics. You pass variables to userforms (or objects) using properties, so at its most simple, something like this:
    Userform1
    Code:
    Private p_allData() As Variant
    
    Public Property Let AllData(data As Variant)
        p_allData = data
    End Property
    
    Private Sub UserForm_Activate()
        MsgBox Join(p_allData, ", ")
    End Sub
    So when initializing the form, we can then pass a variable to the AllData Property:
    Code:
    Sub test()
    
        Dim uf As UserForm1
        Set uf = New UserForm1
        
        uf.AllData = Array(1, 2, 3)
        
        uf.Show
    
    
    End Sub
    Does that code work for you?
    Last edited by Kyle123; Sep 12th, 2019 at 10:03 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •