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?

 

[dmt32]

Hi,
It’s always been my understanding that a parameter in a procedure specified as ParamArray is always declared ByVal.

A workaround maybe to achieve what you want would be to declare a variable as variant & then ReDim to required no of array elements & pass it ByRef to your function.

Something like following maybe

Public Sub Main()
    
'Define variable
    Dim PA As Variant
    
'size array
    ReDim PA(1 To 3)
    
'Initialize elements
    PA(1) = 100
    PA(2) = 0
    PA(3) = "Off"
    
'display
    MsgBox "PA1 " & PA(1) & Chr(10) & _
            "PA2 " & PA(2) & Chr(10) & _
            "PA3 " & PA(3) & Chr(10)
        
    
' Pass array by ByRef
' so the subroutine can change elements if necessary.
    PACheck PA
    
    If Not IsError(PA) Then
'show updated array elements
        MsgBox "PA1 " & PA(1) & Chr(10) & _
                "PA2 " & PA(2) & Chr(10) & _
                "PA3 " & PA(3) & Chr(10)
        
    Else
'report error
        MsgBox "An Error returned"
    End If
    
End Sub




Public Function PACheck(ByRef PArgs As Variant) As Boolean


' Run through Array elements & set new values


PArgs(1) = 10
PArgs(2) = 100
PArgs(3) = "On"


'or return an error
'PArgs = CVErr(10)


End Function

when the array is passed to the function the elements can be updated & values returned to the calling procedure which should be displayed in the msgbox.

If you un-comment the line PArgs = CVErr(10) your function should return the raised error.

This is just an idea & not fully sure if this is what you are looking to achieve & maybe another here can offer further guidance.

Hope Helpful

Dave

 

[Kyle123]

Can’t you just declare the parameter as a variant rather than an array?

 

[dmt32]

Can’t you just declare the parameter as a variant rather than an array?

Hi,
if your response is directed at myself then that's what I have done in OPs function.
It may or may not be what OP wants, just an idea to play with.

Dave

 

[Kyle123]

No, sorry, missed your response altogether

 

[JenniferMurphy]

So is there no way to simply pass the ParamArray in its entirety to the Sub for processing?

This sequence does not get any compile errors, but the statement inside the loop in PASub gets an error.

Public Function PAMain(P1, ParamArray PArgs())

Dim msg As String
Dim i As Long

msg = "Main: "
For i = LBound(PArgs) To UBound(PArgs)
  msg = msg & "PArgs(" & i & ") = " & PArgs(i) & " "
Next i

MsgBox msg

Call PASub(P1, PArgs)

End Function



Public Sub PASub(P1, ParamArray PArgs())

Dim msg As String
Dim i As Long

On Error GoTo ErrorTrap
msg = "Sub: "
For i = LBound(PArgs) To UBound(PArgs)
  msg = msg & "PArgs(" & i & ") = " & PArgs(i) & " "
Next i

MsgBox msg
GoTo Done

ErrorTrap:
MsgBox "Error occurred"

Done:
End Sub

 

[JenniferMurphy]

Problem solved. I wasn't able to pass the original ParamArray variable to the sub, but if I copied it to another array variable, I could pass that. Here's that code:

Public Function PAMain(P1, ParamArray PArgs())

Dim msg As String
Dim i As Long
Dim parray()    'Define a local array variable
parray = PArgs  'Copy the ParamArray to the local array variable

msg = "Main: "
For i = LBound(PArgs) To UBound(PArgs)
  msg = msg & "PArgs(" & i & ") = " & PArgs(i) & " "
Next i

MsgBox msg

Call PASub(P1, parray)  'Pass the local copy of the ParamArray variable

End Function



Public Sub PASub(P1, parray)

Dim msg As String
Dim i As Long

On Error GoTo ErrorTrap
msg = "Sub: "
For i = LBound(parray) To UBound(parray)
  msg = msg & "PArgs(" & i & ") = " & parray(i) & " "
Next i

MsgBox msg
GoTo Done

ErrorTrap:
MsgBox "Error occurred"

Done:
End Sub

 

[shg]

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

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

To answer your next question, I don't know; I just looked at PArgs in the Locals window.

 

[JenniferMurphy]

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

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

To answer your next question, I don't know; I just looked at PArgs in the Locals window.

You are amazing. This works perfectly. I thought I tried it this way, but obviously not.

Thank you

 

[shg]

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