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?

 

[JenniferMurphy]

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

Very nice. The ByVal option essentially does the conversion that CVar would have done, right?

Thank you. My code is now very tight! That always feels so good.

 

[shg]

I had expected this would work, but it doesn't:

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

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

 

[Kyle123]

I tried that too, yeah it doesn’t work. It should do, but someone mustnt have written the rule that allows that when compiling