Pass Array Into Subroutine for Excel VBA?-CodePudding

I'm trying to pass the results of an array into a subroutine. I have an array that picks up four different Buyer codes from a list. They're labelled as BuyOne, BuyTwo, BuyThree, BuyFour. I'm trying to get the results into the next subroutine, but I'm getting a type mismatch at the subroutine call. Even when I fiddle with it I don't know how to get the results into the subroutine. Can anyone tell me what i'm doing wrong?

Code below:

    lastRow = Range("O" & Rows.Count).End(xlUp).Row
    Set rBuyerList = Range("O1:O" & lastRow)
    arrBuyer = Array("BuyOne", "BuyTwo", "BuyThree", "BuyFour")
    
    For i = 0 To UBound(arrBuyer)
        With Application
            chkFind = .IfError(.Match(Range(arrBuyer(i)), Range("O1:O50"), 0), 0)
        End With
        
        If Range(arrBuyer(i)) = vbNullString Or chkFind = False Then
            MsgBox "Invalid Buyer Code.." & arrBuyer(i)
            Range(arrBuyer(i)).Select
        End If
    Next i

Call runFinished(sFrDt, sToDt, arrBuyer())

Sheets("Main Sheet").Select

MsgBox ("done...")


End Sub

Sub runFinished(sFrDt As String, sToDt As String, arrBuyer() As Variant)
Dim SQL As String

' add a new work sheet
ActiveWorkbook.Worksheets.Add

' dispay Criteria
Cells(1, 1) = "Run Date: " & Now()
Call MergeLeft("A1:B1")

Cells(2, 1) = "Criteria:"
Cells(2, 2) = "From " & Range("reqFrDT") & " -To- " & Range("reqToDt")


' SQL
SQL = "select a.StockCode [Finished Part], a.QtyToMake, FQOH,FQOO,/*FQIT,*/FQOA,  b.Component [Base Material], CQOH,CQOO,CQIT,CQOA " & _
"from ( " & _
"    SELECT StockCode, sum(QtyToMake) QtyToMake " & _
"    from [MrpSugJobMaster] " & _
"    WHERE 1 = 1 " & _
"    AND JobStartDate >= '" & sFrDt & "' " & _
"    AND JobStartDate <= '" & sToDt & "' " & _
"    AND JobClassification = 'OUTS' " & _
"    AND ReqPlnFlag <> 'I'  AND Source <> 'E' Group BY StockCode " & _
"    ) a " & _
"LEFT JOIN BomStructure b on a.StockCode = b.ParentPart " & _
"LEFT JOIN ( " & _
"            select StockCode, sum(QtyOnHand) FQOH, Sum(QtyAllocated) FQOO, Sum(QtyInTransit) FQIT, Sum(QtyOnOrder) FQOA " & _
"            from InvWarehouse " & _
"            where Warehouse in ('01','DS','RM') " & _
"            group by StockCode " & _
") c on a.StockCode = c.StockCode " & _
"LEFT JOIN ( " & _
"            select StockCode, sum(QtyOnHand) CQOH, Sum(QtyAllocated) CQOO, Sum(QtyInTransit) CQIT, Sum(QtyOnOrder) CQOA " & _
"            from InvWarehouse " & _
"            where Warehouse in ('01','DS','RM') " & _
"            group by StockCode " & _
") d on b.Component = d.StockCode "
SQL = SQL & _
"LEFT JOIN InvMaster e on a.StockCode = e.StockCode " & _
"WHERE 1 = 1 " & _
"and e.Buyer in  ('" & BuyOne & "','" & BuyTwo & "','" & BuyThree & "','" & BuyFour & "') " & _
"ORDER BY a.StockCode "

CodePudding user response：

If you have this line in your code

arrBuyer = Array("BuyOne", "BuyTwo", "BuyThree", "BuyFour")

Proper call should be

Call runFinished(sFrDt, sToDt, arrBuyer)

And proper declaration of the function is

Sub runFinished(sFrDt As String, sToDt As String, arrBuyer As Variant) without ()

Edit (Thanks to @Rory)

Previously stated is true if arrBuyer was not declared as follows: dim arrBuyer() as variant or dim arrBuyer(). On the other hand if declaration was dim arrBuyer() 'as variant OP's code would work w/o any changes.

Final note: I still prefer not using arrBuyer() As Variant in the sub declaration.