Return an Array via UDF

Question

I have two macros. One of them creates an array based on a range. The second will take that array, go through it and remove any duplicate entries. I then want the second macro to return an Array that I can continue using in the first macro.

Here's what I've tried so far:

This is to get the array:

Sub array_Test()
Dim array1() As Variant
ReDim array1(1 To 10)
array1 = Range("A1:A10")
GEN_USE_Remove_Duplicates_from_Array (array1)
Dim i As Integer
Debug.Print Join(array1, "/") 'Now that array1 has had duplicates removed, print the remaining numbers.  This is where I don't know what to do
For i = 0 To UBound(array1)
    Range(Cells(i + 1, 2).Value) = array1(i + 1)
Next i
End Sub

And here's that GEN_USE_Remove_Duplicates... sub:

Sub GEN_USE_Remove_Duplicates_from_Array(arr As Variant)
Dim Array_1
    Array_1 = arr
Dim Array_2()
Dim Array_toRemove()

Dim dic As New Scripting.Dictionary
Dim arrItem, x As Long
For Each arrItem In Array_1
    If Not dic.Exists(arrItem) Then
        dic.Add arrItem, arrItem
    End If
Next

Array_2 = dic.Keys


Debug.Print Join(Array_2, "/")

GEN_USE_Remove_Duplicates_from_Array = Array_2

End Sub

That successfully keeps only unique values. Now, how do I get that result (Array_2) to be used in the array_Test sub? Do I need to create a function instead of a Sub?

Thanks so much for any advice/help/tips!

try function GEN_USE_Remove_Duplicates_from_Array(arr As Variant) ... end function and assign the returned array to an array like array1 = GEN_USE_Remove_Duplicates_from_Array (array1). — user4039065
– user4039065, Commented Jul 20, 2015 at 18:02
Aha! That did it - I tried doing the function thing, but didn't think to do the array1 = GEN_USE_Remove...(array1). Thanks so much Jeeped, as always!! Edit: Meta question - can I mark your comment as an answer? Or can you put that in an "Answer" so I can do so, so you get the credit you deserve? — BruceWayne
– BruceWayne, Commented Jul 20, 2015 at 18:16

user4039065 · Accepted Answer · 2015-07-20 18:26:51Z

2

The macro Sub isn't going to return a value but you can easily swap it out to a function that will.

function GEN_USE_Remove_Duplicates_from_Array(arr As Variant)
    ' lots of code stuff here ...
    GEN_USE_Remove_Duplicates_from_Array = Array_2
end function

Assign the returned array to an array like,

array1 = GEN_USE_Remove_Duplicates_from_Array(array1)

fwiw, you should be able to leave it as a sub and use,

arr = array2

That should also change the array passed in as the single parameter to the de-duped array.

answered Jul 20, 2015 at 18:26

user4039065

Sign up to request clarification or add additional context in comments.

3 Comments

BruceWayne Over a year ago

If I leave the GEN_USE as a sub, how to I run that from array_Test()? I tried GEN_USE_Remove_Duplicates_from_Array (array1) but when the array_Test() sub gets to Debug.Print Join(array1, "/") I get a runtime error '5', "invalid procedure call or argument" .

user4039065 Over a year ago

Assuming that array2 has been successfully created as a non-duped array from arr then the last line in the sub should be arr = array2. This reassigns the arr array passed into the sub to have the values from the de-duped array2. In this case, you would simply call the 'helper' sub with GEN_USE_Remove_Duplicates_from_Array array1 as it is transforming its parameter, not returning the array like a function does.

BruceWayne Over a year ago

Thanks! For some reason, I think just changing GEN_USE_Remove_Duplicates_from_Array (array1) to GEN_USE_Remove_Duplicates_from_Array array1 did the trick. Thanks a ton again!

Collectives™ on Stack Overflow

Return an Array via UDF

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related