VBA 子程序无法传递变量

VBA Subroutine can't pass variable

提问by Uriel Katz

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?

我在将变量从一个子例程传递到另一个子例程时遇到问题。我已经宣布它们是公开的，但它似乎不起作用。它说子程序超出范围。我所需要的只是在我的第二个子例程中使用 varUnique（一个数组）和 firstIndex。我需要做什么来实现这一目标？

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

回答by Olle Sj?gren

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

我不认为错误是因为传递了参数，尽管“下标超出范围”可能是因为firstIndex指向varUnique数组中缺少的项目。

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 Stringinstead) 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 fistIndexvs 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 firstIndexas far as I can tell

• 通常，您应该避免变体，即使它们在涉及数组时很方便。而是使用类型化数组（在本例中为字符串数组，Dim varUnique() as String改为使用）或集合
• 如果可能，您还应该避免使用公共或全局变量，如对您的问题的评论中所述。在上面的代码中，它们绝对不是必需的。
• 使用Option Explicit，上面也提到过。以这种方式编译时会发现很多错误，否则很难找到错误（参见fistIndexvs firstIndex）
• 使用函数将值传递回调用子 - 我认为在您的示例中不需要它 -firstIndex据我所知，您不会改变

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.

最后，我知道一些在 subs 和函数之间传递参数的示例，但正如我所说，我认为这不是您遇到问题的地方。

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