VBA Excel: Compile Error: Object required?

Question

I get a VBA Excel 'Compiler Error: Object required'-Error in the marked line. I do not understand the reason.

BTW: Wish Excel would support a .Net language without wrapper needs.

Option Explicit

Public Type Inherit
    ReqId As Integer
    Parent As Integer
    Depth As Integer
    Path As String
End Type

Sub test()
    Dim MyStructure() As Inherit
    ReDim MyStructure(1 To 1000)

    MyStructure(1).ReqId = 1

    Dim Data, refData As Inherit
    Set Data = MyStructure(1)  ' <---! 
    Beep

End Sub

Alex K. · Accepted Answer · 2011-10-11 11:04:07Z

20

Set is for the assignment of objects, a user defined type is treated like a regular variable so use = to assign.

Also (confusingly); Dim Data, refData As Inherit only declares refData of type Inherit to declare them both on one line you must; Dim Data As Inherit, refData As Inherit

answered Oct 11, 2011 at 11:04

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Doc Brown · Accepted Answer · 2011-10-11 11:17:46Z

3

Dim Data, refData As Inherit

declares Data as Variant, only refData as Inherit.

Dim Data As Inherit, refData As Inherit

does what you want. The VBA syntax is not "common sense" here, I have seen this error dozens of times. EDIT: of course, you will have to leave out Set in the assignment, since Inherit is a user defined type.

If you are looking for a free and easy to use .NET integration for Excel, look at Excel-DNA:

http://exceldna.codeplex.com/

edited Oct 11, 2011 at 11:17

answered Oct 11, 2011 at 11:03

Doc Brown

20.2k7 gold badges56 silver badges95 bronze badges

Collectives™ on Stack Overflow

VBA Excel: Compile Error: Object required?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related