Solved

Return vba array from user defined function

Posted on 2011-09-20
5
829 Views
Last Modified: 2013-11-27
I'm having difficulties with the basics of passing an array from a function in VBA.
To be used in vba macros in MS Office (Excel & Visio), but without reference to excel ranges.

I start by making a simple dynamic array:

Sub makeArray()
Dim arrItems() As Long
Dim i As Integer
Dim m As Long
m = 2
For i = 0 To 9
ReDim Preserve arrItems(i + 1)
arrItems(i) = i * 10 * m
Next i
For i = 0 To UBound(arrItems) - 1
Debug.Print "arrItems(" & i & ") = "; arrItems(i)
Next i
End Sub

How do I convert the above to a function to be called from this Sub():
Sub doMakeArray()
myArray() as long
Dim m as long
m = 2
myArray =doMakeArray(m)
end sub

I would start off:
Function doMakeArray(m as long)

I would seek to conclude:
doMakeArray = arrItems

I wish to return arrays of either strings or long - data types

Thanks
Kelvin4
 
0
Comment
Question by:Kelvin4
5 Comments
 
LVL 24

Accepted Solution

by:
StephenJR earned 250 total points
ID: 36567548
Do you mean like this?
Sub x()

Dim myArray() As Long
Dim m As Long

m = 2
myArray = doMakeArray(m)

End Sub

Function doMakeArray(m As Long)

Dim arrItems() As Long
Dim i As Integer

ReDim arrItems(10)

For i = LBound(arrItems) To UBound(arrItems)
    arrItems(i) = i * 10 * m
Next i

doMakeArray = arrItems

End Function

Open in new window

0
 
LVL 24

Expert Comment

by:StephenJR
ID: 36567554
Actually line 16 should be ReDim arrItems(9)
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 36567851
You can use:

Function doMakeArray(m As Long) As Variant

That will allow you to return an array.

/gustav
0
 
LVL 11

Assisted Solution

by:Visio_Guy
Visio_Guy earned 250 total points
ID: 36568039
More on this. If you know the size of the array in advance, you can specify it at declaration. If the size is stored in a variable, then you have to use ReDim, but it's probably best to keep ReDim out of loops.

Sub ArrayStuff()

  '// Two ways to pre-size an array in VBA:
  Dim longArray(10) As Long
  Dim stringArray(0 To 14) As String
  
  '// Check out their bounds:
  Debug.Print LBound(longArray) '.....0
  Debug.Print UBound(longArray) '.....10
  
  Debug.Print LBound(stringArray) '...0
  Debug.Print UBound(stringArray) '...14
  
  
  '// Use a variable to specify array size:
  Dim m As Integer
  m = 12
  Dim intArray() As Integer
  ReDim intArray(1 To m - 1)
  
  Debug.Print LBound(intArray) '...0
  Debug.Print UBound(intArray) '...11
  
End Sub

Open in new window

0
 

Author Closing Comment

by:Kelvin4
ID: 36570357
Thanks!
Kelvin
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question