CODEWITHSUNDEEP
vbaexcelexcel-2010
Paradox
Paradox

Reputation: 4566

Run-Time error on add method VBA

My function's intent is explained in the docstring. However, when I run it, I get a "Run-Time error '91': Object variable or With block variable not set", and a line of code is highlighted, as indicated below. I cannot find the source of this issue, especially since I am rather new to VBA. Any help would be greatly appreciated!

'==============================|Range_Collection FUNCTION|=============================='
' Given a collection and a range, add each value in that range to the collection, except
' for False values.
Function Range_Collection(col As Collection, rng As Range) As Collection

    Dim Val As Variant
    For Each Val In rng
        If Not Val.Value = False Then
            col.Add Val.Value ;************** THIS CODE IS HIGHLIGHTED************
        End If
    Next Val
    Set Range_Collection = col

End Function

Upvotes: 1

Views: 116

Answers (3)

Mike Robinson
Mike Robinson

Reputation: 8995

Also, watch out for this little "potential language oddity" ... (in any language)

When you wrote:

If Not Val.Value = False Then

... then you no doubt intended to say: (note the parentheses, carefully ...)

 If Not (Val.Value = False) Then

... (in other words, "not equal to") ...

But most grammars actually bind the Not operator more tightly than a relational-operator such as =, producing what could be this actual interpretation, with no syntax-errors:

 If (Not Val.Value) = False Then

... (in other words, "if Val.Value is True!")

The compiler judges your statement to be "syntactically and semantically correct," as in fact it is. But its interpretation of what you have written might not be what you thought it was. If you include the parentheses as I have shown above, all ambiguity is removed.

  • If you want "not equal to", always use the appropriate operator.
  • When using logical operators, use parentheses plentifully to make your intentions clear, both to the compiler and to other people.

Oddly enough, this sort of issue led to a bug in the very first program that I ever wrote: which "was 6 lines long, took me 6 months to write (in BASIC for a timesharing computer, nevermind how long ago now), and had a bug in it."

Upvotes: 1

Siddharth Rout
Siddharth Rout

Reputation: 149335

Let's say your worksheet looks like this

enter image description here

This is what I get when I run the code

enter image description here

Further to my comment, see this

The syntax is col.add "Item","Key". The key has to be unique. If you have duplicate values then then use OERN as I suggested or use a unique key.

Sub Sample()
    Dim c As New Collection

    For Each itm In Range_Collection(c, Range("A1:A5"))
        Debug.Print itm
    Next
End Sub
Function Range_Collection(col As Collection, rng As Range) As Collection

    Dim rVal As Variant

    For Each rVal In rng
        If Not rVal.Value = False Then
            On Error Resume Next
            col.Add rVal.Value, CStr(rVal.Value)
            On Error GoTo 0
        End If
    Next rVal

    Set Range_Collection = col
End Function

Upvotes: 1

Jeanno
Jeanno

Reputation: 2859

Change Dim Val As Variant to Dim Val As Range

Upvotes: 1

Related Questions