• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1028
  • Last Modified:

Return vba array from user defined function

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
Kelvin4
Asked:
Kelvin4
2 Solutions
 
StephenJRCommented:
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
 
StephenJRCommented:
Actually line 16 should be ReDim arrItems(9)
0
 
Gustav BrockCIOCommented:
You can use:

Function doMakeArray(m As Long) As Variant

That will allow you to return an array.

/gustav
0
 
Visio_GuyCommented:
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
 
Kelvin4Author Commented:
Thanks!
Kelvin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now