The following simple example tries to demonstrate how an array can be passed to a VBA function.
The code snippet declares two functions:
dot_product_explicit_datatype (byRef vec_1() as double, byRef vec_2() as double) as double
dot_product_variant (byRef vec_1 as variant, byRef vec_2 as variant)
The first function has the advantage that the array-parameters are strongly typed (array of doubles). However, on its downside, when this function is called, it needs a variable that matches this declared type.
The second function has the advantage that it can be called with an «in-place declaration» of an array:
xyz = dot_product_variant( array(…), array(…) )
option explicit
sub main() ' {
dim v1(1 to 3) as double
dim v2(1 to 3) as double
v1(1) = 1
v1(2) = 3
v1(3) = -5
v2(1) = 4
v2(2) = -2
v2(3) = -1
'
' Call «strongly typed» version of function:
'
debug.print(dot_product_explicit_datatype(v1 , v2 ))
'
' Call «weakly typed» version of function.
' Note how easy it is to pass the values
' of the array to the function:
'
debug.print(dot_product_variant (array(1, 3, -5), array(4, -2, -1) ))
end sub ' }
function dot_product_explicit_datatype (byRef vec_1() as double, byRef vec_2() as double) as double ' {
dim i as long
dot_product_explicit_datatype = 0
for i = lBound(vec_1) to uBound(vec_1)
dot_product_explicit_datatype = dot_product_explicit_datatype + vec_1(i) * vec_2(i)
next i
end function ' }
function dot_product_variant (byRef vec_1 as variant, byRef vec_2 as variant) as double ' {
dim i as long
dot_product_variant = 0
for i = lBound(vec_1) to uBound(vec_1)
dot_product_variant = dot_product_variant + vec_1(i) * vec_2(i)
next i
end function ' }