Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / November 2007

Tip: Looking for answers? Try searching our database.

Pass an array

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Karen53 - 25 Oct 2007 18:39 GMT
Hi,

How do I pass an array to a sub routine?

Dim HeaderArray(1 to 6) as long

HeaderArray(1) = 43
HeaderArray(2) = 90
HeaderArray(3) = 138
HeaderArray(4) = 186
HeaderArray(5) = 234
HeaderArray(6) = 282

I tried passing it as HeaderArray but got strange results.

Call NextRoutine(HeaderArray)

Signature

Thanks for your help.
Karen53

Chip Pearson - 25 Oct 2007 19:15 GMT
Karen,

How have you declared your NextRoutine procedure?  You need to declare the
input paramater as an array of the exact same data type as the array being
passed, or declare it as a Variant (not as an array of variants). For
example,

Sub CalledProc(Arr() As Long)
   Dim N As Long
   For N = LBound(Arr) To UBound(Arr)
       Debug.Print Arr(N)
   Next N
End Sub

You can call this with code like:

Sub AAA()
   Dim Arr(1 To 3) As Long
   Dim L As Long

   Arr(1) = 11
   Arr(2) = 22
   Arr(3) = 33

   CalledProc Arr
End Sub

Since CalledProc expects an array of Longs, you'll get an error if you try
to pass an array of any other data type to it. The alternative is to declare
the input parameter as a Variant. For example,

Sub CalledProc(Arr As Variant)
   Dim N As Long
   If IsArray(Arr) = True Then
       For N = LBound(Arr) To UBound(Arr)
           Debug.Print Arr(N)
       Next N
   Else
       Debug.Print "not an array"
   End If
End Sub

This can accept an array of any data type and data that is not an array. It
tests the input parameter with IsArray to see if it was passed an array,.

See www.cpearson.com/Excel/PassingAndReturningArrays.htm for more
information and example code about passing arrays to and from procedures.

Signature

Cordially,
Chip Pearson
Microsoft MVP  - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

> Hi,
>
[quoted text clipped - 12 lines]
>
> Call NextRoutine(HeaderArray)
Karen53 - 25 Oct 2007 22:48 GMT
Thank You, Chip!
Signature

Thanks for your help.
Karen53

> Karen,
>
[quoted text clipped - 60 lines]
> >
> > Call NextRoutine(HeaderArray)
Rick Rothstein (MVP - VB) - 25 Oct 2007 19:16 GMT
Can you show us the NextRoutine's declaration statement please?

Rick

> How do I pass an array to a sub routine?
>
[quoted text clipped - 10 lines]
>
> Call NextRoutine(HeaderArray)
Karen53 - 28 Nov 2007 01:39 GMT
Signature

Thanks for your help.
Karen53

> Hi,
>
[quoted text clipped - 12 lines]
>
> Call NextRoutine(HeaderArray)
Alan Beban - 28 Nov 2007 02:17 GMT
What "strange results" did you get?

Alan Beban
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.