Hi i am new to excel programing can any body help me in knowing how to pass
an array to function byref. i need this very badly

Thanks in advance

Sub main()

Dim a(5, 5)


y = PassArray(a)


End Sub

Function PassArray(ByRef a())

b = a(5, 1)
End Function


"kris" wrote:

Hi i am new to excel programing can any body help me in knowing how to pass
an array to function byref. i need this very badly

Thanks in advance

Joel has shown you the code.
However, as ByRef is the default in VB/VBA, you can omit it.
Also, if you try to change the function signature to ByVal instead, you will
get a compile error "Array argument must be ByRef".

So your array will always be byref anyway.

Note that if the array is in a Variant, then all the normal ByRef/ByVal
considerations apply. e.g.

Private Sub CommandButton1_Click()
Dim a(5, 5)

a(5, 1) = "Original"

PassArray (a)
Debug.Print a(5, 1)

PassArray a
Debug.Print a(5, 1)

End Sub

Function PassArray(ByRef a As Variant)
a(5, 1) = "New value"
End Function

NickHK

"kris" wrote in message
...
Hi i am new to excel programing can any body help me in knowing how to
pass
an array to function byref. i need this very badly

Thanks in advance

"kris" wrote:

Hi i am new to excel programing can any body help me in knowing how to pass
an array to function byref. i need this very badly

Thanks in advance


Thanks again Joel and NickHK