ParamArray

This keyword
The parameter array must be defined with a Variant datatype
The parameter array is always zero based and is not effected by the Option Base statement.


The ParamArray keyword allows you to accept a dynamic number of arguments.
ParamArrays is an abbreviation for parameter array.


This must be the last argument in the list and it cannot be used in the same argument list as an Optional argument (?)


This gives you enormous flexibility because it allows you to pass any data type including objects.



This can only be used as the last argument in a procedure or function
This keyword allows you to provide an arbitrary number of arguments that get submitted as an array.
This keyword cannot be used with ByRef, ByVal or Optional


Sub ProcedureOne(ByVal iConstant As Integer, _ 
                 ParamArray avWorksheets() As Variant)

Dim vWorksheet As Variant

   For Each vWorksheet In avWorksheets

   Next vaWorksheet

End Sub


Option Explicit 
Option Base 1
 
Public Sub testthis()
 
   Call MyProc(Array(1, 2, 3, 4))
  
End Sub


'always zero based array
'array is always 2 dimensional with a 0 first dimension

Public Sub MyProc(ParamArray vSomething()) - since Variant is the default 
OR
Public Sub MyProc(ParamArray vSomething() As Variant)
Dim ivalue As Integer
Dim stest As String
 
   stest = CStr(ivalue)
 
End Sub


ParamArray Optional. Used only as the last argument in arglist to indicate that the final argument is an Optional array of Variant elements.
The ParamArray keyword allows you to provide an arbitrary number of arguments.
ParamArray can't be used with ByVal, ByRef, or Optional.



1 or 2 Dimensional Array ?

Lets consider the following subroutine that accepts an array as its argument.

Public Sub DoSomething(ParamArray vMyArray As Variant) 
End Sub


Dim vArrayOne As Variant 

Call DoSomething("One","Two","Three"))

In this situation the array is passed in as a 1 dimensional array
vMyArray(0) = "One"
vMyArray(1) = "Two"
vMyArray(2) = "Three"



Dim vArrayOne As Variant 

vArrayOne(0) = "One"
vArrayOne(1) = "Two"
vArrayOne(2) = "Three"

Call DoSomething(vArrayOne)

In this situation the array is passed in as a 2 dimensional array
vMyArray(0)(0) = "One"
vMyArray(0)(1) = "Two"
vMyArray(0)(2) = "Three"


Need to call the Array_ParamArray1Dimension function to convert this back to a 1 dimensional array.



© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

Top

PrevNext