0

I'm trying to pass a 2D array into a function to return a collection of unique values from the first column.

When I run it I get a compile error ("Compile error: Argument not optional")

This is my code block:

Function uniqueColl(ByVal arr As Variant) As Collection
    Dim tempColl As New Collection
    
    For x = 1 To UBound(arr, 2)
        tempColl.Add arr(1, x)
    Next x
    
    uniqueColl() = tempColl

End Function

I've tried it with the brackets on the last line, without them and filling the brackets with "arr", but none of those things seem to work. Any tips would be much appreciated.

Thank you.

Alastair
  • 63
  • 8
  • I tried looking for this exact question several times, but I couldn't find it. When I posted it I found a link with the answer and then someone posted that content too. I gave them the tick. What's the problem? – Alastair Apr 21 '21 at 13:38
  • 1
    Huh? There's no problem at all, I even gave an upvote to both posts. I'm just letting you know that there's a free & open-source VBE tool that can help you identify this (and many others) type of issues in your VBA code. Cheers! – Mathieu Guindon Apr 21 '21 at 13:47
  • Ah! I'm sorry, I totally missed the link! Thank you for that (sorry if I came across as rude) – Alastair Apr 21 '21 at 14:16
  • The answer's in your return statement: `uniqueColl() = tempColl` - **ditch the brackets**, the compiler is parsing that as a recursive call to the function, and your intention is to place a value (or a pointer to it) in the function's return buffer. The correct statement for doing that is `Set uniqueColl = tempColl`, and I can see that @BZngr has that answer below, in full. – Nigel Heffernan Apr 22 '21 at 15:51

2 Answers2

4

Use Set uniqueColl = tempColl

Or,

Function uniqueColl(ByVal arr As Variant) As Collection
    Set uniqueColl = new Collection
    For x = 1 To UBound(arr, 2)
        uniqueColl.Add arr(1, x)
    Next x
    
End Function
BZngr
  • 671
  • 5
  • 6
1

This is the answer to the question:

How to return a result from a VBA function

The final line needs to use a set.

Alastair
  • 63
  • 8