Pass Array Into Subroutine for Excel VBA?

Question

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 "

`Call runFinished(sFrDt, sToDt, arrBuyer())` should be `Call runFinished(sFrDt, sToDt, arrBuyer)` — milo5m, Oct 20 '22 at 14:24
`Call` is deprecated by the way. Well maybe there's an argument whether it's deprecated or not, but it's definitely redundant. — BigBen, Oct 20 '22 at 14:26
Do you have `Dim arrBuyer()` somewhere? It needs to be declared as an array — Rory, Oct 20 '22 at 14:34
@milo5m when I put just arrBuyer, it STILL says "type mismatch: array or user-defined type expected" — majinvegito123, Oct 20 '22 at 14:34
in the runFinished sub, `arrBuyer() As Variant` should be just `arrBuyer As Variant`. If `arrBuyer = Array(...)...` — milo5m, Oct 20 '22 at 14:38
@Rory OK, I fixed that, but It's not feeding the information into the subroutine how I want it to. I switched e.Buyer in to say arrBuyer(0), 1, 2 and 3 and it outputs BuyOne, BuyTwo, BuyThree, BuyFour, but I need it to output what is INSIDE BuyOne (These are manually typed in and can be CAW, CSW etc). How do I make that change since just putting BuyOne, BuyTwo, BuyThree, BuyFour doesn't work and says no variable defined — majinvegito123, Oct 20 '22 at 14:44
@BigBen: *"Call is deprecated by the way."* - can you give a source for that? For me, that's just a different syntax and I often use it - I hate it that calls to a function uses parenthesis and call to a Sub not. Especially when a function is used as sub because I don't bother about the result — FunThomas, Oct 20 '22 at 14:44
@FunThomas https://stackoverflow.com/questions/56504639/call-keyword-deprecated-or-not I did caveat my deprecated claim with "redundant" haha. — BigBen, Oct 20 '22 at 14:44
@While I am a Big fan of Mathieu, I cannot really follow his argument - and again, no link to any official document. I was just curious. — FunThomas, Oct 20 '22 at 14:50
@FunThomas calling a function only uses parens if you are doing something with the return value (or using `Call`); otherwise it's the same as a sub. — Rory, Oct 20 '22 at 15:00
@majinvegito123 that's a whole different question and I don't think can be answered give what you've posted. — Rory, Oct 20 '22 at 15:01
Yes, I know - but exaclty that doesn't make no sense to me. Why should I remove or add parenthesis - I just replace the `call` with the assignment. But I don't want to open a discussion, I was just curious if this *deprecated* is an official MS statement. — FunThomas, Oct 20 '22 at 15:03
@Rory I can make a new question for you in regards to that. What additional information would you need? — majinvegito123, Oct 20 '22 at 15:08
Your current question provides no information as to what BuyOne etc might be other than simple text. — Rory, Oct 20 '22 at 15:09
@Rory BuyOne is actually just a range where a user enters simple text in, so that's not inaccurate. — majinvegito123, Oct 20 '22 at 15:12
If it's the name of a range (not the name of a variable) then you can use `Range(arrBuyer(0))` for example. — Rory, Oct 20 '22 at 15:14
@Rory that actually worked. You are a legend and a god among men. — majinvegito123, Oct 20 '22 at 15:16
@Rory I tried to post a separate question, but what modifications do I need to make to the code to allow the BuyOne etc entries to be blank? Meaning EVERY entry is just left blank so the code just runs every possible entry? Currently the For loop I have checks the entries with a list in column O, but if no entry is put into BuyOne etc I want it to just run them all. — majinvegito123, Oct 20 '22 at 15:25
No context so impossible to answer. And very definitely a completely different question... ;) — Rory, Oct 20 '22 at 15:27

milo5m · Accepted Answer · 2022-10-20T16:01:10.617

0

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.

edited Oct 20 '22 at 16:01

answered Oct 20 '22 at 14:49

milo5m

619
1
3
8

Thank you - this worked perfectly. I think I'm misunderstanding how these things work though because in the runFinished subroutine, it's putting in "BuyOne" etc in the e.Buyer section when I put in arrBuyer(0) where I really need it to output what BuyOne CONTAINS (which is actually a value like CSW) How do I go about doing that? – majinvegito123 Oct 20 '22 at 14:54
@majinvegito123damn now I can't delete my answer :P – milo5m Oct 20 '22 at 15:00
Also not strictly accurate. It all depends on how the variable is declared. – Rory Oct 20 '22 at 15:02
https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/array-function BTW, @Rory can you give an example? – milo5m Oct 20 '22 at 15:09
`Array` actually returns an array of type variant, not just a Variant (you can check it with `TypeName`). Try the code with `Dim arrBuyer()`. – Rory Oct 20 '22 at 15:13
My point is that you can't have `arrBuyer() As Variant` in the sub declaration and call that sub with `array("..",....)` argument – milo5m Oct 20 '22 at 15:14
You can't pass the Array arg *directly* in the call, no (which is slightly odd, but anyway). But that's not what the OP was doing, or what your posted code does. I'm just pointing out that simply changing the variable declaration to an array in the caller makes everything else work with no further changes. – Rory Oct 20 '22 at 15:26
@Rory Actually you are completely correct, when I think about it (my tunnel vision..). If OP had `Dim arrBuyer()` or `Dim arrBuyer() as variant`, at the top (we had no info about that tho), It would've worked w/o any problem. – milo5m Oct 20 '22 at 15:41

Pass Array Into Subroutine for Excel VBA?

1 Answers1