Trouble Passing Array Values

Question

I have a sub that fills an array with a list of user defined values from a fixed range in my spreadsheet. I want to call this sub from another, and utilize the values in the array to drive a For Each loop.

When I call the sub getInvoiceList I can see that it does fill the array invoiceList with the user's values. But they don't pass back into the sub I call from.

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

When I call the sub 'getInvoiceList' I can see that it does fill the array invoiceList with the user's values. But they don't pass back into the sub I call from.

Run-time error '13': Type Mismatch Debug takes me to the For i = 0 to UBound line. Locals window shows invoiceList but has value = empty both on Stop line and after clicking debug.

I see that you reformatted the references to my code; but how did you do that, so I know for next time? — SFC9104
– SFC9104, Commented Mar 25, 2019 at 19:51
Subs don't return anything. You could return from a Function or alternately you could move Dim invoiceList() As Variant outside of the Sub so it's accessible by everything in the module. — Mark Balhoff
– Mark Balhoff, Commented Mar 25, 2019 at 19:53
If you click the Edited 5 mins ago link, you can see what characters were added for formatting. — Mark Balhoff
– Mark Balhoff, Commented Mar 25, 2019 at 19:55
Select code and press Ctrl + K for Block. Your array is a local variable. Either make it a function that returns an array, or declare the array in main sub and pass it to the sub to fill in. — Noodles
– Noodles, Commented Mar 25, 2019 at 20:13
@Mark Balhoff, @ Noodles: Thank you. Moving the array declaration out side of the sub does work to pass the array values. Doing so then yields an Out of Range error at If Sheets("Calculator").Cells(j, 10).Value = InvoiceList(i) Then. — SFC9104
– SFC9104, Commented Mar 25, 2019 at 20:54

Mathieu Guindon · Accepted Answer · 2019-03-25 21:22:06Z

1

You can pass variables in/out of sub's.

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

edited Mar 25, 2019 at 21:22

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

answered Mar 25, 2019 at 20:26

Wes Howell

10610 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SFC9104 Over a year ago

I've read in my reference guide that this solution should work. But it does not: Runtime Error 13 Type Mismatch. Debugs to For i = 0.

Mathieu Guindon Over a year ago

@SFC9104 the type mismatch is due to the superfluous parentheses in the getInvoiceList (InvoiceList) call, which are attempting to pass the variant array by value, which isn't legal. Just drop the parens and it should work fine... after transposing the 2D variant array you're getting from Sheet2.Range("C4:C14") - i.e. do InvoiceList = Application.Transpose(Sheet2.Range("C4:C14")) and you'll get a one-dimensional variant array, which UBound will happily work with (a 2D array would require a dimension index for UBound to work).

SFC9104 Over a year ago

Ah, I totally missed the Application.Transpose(Sheet2.Range("C4:C14")) update. HUZZAH! I had to update i= 0 to i =1 to resolve the out of range error; but I got colors now. Thank you.

W-hit · Accepted Answer · 2019-03-25 22:28:43Z

0

It would probably be easier to use a collection. Try my code below.

Public Sub columnLoop()
    Dim i As Long, j As Long
    Dim coll As New Collection

    'add items to collection
    For Each cell In Sheets(2).Range("C4:C14")
        coll.Add cell
    Next
    Stop 'to view array values

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

    Dim itm As Variant

    For Each itm In coll
        '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 = itm Then 'checks values against items in collection
                Sheets("Calculator").Cells(j, 10).Font.Color = vbRed
            End If
        Next j
    Next itm

End Sub

edited Mar 25, 2019 at 22:28

answered Mar 25, 2019 at 20:32

W-hit

3615 silver badges15 bronze badges

7 Comments

SFC9104 Over a year ago

Testing... Meanwhile, what are the benefits to using Collection instead of Arrays? If I expand this to search many more entries would it be lower/faster?

W-hit Over a year ago

Collections are more dynamic. See the differences here: excelmacromastery.com/excel-vba-collections

Mathieu Guindon Over a year ago

As New makes coll an auto-instantiated object variable, which can have unintended consequences. Iterating object collections by index is also the single least efficient way to use collections, which are optimized for For Each iteration.

SFC9104 Over a year ago

@W-hit This sort of worked. It doesn't throw any errors in the compiler; but it also doesn't seem to load values to the collection or evaluate true in any case. And yes, there are values to load from that range.

W-hit Over a year ago

Is Sheet2 a public declaration, or is it actually the 2nd sheet? You may need to change the for each line to Sheet2.Range... if Sheet2 is already decalred

|

Collectives™ on Stack Overflow

Trouble Passing Array Values

2 Answers 2

3 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related