VBA Variant to certain object?

Question

I started using Collection in VBA (Excel AddIn --> xlam), but find it quite hard to use. I cannot retrieve anything I put into, other then Variants. In Java for example, if you make a Collection, you assign a type to it, like Collection<MyPerfectObjectThatStoresSomeData>, so when I foreach it, I know I get my object back and can use it like that. When I would like to do the same in VBA, I get back Variant types and I can't even cast it to my type.

Example.

Let's say I would like to get the last entry from the Collection I fill up like this:

Private Function FindTargets(sheet As Worksheet, target As String) As Collection
    Dim result As New Collection
    Dim aCell As range
    Dim i As Long
    For i = 1 To 10456
      Set aCell = sheet.Rows(i).Find(What:=target, LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        If Not aCell Is Nothing Then
             MsgBox "Value Found in Cell " & aCell.Address & _
            " and the Cell Column Number is " & aCell.Column
            result.Add aCell
        End If
    Next i

    Set FindTargets = result
End Function

After, I would like to loop through the results, but I cannot event get the last entry out without an error:

Dim targetCollection As Collection
Set targetCollection = FindTargets(targetSheet, "some text")
Dim target As range
target = targetCollection.Item(targetCollection.Count - 1)

I get the error:

Run-time error '91':
Object variable or With block variable not set

If I try

Set target = targetCollection.Item(targetCollection.Count - 1)

I get:

Run-time error '1004':
Application-defined or object defined error

Can I only loop and get entries from a Collection into other than Variant types?

Edit: To be more specific, this code would need the range's coordinates, but in a Variant I get the text of the cell. I thought about making a new Class, like CellProperties with properties to both text and coordinates and put this into the Collection, but again, I cannot retrieve anything other than Variant from a Collection.

The last item will be found using `Set target = targetCollection.Item(targetCollection.Count)`. Is it possible that you were only adding one item to the collection, and therefore your error is simply because you were trying to access `targetCollection.Item(0)`? (I tested it using a count-down version of your function, which simply added the 100 cells in A1:A100 to the collection.) — YowE3K, Mar 24 '17 at 06:44
The collection have two elements in it. Is the VBA Collections seriously indexed starting from 1? — szab.kel, Mar 24 '17 at 06:47
It certainly appears to be when I look at a collection in the Watch window. I'll temporarily post my test code as an "answer" so that you can see whether it works for you. — YowE3K, Mar 24 '17 at 06:47
Oh thanks, I did not know we had a Watch window. for the Type it says Variant/Object/Range, but I cannot read any elements without the errors. — szab.kel, Mar 24 '17 at 06:49
The Watch window should show `targetCollection` as a `Collection/Collection` and `target` as a `Range/Range`. Ahh, but each item of the `Collection/Collection` will be a `Variant/Object/Range`, which can validly be used to `Set` a `Range/Range`. — YowE3K, Mar 24 '17 at 06:52

YowE3K · Accepted Answer · 2017-03-24T07:02:52.423

4

It appears that your error is caused by assuming a Collection's items are zero-based, but the first item in a Collection has an index of 1

Below is a cut down version of your FindTargets which I used for testing, and a test subroutine that returns the Address of the last cell added to the Collection:

Private Function FindTargets(sheet As Worksheet, target As String) As Collection
    Dim result As New Collection
    Dim i As Long
    For i = 1 To 100
        result.Add Cells(i, 1)
    Next i

    Set FindTargets = result
End Function

Sub test()
Dim targetCollection As Collection
Set targetCollection = FindTargets(Worksheets("Sheet1"), "some text")
Dim target As Range
Set target = targetCollection.Item(targetCollection.Count)
MsgBox target.Address 'will display $A$100
End Sub

edited Mar 24 '17 at 07:02

answered Mar 24 '17 at 06:49

YowE3K

23,852
7
26
40

@appl3r But if your `targetCollection.Count` was 2, then Count - 1 should have been fine. – YowE3K Mar 24 '17 at 06:55
@appl3r - BTW - you say "but in a Variant I get the text of the cell" - was that simply trying something like `MsgBox target` (which uses the default property of a range which is `.Value` and is therefore equivalent to saying `MsgBox target.Value`)? – YowE3K Mar 24 '17 at 06:58
I guess I had another assignment after this one that threw the same exception. – szab.kel Mar 24 '17 at 06:58
If you hover over the variable while you are on a breakpoint, you get a popout printing its value, I though it was a string, but using watches I can check it precisely. – szab.kel Mar 24 '17 at 07:00
@appl3r that's because of the lovely *default member* feature, which in the case of a `Range` returns its `Value`; in the case of a `Collection` the default member is the `Item` property, so `myCollection.Item(i)` is exactly the same as `myCollection(i)` - although I'd suggest avoiding implicit default member references, since they make the code "easier to type" but harder to follow and often confusing, *especially* when object references boxed in `Variant` are involved. – Mathieu Guindon Mar 24 '17 at 14:38

score 1 · Answer 2 · edited May 23 '17 at 12:10

1

VBA-Collection is data structure which can contain heterogeneous objects. So unlike Collection Of MyPerfectObjectThatStoresSomeData where the collection ensures that object of certain data type can be added into, in VBA-Collection we can store anything in the collection so it can contain heterogeneous object types at the same time. But in your case the collection actually contains objects of type Variant\Object\Range. This means it contains objects of type Variant and sub-type Range. VBA-Collection can be enumerated and it hast Item method which can be used to acces items of the collection where the first item hast index of 1.

In you case you can do e.g. this:

targetCollection.Add "Banana"
targetCollection.Add ActiveSheet
targetCollection.Add Excel.Application

Dim itm As Variant
For Each itm In targetCollection
    If TypeName(itm) = "Range" Then _
        Debug.Print itm.Address
Next itm

To get the last element safely (in case the last element is of type Range):

Dim lastElement As Range
If targetCollection.Count > 0 Then _
    Set lastElement = targetCollection.Item(targetCollection.Count)

As a side note: why not use Dim As New.

edited May 23 '17 at 12:10

Community

1
1

answered Mar 24 '17 at 09:09

Daniel Dušek

13,683
5
36
51

So I can check the `.Address` on a `Variant` without casting? Interesting – szab.kel Mar 24 '17 at 09:13
`Variant` is variant, which means it can contain anything. If the `sub-type` of it hast a property `Address` then you can use it. – Daniel Dušek Mar 24 '17 at 09:14
4

Per "why not use Dim As New" - there is a subtle difference in instantiating an object that way - it uses the predeclared instance. This allows auto-instantiation of the object, decreases performance of compiled code (due to checking for whether an object is instantiated everywhere it's used), and can easily lead to coding errors. It is better to avoid unless you *require* object auto-instantiation. – Comintern Mar 24 '17 at 14:26
1

BTW, the reason to cast is that a `Variant` doesn't *have* a sub-type `Range` - it has a sub-type `IDispatch`. Calling members by name directly from the `Variant` results in late-bound calls on early-bound objects. – Comintern Mar 24 '17 at 14:29
2

@appl3r absolutely. `Variant` (and `Object`) member calls are resolved at run-time (late-bound), similar to what duck-typed languages like Javascript do, or how C#'s `dynamic` works (i.e. run-time error 438 "object does not support this property or method" gets raised if `itm.IDontExist` is called, but the code will compile perfectly fine). Wanting strongly-typed generics in VBA is pretty much asking for the moon, VBA knows nothing of generics. – Mathieu Guindon Mar 24 '17 at 14:32

VBA Variant to certain object?

2 Answers2