CODEWITHSUNDEEP
vbaexcelruntime-error
EricHH11
EricHH11

Reputation: 23

VBA Error Code Object Required --- Have Triple checked but still not working

The commented lines below continue to return to me the error Run Time Error'424': Object Required. I am working on this as part of a self learn class, and I have tripled checked to make sure I am entering the code properly and am still returning this error. I tried looking at other examples of this error on here, but none were helpful to this instance because I do not know the code well enough yet.

Thank you!

Sub FirstArray()

Dim Fruit(2) As String

Fruit(0) = "Apple"
Fruit(1) = "Banana"
Fruit(2) = "Cherry"
Range("A1").Text = "First Fruit: " & Fruit(1)
' ^ RUN TIME ERROR 424

Dim Veg(1 To 3) As String

Veg(1) = "Artichoke"
Veg(2) = "Broccoli"
Veg(3) = "Cabbage"
Range("B1").Text = "First Veg:" & Veg(1)
' ^ RUN TIME ERROR 424

Dim Flower() As String
ReDim Flower(1 To 3)

Flower(1) = "Azalea"
Flower(2) = "Buttercup"
Flower(3) = "Crocus"
Range("C1").Text = "Final Flower:" & Flower(3)
' ^ RUN TIME ERROR 424





End Sub

Upvotes: 2

Views: 226

Answers (3)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

Changing .Text for .Value will fix it, but in case you're wondering how "Object required" even remotely makes sense as an error message, here's why.

You know the syntax for implicit value assignment:

foo.Bar = 42

The explicit value assignment syntax is still supported, but obsolete/deprecated:

Let foo.Bar = 42

This assignment calls the Property Let accessor of the Bar property of the foo object, which could look something like this:

Public Property Let Bar(ByVal value As Long)
    internalBar = value
End Property

Public Property Get Bar() as Long
    Bar = internalBar
End Property

In the case of Range.Text, the property might look something like this:

Public Property Get Text() As String
    Text = GetStringRepresentationOfValue
End Property

Notice there's no Property Let accessor, so this:

someRange.Text = "foo"

Isn't legal, because the Text property doesn't expose a Property Let accessor.

So what's the deal with object required? Getting to it.

But first you need to know what a default member is. You see every class module can define a "default member". For collections that member is usually the Item property, by convention.

This means whether you do this:

foo = myCollection.Item(12)

Or that:

foo = myCollection(12)

You get the exact same foo, exactly the same way - the two are exactly the same.. except the latter implicitly calls the default member, and the former explicitly does so.

A class' default member is determined by a hidden member attribute that you can only see if you export the class module and open it in a text editor:

Public Property Get Item(ByVal Index As Long) As Variant
Attribute Foo.VB_UserMemId = 0
    Item = internalCollection(Index)
End Property

Only 1 member in a class can be the default.

So what the error message is saying, is that when it sees this:

foo.Bar = 42

And knows that Bar is a read-only property that only exposes a Property Get accessor, then the only way for this code to be legal, is if Bar returns an object that has a default member that can be assigned to take that value.

And since Range.Text returns a String and not an Object, VBA complains that an object is required.

Upvotes: 3

jcarroll
jcarroll

Reputation: 577

You just need to change .Text to .Value

Upvotes: 4

QHarr
QHarr

Reputation: 84465

Perhaps 

Range("B1") = "First Veg:" & Veg(1)

Same for 

Range("C1") = "Final Flower:" & Flower(3)

Upvotes: 0

Related Questions