Microsoft Office Development and Consultancy
 Home|Excel|

VBA

|C#|Finance|Tools|Newsletter|Feedback|Contact 
 VBA > Arrays > Using ParamArray< Previous | Next > 
 

Using ParamArray

 

 

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.

 

 

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

 

 
1
2
3
4
5
6
7
8
9
10
Sub ProcedureOne(ByVal iConstant As Integer, _
                 ParamArray avWorksheets() As Variant)

Dim vWorksheet As Variant

   For Each vWorksheet In avWorksheets

   Next vaWorksheet

End Sub
   

 
11
12
13
14
15
16
17
18
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

 
 

 
 
19
20
21
22
23
24
25
26
27
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.

 
 
28
29
Public Sub DoSomething(ParamArray vMyArray As Varant)
End Sub
   

 
30
31
32
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"

 


 
33
34
35
36
37
38
39
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.

 


 © 2013 Better Solutions Limited. All Rights Reserved.< Previous | Top | Next > | 02-Jan-2013