PDA

View Full Version : Solved: Returning an array from a function



gscarter
07-17-2008, 07:51 AM
Hello,

I am using Excel within Office XP (2002).

Im currently doing a project in which i copy two spreadsheets into one, and then use that data to create arrays of unique customers. I have created a function (below) that will do this.


Function uniqueCustomer(column As String) As String()

Dim I As Integer
Dim J As Integer
Dim flag As Integer
Dim customer() As String
Dim currentCustomer As String
Dim nextCustomer As String
I = 1
J = 0

ReDim customer(J) As String
customer(J) = ThisWorkbook.Worksheets("Sheet1").Range(column & Trim(CStr(I))).Value

Do
currentCustomer = ThisWorkbook.Worksheets("Sheet1").Range(column & Trim(CStr(I))).Value
nextCustomer = ThisWorkbook.Worksheets("Sheet1").Range(column & Trim(CStr(I + 1))).Value
flag = 0
For Q = 0 To J
If customer(Q) = currentCustomer Then
flag = flag + 1
End If
Next Q

If flag = 0 Then
ReDim Preserve customer(J + 1)
customer(J + 1) = currentCustomer
J = J + 1
End If
I = I + 1
Loop While nextCustomer <> ""

uniqueCustomer = customer

End Function
The problem i have is i dont know whether i have correctly returned the array from the function (its all i could work out from the interent), and if so how do i reference that array in another function?

This may seem a silly question but its got me stumped.

Thanks
Gary

Bob Phillips
07-17-2008, 09:14 AM
ARe you looking to return the array to a VBA procedure, or to worksheet cells?

gscarter
07-17-2008, 09:15 AM
VBA procedure

Bob Phillips
07-17-2008, 09:49 AM
I haven't tried it, have you?

The main thing I would suggest is changing the types to variant not String(). I would also rationalise the Redim'ing, but that is a performance issue , not logical.

gscarter
07-18-2008, 01:28 AM
I haven't tried it, have you?

The main thing I would suggest is changing the types to variant not String(). I would also rationalise the Redim'ing, but that is a performance issue , not logical.

I figured it out, i simply used:

month1cust = uniqueCustomer("A")
in the function i wished to use the returned array.

I though using variant took longer as it hadnt got a type? I dont know its my first time using vba, its just what i seemed to get from the net. Is this incorrect?

So if i use variant i can just add to the array each time without redim'ing it?

Bob Phillips
07-18-2008, 01:51 AM
You are right that variants are slower than specific type, but they are more flexible, and it is usually flexibility that I need with arrays. But no, variant doesn't preclude the need to redim.

But the type is not the killer, it is the constant redim'ong, that is why I tend to use a loop construct like this



For Each something In mycollection

If i Mod 1000 = 0 Then ReDim Preserve ary(i + 1000)
ary(i) = something.property
i = i + 1
Next something

ReDim Preserve ary(i)

gscarter
07-18-2008, 02:01 AM
You are right that variants are slower than specific type, but they are more flexible, and it is usually flexibility that I need with arrays. But no, variant doesn't preclude the need to redim.

But the type is not the killer, it is the constant redim'ong, that is why I tend to use a loop construct like this



ReDim ary(1 To 1000)
For Each something In mycollection

If i > 1000 Then ReDim Preserve ary(i + 1000)
ary(i) = something.property
i = i + 1
Next something

ReDim Preserve ary(i)


Thanks alot for the advice, i will try that as this macro is going to require alot of array manipulation.

Thanks
Gary