CODEWITHSUNDEEP
vbaexcel
szab.kel
szab.kel

Reputation: 2526

VBA Variant to certain object?

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.

Upvotes: 1

Views: 5844

Answers (2)

gembird
gembird

Reputation: 14053

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.

Upvotes: 1

YowE3K
YowE3K

Reputation: 23974

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

Upvotes: 4

Related Questions