Solved: Returning an array from a function

[gscarter]

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]

ARe you looking to return the array to a VBA procedure, or to worksheet cells?

[gscarter]

VBA procedure

[Bob Phillips]

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]

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]

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

[vba]

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)
[/vba]

[gscarter]

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

[vba]

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)
[/vba]

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

Thanks
Gary