Excel VBA Object Variable Not Set issue, possible improper Redim usage?

Question

I am running into an error with the below code. When I debug, I find the line causing my problem:

Options(a) = New Element

The error displayed is Object Variable or With Block Variable Not Set. With msg boxes I have found the value of a to be 0 at the time of the crash and the TotalItems to be 7. The Element object initialization is empty. I call the PopulateChildren method from another method within the same class. Am I using ReDim improperly? It seems like maybe it isn't increasing the size of my array... I have noticed examples of using it like this...

ReDim Preserve Options(0 to TotalItems)

...but it doesn't seem to do any different when I try it. Anyone have any idea what is going on?

Dim Options() As Element
Dim TotalItems As Integer
Dim Children(100) As Integer

Private Sub PopulateChildren()
    ReDim Preserve Options(TotalItems)
    For a = 0 To TotalItems - 1
        Options(a) = New Element
        Options(a).Populate (Children(a))
    Next a
End Sub

Thanks

Dmitry Pavliv · Accepted Answer · 2014-01-24 16:18:29Z

4

Since Element is Object, you should use Set:

Set Options(a) = New Element

answered Jan 24, 2014 at 16:18

Dmitry Pavliv

35.9k13 gold badges84 silver badges85 bronze badges

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

4 Comments

Floris Over a year ago

And if you are worried about whether your array indexing starts at 0 or 1, add Option Base 0 to the top of the module so you are sure that a=0 is a valid index.

simpLE MAn Over a year ago

@Floris, except when you use a Split it will always start from 0 whether you have Option Base 0 or Option Base 1. I know there is no Splitused here but just saying :)

enderland Over a year ago

I always specify my arrays lower/upper bounds explicitly in VBA. I've been burned way too many times. In fact, I've started even using lbound(arr) to ubound(arr) when iterating through an array, too

enderland Over a year ago

Bah, just found a bug from pre-doing-this-time because I did an 1 to ubound mistake. Good timing to post this comment, I guess..

Patrick Lepelletier · Accepted Answer · 2014-01-25 20:26:24Z

0

easy, put your dim options inside the SUB, or ...

public dim options()

sub ...
'your code
end sub

of course options doesn't exit for your sub like you wrote it ! so the error showing...

answered Jan 25, 2014 at 20:26

Patrick Lepelletier

1,6522 gold badges19 silver badges24 bronze badges

Collectives™ on Stack Overflow

Excel VBA Object Variable Not Set issue, possible improper Redim usage?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related