Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jan 2016
    Posts
    22
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Variable Not Defined?

    I am having difficulty figuring out why I am receiving a 'Variable ot defined error' in the highlighted code below.

    I have declared variable 'employ_in' at the top of my userform module.
    Code:
     Dim employ_in as String
    I then have a comboxbox change event in which I set the variable and pass it to a procedure (name_change2) in a general module within my project...

    Code:
    Private Sub cupe1_name_Change()
        If mbEvents Then Exit Sub
        mbEvents = True
        
        Dim employ_in As String
        
        ws_vh.Activate
        sr = 25
        sub_shift = "CUE1"
        sub_shift_start = WorksheetFunction.Text(ws_vh.Range("U25"), "h:mm AM/PM")
        sub_shift_end = WorksheetFunction.Text(ws_vh.Range("V25"), "h:mm AM/PM")
        
        employ_in = Me.cupe1_name
        If employ_in = "Other" Then
            crew_in = "CUPE6"
        ElseIf employ_in = "Not Staffed" Then
            employ_in = ""
            crew_in = ""
            sub_shift_start = ""
            sub_shift_end = ""
        Else
            crew_in = WorksheetFunction.Index(ws_lists.Range("Y2:Y24"), WorksheetFunction.Match(employ_in, ws_lists.Range("Z2:Z24"), 0))
        End If
        
        If employ_in = "" Or WorksheetFunction.CountIf(ws_vh.Range("T25:T42"), employ_in) = 0 Then
            repl_shift = ""
            rr = 0
        Else
            rr = WorksheetFunction.VLookup(employ_in, ws_vh.Range("T25:W42"), 4, False)
            repl_shift = WorksheetFunction.Index(ws_vh.Range("R25:R42"), WorksheetFunction.Match(employ_in, ws_vh.Range("T25:T42"), 0))
        End If
        
        sr_in = WorksheetFunction.VLookup(sub_shift, ws_vh.Range("R25:W42"), 6, False)
        employ_out = WorksheetFunction.VLookup(sub_shift, ws_vh.Range("R25:W42"), 3, False)
        crew_out = WorksheetFunction.VLookup(employ_out, ws_lists.Range("X19:Y25"), 2, False)
        
        name_change2 sh:=ws_vh, form:=Me, string1:=employ_in
        
        mbEvents = False
    End Sub
    However, the in my name_change2 procedure, when 'employ_in' is encounter, the code breaks with a 'Variable not defined.' error.

    Code:
    Sub name_change2(ByVal sh As Object, ByVal form As UserForm, ByVal string1 As String)
    
    Dim ui1 As String
    
    If employ_in = "Not Staffed" Then
        ui1 = MsgBox("This will remove " & employ_out & " (" & crew_out & ") from the roster.", vbOKCancel, "STAFF DEFICIENCY")
        If ui1 = vbCancel Then
            cb_name_out = employ_out
        Else
            With uf_create_wo2
                cb_name_out = employ_in
                cb_crew_out = crew_in
                cb_start_out = sub_shift_start
                cb_end_out = sub_shift_end
                With sh
                    .Range("S" & sr) = "X"
                    .Range("T" & sr) = "Not Staffed"
                    .Range("U" & sr) = ""
                    .Range("V" & sr) = ""
                End With
            End With
        End If
                    
    End If
    End Sub
    Is anyone able to determine why I may be receiving this error? I am new to passing variables, so I don't doubt I've misunderstood something. This problem has also been cross posted here in order to reach a broader audience.

    Thank you for your time ...

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    JennEx,

    Declare the variable in a standard module:

    Public employ_in as String

    It will stay in scope as global as long as the workbook is open.

    HTH,
    Maud

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    JennEx (2016-02-28)

  4. #3
    New Lounger
    Join Date
    Jan 2016
    Posts
    22
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Hi Maud, thank you very much for your suggestion.

    I had originally used this method you suggested and admittedly it did work. However, I am trying to learn how to avoid using public declarations. Luckily I have this as a fallback I suppose, but I do hope I can learn from this error how to properly pass variables using arguments. (I may even learn that it's not possible?)

    Jenn

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Jenn,

    You are passing "employ_in" from the Change Event in the form into the variable "String1" (see your Sub declaration) in name_change2. Therefor, you need to reference String1 NOT employ_in in name_change2. Of course, you could also change the Sub declaration to employ_in.

    HTH
    Last edited by RetiredGeek; 2016-02-28 at 08:22.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #5
    New Lounger
    Join Date
    Jan 2016
    Posts
    22
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thank you!!

    So, I changed my reference to 'string1' in procedure name_change2 and things worked until I got to the next variable. I think once I can wrap my head around this concept, the others can be changed accordingly.

    In my userform module
    Code:
     Dim employ_in as string
    In my control change event...
    Code:
    name_change2 sh:=ws_vh, form:=Me, string1:=employ_in
    And in my procedure ...
    Code:
    Sub name_change2(ByVal sh As Object, ByVal form As UserForm, ByVal string1 As String)
    Of course, you could also change the Sub declaration to employ_in.
    Like this? It doesn't look right ...
    Code:
    name_change2 sh:=ws_vh, form:=Me, employ_in:=employ_in
    Code:
    Sub name_change2(ByVal sh As Object, ByVal form As UserForm, ByVal employ_in As String)

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Jenn,

    I don't see where you use this variable outside of the two listed routines therefor, you should just declare it inside the Change Event code.
    In my userform module
    Code:
     Dim employ_in as string
    Yes this would give you problem I'm sure!
    Like this? It doesn't look right ...
    Code:
    name_change2 sh:=ws_vh, form:=Me, employ_in:=employ_in
    I usually don't use the qualifiers but do it positionally which solves the problem.
    In my control change event...
    Code:
    name_change2 ws_vh, Me, employ_in
    This is fine in line with the above
    And in my procedure ...
    Code:
    Sub name_change2(ByVal sh As Object, ByVal form As UserForm, ByVal employ_in As String)
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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