Can a UDF pass a ParamArray to a subroutine for processing?

[JenniferMurphy]

I am working on a UDF with several keyword parameters that I am passing from Excel as a ParamArray. To keep the main UDF simpler, I would like to pass the entire ParamArray to a subroutine for processing. I can't get it to work.

Here's what I've tried:

' This is the main UDF as called from Excel.
Public Function Main(P1 As Range, P1 As Range, ParamArray PArgs()) As Variant

' Define and initialize the ParamArray variables
Dim PA1 As Single:  PA1 = 100
Dim PA2 As Single:  PA2 = 0
Dim PA3 As String:  PA3 = "Off"

 . . .

' Call the subroutine to process the ParamArray string (PArgs).
' Pass the variables that will be set by the ParamArray string ByRef
' so the subroutine can change them if necessary.
If PACheck(PA1, PA2, PA3, PArgs()) Then
  Main = CVErr(xlErrValue): Exit Function   'If errors, exit
End If

 . . .

End Function 


' 
Public Function PACheck(ByRef PA1, ByRef PA2, ByRef PA3, _
                                 ByRef PArgs()) As Boolean

' Run through the ParamArray parameters, check for errors, and set the values

 . . .

End Function

When I try to run this, I get a Compile Error on the call to the subroutine saying that it is "Invalid parameter use".

I've tried it with and without the parentheses.

Can this be done? How?

 

[Kyle123]

You don’t need to deal with the array of arrays if you remove the paramarray from Pasub and declare PArgs as a variant and not an array

 

[JenniferMurphy]

You're welcome. I think your approach of copying the array is the better method though.

Why is that?

 

[JenniferMurphy]

You don’t need to deal with the array of arrays if you remove the paramarray from Pasub and declare PArgs as a variant and not an array

I don't understand. I want to process the ParamArray parameters in the Sub exactly as I would in the Main. Are you suggesting a method that is better than what Shg provided? That method works perfectly for my needs.

 

[shg]

Why is that?

Because otherwise you need to keep adding a zero with each increase in depth:

Public Function PAMain(P1, ParamArray PArgs())
  MsgBox "Main: PArgs = {" & Join(PArgs, ", ") & "}"
  Call PASub1(P1, PArgs)
End Function

Public Sub PASub1(P1, ParamArray PArgs())
  MsgBox "Sub: Pargs(0) = {" & Join(PArgs(0), ", ") & "}"
  Call PASub2(P1, PArgs())
End Sub

Public Sub PASub2(P1, ParamArray PArgs())
  MsgBox "Sub: Pargs(0)(0) = {" & Join(PArgs(0)(0), ", ") & "}"
End Sub

EDIT: I'd be interested to see Kyle's suggestion as well.

 

[JenniferMurphy]

Because otherwise you need to keep adding a zero with each increase in depth:

Public Function PAMain(P1, ParamArray PArgs())
  MsgBox "Main: PArgs = {" & Join(PArgs, ", ") & "}"
  Call PASub1(P1, PArgs)
End Function

Public Sub PASub1(P1, ParamArray PArgs())
  MsgBox "Sub: Pargs(0) = {" & Join(PArgs(0), ", ") & "}"
  Call PASub2(P1, PArgs())
End Sub

Public Sub PASub2(P1, ParamArray PArgs())
  MsgBox "Sub: Pargs(0)(0) = {" & Join(PArgs(0)(0), ", ") & "}"
End Sub

EDIT: I'd be interested to see Kyle's suggestion as well.

I don't understand this and I don't have time now to explore it. There must be something different about a ParamArray array variable and an array variable even when the first is copied into the second.

But I now have something that works. Unless Kyle or someone comes up with something better, I'll go with your suggestion to copy it so a second array variable.

Thanks

 

[shg]

This also works:

Public Function PAMain(P1, ParamArray PArgs())
  MsgBox "Main: PArgs = {" & Join(PArgs, ", ") & "}"
  Call PASub1(P1, CVar(PArgs))
End Function

Public Sub PASub1(P1, PArgs)
  MsgBox "Sub: Pargs = {" & Join(PArgs, ", ") & "}"
End Sub

 

[Kyle123]

Sorry for the late reply, shg your last post is what I meant

 

[JenniferMurphy]

This also works:

Public Function PAMain(P1, ParamArray PArgs())
  MsgBox "Main: PArgs = {" & Join(PArgs, ", ") & "}"
  Call PASub1(P1, CVar(PArgs))
End Function

Public Sub PASub1(P1, PArgs)
  MsgBox "Sub: Pargs = {" & Join(PArgs, ", ") & "}"
End Sub

Nice!

I presume that this is the equivalent of assigning it to a local array, right?

Thank you!!!

 

[JenniferMurphy]

Sorry for the late reply, shg your last post is what I meant

Thanks

 

[Kyle123]

I'm back at a computer now, essentially you just need to pass byVal rather than byRef, so this is a bit cleaner:

Public Function PAMain(P1, ParamArray PArgs())
  MsgBox "Main: P9Args = {" & Join(PArgs, ", ") & "}"
  Call PASub1(P1, PArgs)
End Function

Public Sub PASub1(P1, ByVal PArgs)
  MsgBox "Sub: Pargs = {" & Join(PArgs, ", ") & "}"
End Sub