New to VBA

[gregyoung]

I am new to VBA and I am having a hard time understanding how variables are passed from one procedure to the next. The following code works up until I click an option in ListBox1, when that happens I get an error stating "Sub or Function not defined"... the error is refering to the array named Jobarray. How do I get the procedure "UserForm_Initialize" to pass the variable to "ListBox1_Click"?

Private Sub ListBox1_Click()

listnum = ListBox1.ListIndex
fn = Jobarray(listnum, 2)

End Sub

Private Sub UserForm_Initialize()

Dim MyFile, MyPath, MyName, intI As Integer
Dim Jobarray(50, 50) As String
MyPath = "F:\"
MyName = Dir(MyPath, vbDirectory)
intI = 0
Do While MyName <> ""
    If MyName <> "." And MyName <> ".." Then
        If &#40;GetAttr&#40;MyPath & MyName&#41; And vbDirectory&#41; = vbDirectory Then
            If Left&#40;MyName, 5&#41; Like "#####" Then
                Jobarray&#40;intI, 0&#41; = &#40;MyPath & MyName&#41;
                intI = intI + 1
            End If
        End If
    End If
    MyName = Dir
Loop

intB = 0
intC = 0
Dim Check
JobPath = Dir&#40;MyName, vbNormal&#41;
Do While JobPath <> ""
        JobPath = Jobarray&#40;intB, 0&#41;
        If JobPath <> "" Then
            FabName = Dir&#40;JobPath & "\*.txt", vbNormal&#41;
            Jobarray&#40;intC, 1&#41; = FabName
            intB = intB + 1
            intC = intC + 1
        End If
Loop

ListBox1.ColumnCount = 1

TmpName = "temp"
intC = 0
intD = 0
Dim TempArray&#40;50&#41; As String
Do While TmpName <> ""
    TmpName = Jobarray&#40;intC, 1&#41;
    TmpLen = Len&#40;TmpName&#41;
    NewLen = TmpLen - 4
    If TmpName <> "" Then
        TmpFab = Left&#40;TmpName, NewLen&#41;
    End If
    If TmpName <> "" Then
        Check = Filter&#40;TempArray, TmpFab&#41;
        Check1 = UBound&#40;Check&#41;
        If Check1 = -1 Then
            TempArray&#40;intD&#41; = TmpFab
            intD = intD + 1
        End If
    End If
    intC = intC + 1
Loop

intE = 0
Do While TempArray&#40;intE&#41; <> ""
    ListBox1.AddItem UCase&#40;TempArray&#40;intE&#41;&#41;
    intE = intE + 1
Loop

ListBox3.ColumnCount = 1

ListBox1.TextColumn = 1
ListBox3.TextColumn = 1
End Sub

Thanks in advance.

Greg

[Joro--]

Hi Greg,

Refering to Jobarray causes an error because the ListBox1_Click() procedure is unaware of the local variables in other procedures. To pass a variable vrom one procedure to another you have several options.

First way is to declare Jobarray as public variable at module level. You have to do this in one of standard modules in your application (not UserForm ones). It will look like

Public Jobarray(50, 50) As String

Now Jobarray will be available everywhere in your project

Second way- it is a bit more complicated. You may have the Jobarray as a local variable in a certain procedure but to pass it to another procedure (Sub or Function) you must provide arguments between parenteces for a variable like Jobarray in the other procedure. You should do this in standard module procedures and then just assign these procedures to UserForm_Initialize() and ListBox1_Click() procedures in the userform module.

Second way is more difficult for begginers but many addvanced programers avoid using Public variables unless there is no other way.

And in the end a bit of advice from me - avoid putting so much code in the userform modules. Better create other procedures and then assign them to the ActiveX control you need. Wish you luck!

[gregyoung]

Thank you very much! This worked out just fine for me. It seems like creating a module just for declairing global variables is the way to go.

I knew this code was ugly, I just didnt know how it was Thank you for your advice on creating procedures and just calling them from the UserForm modules. I will try to fix that on this project. Dont want to get off to a bad start with VBA.

Greg