VBA Subroutine can't pass variable

Question

I am having problems passing a variable to from one subroutine to another. I have declared them public, yet it doesn't seem to work. It says subroutine out of range. All I need is to use varUnique (an array) and firstIndex on my second subroutine. What do I need to do to accomplish this?

Public fistIndex As Integer
Public varUnique As Variant
Sub FindUnique()

    Dim varIn As Variant
    Dim iInCol As Long
    Dim iInRow As Long
    Dim iUnique As Long
    Dim nUnique As Long
    Dim isUnique As Boolean
    Dim lastIndex As Integer

    varIn = Range("List")
    ReDim varUnique(1 To UBound(varIn, 1) * UBound(varIn, 2))

    nUnique = 0
    For iInRow = LBound(varIn, 1) To UBound(varIn, 1)
        For iInCol = LBound(varIn, 2) To UBound(varIn, 2)

            isUnique = True
            For iUnique = 1 To nUnique
                If varIn(iInRow, iInCol) = varUnique(iUnique) Then
                    isUnique = False
                    Exit For
                End If
            Next iUnique

            If isUnique = True Then
                nUnique = nUnique + 1
                varUnique(nUnique) = varIn(iInRow, iInCol)
            End If

        Next iInCol
    Next iInRow
    '// varUnique now contains only the unique values.
    '// Trim off the empty elements:
    ReDim Preserve varUnique(1 To nUnique)
    firstIndex = LBound(varUnique)
    lastIndex = UBound(varUnique)


create:
    If Not varUnique(firstIndex) = "Sub-Total" Then
    Worksheets.Add.Name = varUnique(firstIndex)
    Call Ledge(varUnique, firstIndex)
    Else
    End
    End If
    If Not firstIndex = lastIndex Then
    firstIndex = firstIndex + 1
    ActiveCell.Offset(1, 0).Select
    GoTo create
    Else
    End If
End Sub
Sub Ledge(varUnique, firstIndex)
'

'

'Define Variables
Dim Account_type As String
Dim Debit As Long
Dim Credit As Long


'Select Journal and Cell B4
    Sheets("Journal").Select
    Range("B4").Select

Account_Search:
'Make that cell= account_type
    Account_type = ActiveCell.Value
'If that cell= cash then save the values adjecent
    If Account_type = varUnique(firstIndex) Then
        ActiveCell.Offset(0, 1).Select
        Debit = ActiveCell.Value
        ActiveCell.Offset(0, 1).Select
        Credit = ActiveCell.Value
'Then go back to where you began
        ActiveCell.Offset(0, -2).Select
'Select Cash and Cell A2
        Sheets(varUnique(firstIndex)).Select
        Range("A2").Select
Search:
'If both cells are empy
            If ActiveCell.Value = "" And ActiveCell.Offset(0, 1).Value = "" Then
'Then write values and indicate you have done so
               ActiveCell.Value = Debit
               ActiveCell.Offset(0, 1).Select
               ActiveCell.Value = Credit
               Else
'If they are not empty go down one cell and search again
               ActiveCell.Offset(1, 0).Select
               GoTo Search
            End If
'Once it is recorded go to Journal again and go down one more cell
                Sheets("Journal").Select
                ActiveCell.Offset(1, 0).Select
'If it wasn't cash then go down one
    Else
        ActiveCell.Offset(1, 0).Select
    End If
'Record that cell value and check to see if it is not sub-total
    Account_type = ActiveCell.Value
    If Not Account_type = "Sub-Total" Then
'Now see if it is cash
        GoTo Account_Search
    Else
    End If
End Sub

Answer 1

I don't think the error is because of passing parameters, though "Subscript out of range" could be from firstIndex pointing to a missing item in the varUnique array.

A few thoughts regarding your code:

• Generally, you should avoid Variants, even though they can be handy when it comes to arrays. Instead use typed arrays (in this case a string array, use Dim varUnique() as String instead) or collections
• You should also avoid public or global variables if possible, as mentioned in the comments to your question. In your code above they are definitely not necessary.
• Use Option Explicit, also mentioned above. You will find many errors when compiling this way, errors which can be very hard to find otherwise (see fistIndex vs firstIndex)
• Use functions to pass values back to the calling sub - I don't think it is needed in your example - you don't change firstIndex as far as I can tell

Finally, an few examples of passing parameters betweens subs and functions that I know work, but as I said, I don't think that is where you are having the problem.

Option Explicit

Sub OuterSub()
    Dim varUnique As Variant
    Dim firstIndex As Integer
    Dim returnedInt As Integer

    '***** Create array
    varUnique = Array("String#1", "String#2", "String#3", "String#4", "String#5", "String#6")

    '***** Get first index
    firstIndex = LBound(varUnique)

    '***** Pass variables to second sub
    Call InnerSub(varUnique, firstIndex)

    '***** Pass variables to and from function
    returnedInt = InnerFunction(varUnique, firstIndex)

    Debug.Print "returnedInt=" & returnedInt & ", varUnique(returnedInt)=" & varUnique(returnedInt)
End Sub

Sub InnerSub(pvIn As Variant, piIndex As Integer)
    '***** Do something with the paramterers, like
    '***** checking to see if pvIn is an array
    If IsArray(pvIn) Then
        Debug.Print pvIn(piIndex)
    Else
        Debug.Print "pvIn not an array..."
    End If
End Sub

Function InnerFunction(pvIn As Variant, piIndex As Integer) As Integer
    '***** Return Integer
    InnerFunction = piIndex + 1
End Function