0

Please would someone who understands VBA Arrays (Access 2003) help me with the following code.

The idea is that ClassA holds a dynamic array of ClassB instances. The dynamic array starts empty. As callers call ClassA.NewB() then a new instance of ClassB is created, added to the array, and returned to the caller. The problem is that I can't return the new instance of ClassB to the caller, but get "Runtime error 91: Object variable or With block variable not set"

Also, a little WTF occurs where UBound() fails but wrapping the exact same call in another function works!?!? (Hence MyUbound() )

I'm from a C++ background and this VBA stuff is all a bit strange to me!

Thanks for any help!

Main code:

Dim a As clsClassA
Dim b As clsClassB

Set a = New clsClassA
a.Init
Set b = a.NewB(0)

clsClassA:

Option Compare Database

Private a() As clsClassB

Public Sub Init()
    Erase a
End Sub

Public Function NewB(i As Integer) As Variant
    'If (UBound(a, 1) < i) Then ' FAILS: Runtime error 9: Subscript out of range
    If (MyUBound(a) < i) Then ' WORKS: Returns -1
        ReDim Preserve a(0 To i)
    End If
    NewB = a(i) ' FAILS: Runtime error 91: Object variable or With block variable not set
End Function

Private Function MyUBound(a As Variant) As Long
    MyUBound = UBound(a, 1)
End Function

clsClassB:

Option Compare Database
' This is just a stub class for demonstration purposes
Public data As Integer
Improve this question
0

4 Answers 4

Reset to default
3

Your approach stores a collection of ClassB instances in an array. For each instance you add, you must first ReDim the array. ReDim is an expensive operation, and will become even more expensive as the number of array members grows. That wouldn't be much of an issue if the array only ever held a single ClassB instance. OTOH, if you don't intend more than one ClassB instance, what is the point of storing it in an array?

It makes more sense to me to store the collection of instances in a VBA Collection. Collections are fast for this, and aren't subject to the dramatic slow downs you will encounter with an array as the number of items grows.

Here is a Collection approach for clsClassA. 

Option Compare Database
Option Explicit
Private mcolA As Collection

Private Sub Class_Initialize()
    Set mcolA = New Collection
End Sub

Private Sub Class_Terminate()
    Set mcolA = Nothing
End Sub

Public Function NewB(ByVal i As Integer) As Object
    Dim objB As clsClassB
    If i > mcolA.Count Then
        Set objB = New clsClassB
        mcolA.Add objB
    Else
        Set objB = Nothing
    End If
    Set NewB = objB
    Set objB = Nothing
End Function

The only change I made to clsClassB was to add Option Explicit.

This procedure uses the class.

Public Sub test_ClassA_NewB()
    Dim a As clsClassA
    Dim b As clsClassB

    Set a = New clsClassA
    Set b = a.NewB(1) '' Collections are one-based instead of zero-based
    Debug.Print TypeName(b) ' prints clsClassB
    Debug.Print b.data '' prints 0
    b.data = 27
    Debug.Print b.data '' prints 27
    Set b = Nothing
    Set a = Nothing
End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I'm a huge fan of VBA collections, myself. +1 for the recommendation here.
2

Try this:

Public Function NewB(i As Integer) As Variant
    'If (UBound(a, 1) < i) Then ' FAILS: Runtime error 9: Subscript out of range
    If (MyUBound(a) < i) Then ' WORKS: Returns -1
        ReDim Preserve a(0 To i)
    End If

    Set a(i) = New clsClassB

    Set NewB = a(i)
End Function

You need to set a(i) to a new instance of the class (or it will simply be null), you also need to use Set as you're working with an object...

I'd perhaps also suggest changing the return type of NewB to clsClassB rather than Variant.

You could also do

Public Sub Init()
    ReDim a(0 To 0)
    Set a(0) = New Class2
End Sub

to remove the need for the special UBound function.

Improve this answer

2 Comments

Marvellous! Thanks very much!
@Alex: If this has solved your problem, please consider accepting as the answer to your question. This also helps users in the future find answers and stops others coming here only to find it already answered!
1

The UBound function throws this error when you try to use it on an array with no dimension (which is your case since you did an Erase on the array). You should have an error handler in your function to treat this case.

Improve this answer

2 Comments

I'd assume that's to do with the way that it gets wrapped by Variant
Ok, sounds reasonable. Thanks. Anyone got any idea about the Error 91?
0

I use a special function to check if the array is empty, but you can just use parts of it for error handling.

Public Function IsArrayEmpty(ByRef vArray As Variant) As Boolean

    Dim i As Long

    On Error Resume Next
    IsArrayEmpty = False
    i = UBound(vArray) > 0
    If Err.Number > 0 Then IsArrayEmpty = True
    On Error GoTo 0

End Function

Also, if you still want to do an array then you could 

redim preserve MyArray(lbound(MyArray) to ubound(MyArray)*2)

which will lesson the amount of times it redimensions, you would need a counter to redimension it at the very end.

Also, Dictionaries are supposed to be really fast (and more versatile than collections), they're like collections and you need to add a reference to Microsoft Scripting Runtime if you want to do dictionaries.

Improve this answer

7 Comments

You can use dictionaries without a reference: Set MyDictionary = CreateObject("Scripting.Dictionary")
How is the versatility of a dictionary a greater benefit than a collection WRT the OP's question? (Not challenging your assertion it can be useful for other problems.)
I brought up dictionaries as an alternative to collections because you brought up collections in your post. I like using dictionaries just because you have the "exists" method and can cycle through them using ".items(index number)" and ".keys(index number)" (which you might be able to do with collections, I just stopped using them once I found out about dictionaries, so I don't have a ton of experience with them). I've also read that dictionaries are much faster than collections, so, for large data sets dictionary can get the job done faster. Is it possible to get e-mail responses from these?
@Jon49 Coming from a Python background, I was pleased to find dictionaries for VBA. However, I was curious what benefits they would provide in this case ... I didn't see any need for .exists and so forth. As for dictionary faster than collection, where did you get that information? I would appreciate a link if you have one. Both seem pretty darn fast to me! :-) I think maybe you can get email notifications for questions you ask, but other than that, none (AFAIK).
That's too bad about the e-mails, it may take a while for me to get back to people because of that! --- Here's the source for the dictionary being faster and some other benefits of dictionary. Also, I've seen it in other places but am not about to do a thorough search on the subject again. "Dictionaries are also considerably faster than Collections." -- cpearson.com/excel/CollectionsAndDictionaries.htm
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.