Pass array to ParamArray

by

Pass a ParamArray argument to another function that expects a ParamArray argument (delegate ParamArray arguments).
I need to delegate to a function of type: strf(str as string, ParamArray args() as Variant) as String the arguments received in other function in a ParamArray passing directly without explicitly write.
The restrictions I’ve found are:

  1. A ParamArray() it can only be passed to another function that expects a ParamArray.
  2. The ParamArray is received at element 0 as a Variant ()
  3. When the second function receives it increases a level of depth
    I have not found any satisfactory solution, but I have written a function that works perfectly, undoing the depth levels added and returning a vector with arguments received.

Code:

Option Explicit
Option Base 1

Public Sub PrAr1(ParamArray pa1() As Variant)
Dim arr() As Variant
  arr = fn.ParamArrayDelegated(pa1)
  PrAr2 pa1
End Sub

Public Sub PrAr2(ParamArray pa2() As Variant)
Dim i As Integer, arrPrms() As Variant
  arrPrms = fn.ParamArrayDelegated(pa2)
  For i = 0 To UBound(arrPrms)
    Debug.Print s.strf("i: %0 prm: %1 ", i, arrPrms(i))
  Next i
  PrAr3 pa2
End Sub

Public Sub PrAr3(ParamArray pa3() As Variant)
Dim i As Integer, arrPrms() As Variant
  arrPrms = fn.ParamArrayDelegated(pa3)
  For i = 0 To UBound(arrPrms)
    Debug.Print s.strf("i: %0 prm: %1 ", i, arrPrms(i))
  Next i
End Sub

Public Function ParamArrayDelegated(ParamArray prms() As Variant) As Variant
Dim arrPrms() As Variant, arrWrk() As Variant
'When prms(0) is Array, supposed is delegated from another function
  arrPrms = prms
  Do While VarType(arrPrms(0)) >= vbArray And UBound(arrPrms) < 1
    arrWrk = arrPrms(0)
    arrPrms = arrWrk
  Loop
  ParamArrayDelegated = arrPrms
End Function

More Related Contents:

  1. Is it possible to pass parameters programmatically in a Microsoft Access update query?
  2. using for loop for vba
  3. Simple VBA code to open cells one after the other
  4. Refer to sheet using codename
  5. What are some good patterns for VBA error handling?
  6. VBA Macro On Timer style to run code every set number of seconds, i.e. 120 seconds
  7. VB6 pass by value and pass by reference
  8. Last not empty cell (column) in the given row; Excel VBA
  9. HTML Text with tags to formatted text in an Excel cell
  10. Get list of Excel files in a folder using VBA [duplicate]
  11. Getting Error 3340 Query ‘ ‘ is corrupt while executing queries DoCmd.RunSQL
  12. VBA to copy a file from one directory to another
  13. Excel VBA – QueryTable AfterRefresh function not being called after Refresh completes
  14. excel vba- extract text between 2 characters
  15. Round function in Excel, worksheet function vs VBA
  16. What is behind this difference in parentheses effect in VBA?
  17. Count the number of rows in another sheet [duplicate]
  18. Getting Excel to refresh data on sheet from within VBA
  19. Working with Unicode file names in VBA (using Dir, FileSystemObject, etc.)
  20. Search structured text in Outlook body
  21. VBA Password Input with Cancel Function
  22. What does the $ symbol do in VBA?
  23. Fast compare method of 2 columns [closed]
  24. How can an Excel Add-In respond to events in any worksheet?
  25. Iterating through all the cells in Excel VBA or VSTO 2005
  26. SendKeys is messing with my NumLock key via VBA code in Access form
  27. How can I display my windows user name in excel spread sheet using macros? [closed]
  28. How does Excel’s worksheet password protection work
  29. How to find a value in an excel column by vba code Cells.Find
  30. Best way to define a large vba string – i.e. heredoc equivalent?

Leave a Comment