时间戳

首页 > 解决方案 > 传递数组值的麻烦

arrays - 传递数组值的麻烦

问题描述

我有一个 sub 用我的电子表格中固定范围内的用户定义值列表填充数组。我想从另一个调用这个 sub，并利用数组中的值来驱动 For Each 循环。

当我调用 sub 时，getInvoiceList我可以看到它确实invoiceList用用户的值填充了数组。但他们不会传回我打电话的潜艇。

Public Sub columnLoop()

    Dim i As Long, j As Long
    getInvoiceList
    Stop 'to view array values

    Sheets("Calculator").Columns(10).Font.Color = vbBlack

    For i = 0 To UBound(invoiceList)

    'Loops through column for specific value(as declared)
    'Recolors text when current cell value = specific value
        For j = 3 To Range("NumFilledRows").Value
            If Sheets("Calculator").Cells(j, 10).Value = invoiceList(i) Then
            Sheets("Calculator").Cells(j, 10).Font.Color = vbRed
        End If
    Next j
Next i

End Sub

'Fill array from fixed range
Public Sub getInvoiceList()
    Dim invoiceList() As Variant
    invoiceList = Sheet2.Range("C4:C14")

    Stop 'allows review of array.
End Sub

当我调用子“getInvoiceList”时，我可以看到它确实用用户的值填充了数组 invoiceList。但他们不会传回我打电话的潜艇。

Run-time error '13': Type Mismatch 调试带我去For i = 0 to UBound生产线。Locals 窗口显示invoiceList，但value = empty在停止行和单击调试后都有。

标签： arraysexcelvba

解决方案

您可以将变量传入/传出子。

Public Sub columnLoop()

    Dim i As Long, j As Long, InvoiceList As Variant
    getInvoiceList  InvoiceList
    Stop 'to view array values

    Sheets("Calculator").Columns(10).Font.Color = vbBlack

    For i = 0 To UBound(InvoiceList)

    'Loops through column for specific value(as declared)
    'Recolors text when current cell value = specific value
        For j = 3 To Range("NumFilledRows").Value
            If Sheets("Calculator").Cells(j, 10).Value = InvoiceList(i) Then
            Sheets("Calculator").Cells(j, 10).Font.Color = vbRed
        End If
    Next j
Next i

End Sub

'Fill array from fixed range
Public Sub getInvoiceList(ByRef InvoiceList As Variant)

    InvoiceList = Application.Transpose(Sheet2.Range("C4:C14").Value)

    Stop 'allows review of array.
End Sub

推荐阅读