Runtime Error 424 - Text Property in Range object

Question

I am a VBA beginner, so the below is likely to be a basic step that I've missed.

When trying to edit the Text property of the Range method , I keep getting

Runtime error 424 , object required.

e.g. for the below code:

Range("A1").Text = "ABC"

Is this a case of setting the range?

Answer 1

The run-time error "424 Object Required" is explainable, and believe it or not, actually makes sense.

Range.Text is read-only, so you can't assign to it; arguably there should be a compile-time error complaining about the assignment attempt, such as:

Invalid use of property

Or

Can't assign to read-only property

However, because Range.Text returns a Variant and not a String, and the property is get-only, VBA assumes the assignment is legal, and that the property will be returning a Variant/Object, where the Object has a default property that can be assigned.

So assigning this property in Sheet1's code-behind:

Public Property Get Foo() As String
    Foo = "FOO"
End Property

Is a compile-time can't assign to read-only property error.

But this:

Public Property Get Foo() As Variant
    Foo = "FOO"
End Property

Is a run-time object required error - because for the assignment to be legal, the Property Get function is expected to return an object. And when it doesn't, then object required makes total sense.

Consider this:

Public Property Get Foo() As Range
    Set Foo = ActiveCell
End Property

This property is just as read-only as any of the above: you cannot legally re-assign the reference Foo is returning.

But this is totally legal:

Sheet1.Foo = 42

And, in fact, implicit code for this:

Sheet1.Foo().Value = 42

So the assignment is assigning to the write-enabled, default property of the returned object reference.

And this reference assignment:

Set Sheet1.Foo = ActiveCell

Is a compile-time invalid use of property, because Foo doesn't expose a Set accessor.

This is indeed very confusing for beginners. Ironically, VB6/VBA exposing default properties was supposed to "make it easier" for beginners.