RE: Passing an array as argument for custom Function

anonymous_at_discussions.microsoft.com
Date: 03/07/05 

Date: Mon, 7 Mar 2005 08:44:57 -0800

When I do that, it seems to only pass the first item in
the array.
For example, lets say I have the following function and
procedure:

Sub test()
    Dim colors(1 To 10) As Integer
    
    For i = 1 To 10
        colors(i) = i + 10
    Next i
   zzz(colors)
End Sub

Function zzz(ParamArray myarr() As Variant)
    Range("B1") = myarr(1)
End Function

When I use myarr(0) in the zzz function, it works fine,
but when I try to use myarr(1), I get a "Subscript out of
range" message.

>-----Original Message-----
>I'm pretty sure you would just pass the name: zzz(colors)
>
>"anonymous@discussions.microsoft.com" wrote:
>
>> And how do I enter the argument when I use the function.
>> Can I just do:
>>
>> zzz(colors())
>>
>> >-----Original Message-----
>> >Use the ParamArray keyword
>> >Function zzz(ParamArray myarr())
>> >
>> >"anonymous@discussions.microsoft.com" wrote:
>> >
>> >> I am trying to find out how to pass an array as an
>> >> argument for a custom function.
>> >> I have an array colors(1 to 10) and I want to pass
the
>> >> whole array into a custom fundtion so I can use the
>> array
>> >> within the function.
>> >>
>> >> Thanks.
>> >>
>> >.
>> >
>>
>.
>

Relevant Pages

  • Re: ReDim not working as expected. Array expert needed.
    ... MsgBox TypeName) ' Type Variant() ... examples of how array dimensions work in VBA. ... Sub ArrayStudies14() ... Dim MyArray As Variant ' Declare nonarray variant. ...
    (microsoft.public.excel.programming)
  • Re: Need Help With Arrays and Passing Arguments
    ... array definition to illustrate without really thinking about it. ... Dim myArray() As Variant ... > Sub CallTest1() ...
    (microsoft.public.word.vba.general)
  • Array Problem
    ... When you define an array as Dim Arrayas Variant, ... routine that needs to recognized that if the array has not ... it needs to exit the sub. ... Dim ArrayPBas Variant ...
    (microsoft.public.excel.programming)
  • Re: Vartype() and vbObject
    ... If the range object refers to multiple cells Value returns a variant array, ... Sub Test2 processes TestVar ... I found the TypeName function. ...
    (microsoft.public.excel.programming)
  • Re: Vartype() and vbObject
    ... If the range object refers to multiple cells Value returns a variant array, ... If the range refers to a single cell, VarType will return the type of the ... Sub Test2 processes TestVar ...
    (microsoft.public.excel.programming)