Passing paramarray into function requiring paramarray

Question

In excel-vba, how do I pass a paramarray into a function which expects the same paramarray? I've been looking around but can't seem to find any answers to this specific problem

My code looks like this:

Sub test()
    GetRefData "SPX Index", "PX_LAST", "PX_VOLUME", "PE_RATIO", "PX_TO_CASH_FLOW"
End Sub

Public Function GetRefData(security As String, ParamArray fields() As Variant) As String
    Debug.Print fields(0) // returns "PX_LAST"
    SendReq security, fields
End Function

Sub SendReq(security, ParamArray fields() As Variant)
    Debug.Print fields(0) // returns type mismatch
End Sub

I'm not sure what your problem is. If I remove the brackets around the call to GetRefData in Sub test, then I can put a breakpoint in SendReq and the fields parameter has 4 items with the expected string values. — DeanOC
– DeanOC, Commented Jan 10, 2017 at 6:52
Hi DeanOC, thank you for your reply. Are you saying that the code works when you run it? For example, if I use Debug.Print fields(0) when I am in SendReq, VBA returns a 'type mismatch' error — Poh Zi How
– Poh Zi How, Commented Jan 10, 2017 at 6:58
See "Passing a ParamArray variable to another procedure" here: tushar-mehta.com/publish_train/xl_vba_cases/… — Tim Williams
– Tim Williams, Commented Jan 10, 2017 at 7:15
Another Q&A with more approaches: stackoverflow.com/questions/20783170/pass-array-to-paramarray — Andre
– Andre, Commented Sep 11, 2024 at 15:09

Poh Zi How · Accepted Answer · 2017-01-10 08:00:53Z

1

After looking up the article Tim posted, the solution was that paramarrays have to be accessed one level deeper when passed as a parameter, ie:

Sub SendReq(security, ParamArray fields() As Variant)
    Debug.Print fields(0)(0)
End Sub

answered Jan 10, 2017 at 8:00

Poh Zi How

1,5884 gold badges19 silver badges42 bronze badges

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

1 Comment

Julio Nobre Over a year ago

Yep! The second fields(0) (0) parameter did the trick

Collectives™ on Stack Overflow

Passing paramarray into function requiring paramarray

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related