VBA: Passing a 2-D Array into a Collection Function [duplicate]

Question

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.

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
– Alastair, Commented Apr 21, 2021 at 13:38
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
– Mathieu Guindon, Commented Apr 21, 2021 at 13:47
Ah! I'm sorry, I totally missed the link! Thank you for that (sorry if I came across as rude) — Alastair
– Alastair, Commented Apr 21, 2021 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
– Nigel Heffernan, Commented Apr 22, 2021 at 15:51

BZngr · Accepted Answer · 2021-04-21 11:58:29Z

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

answered Apr 21, 2021 at 11:58

BZngr

6816 silver badges7 bronze badges

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

Comments

Alastair · Accepted Answer · 2021-04-21 11:58:28Z

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.

answered Apr 21, 2021 at 11:58

Alastair

839 bronze badges

Collectives™ on Stack Overflow

VBA: Passing a 2-D Array into a Collection Function [duplicate]

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related