Why can't I declare my Class Object as such?

user_16606189 0
Viewed 133

I am currently creating a Class Object for a VBA file, its objective is to act as a range dictionary that can be passed single cells. If this cell is contained in one of the ranges, it returns the value associated to the corresponding range key. The class name is "rangeDic".

It is in the making so its functionalities are not implemented yet. Here's the code:

Private zone() As String
Private bounds() As String
Private link As Dictionary
Const ContextId = 33

'Init zone
Private Sub Class_Initialize()

    Set link = New Dictionary
    ReDim zone(0)
    ReDim bounds(0)
    
End Sub

'properties
Property Get linkDico() As Dictionary
    Set linkDico = link
End Property

Property Set linkDico(d As Dictionary)
    Set link = d
End Property

Property Get pZone() As String()
    pZone = zone
End Property

Property Let pZone(a() As String)
    Let zone = a
End Property

'methods
Public Sub findBounds()

    Dim elmt As String
    Dim i As Integer
    Dim temp() As String
    
    i = 1
    
    For Each elmt In zone
        ReDim Preserve bounds(i)
        temp = Split(elmt, ":")
        bounds(i - 1) = temp(0)
        bounds(i) = temp(1)
        i = i + 2
    Next elmt

End Sub

I was trying to instanciate it in a test sub in order to debug mid conception. Here's the code:

Sub test()

    Dim rd As rangeDic
    Dim ran() As String
    Dim tabs() As Variant
    Dim i As Integer
    
    i = 1
    
    With ThisWorkbook.Worksheets("DataRanges")
        While .Cells(i, 1).Value <> none
            ReDim Preserve ran(i - 1)
            ReDim Preserve tabs(i - 1)
            ran(i - 1) = .Cells(i, 1).Value
            tabs(i - 1) = .Cells(i, 3).Value
            i = i + 1
        Wend
    End With
    
    Set rd = createRangeDic(ran, tabs)

End Sub

Public Function createRangeDic(zones() As String, vals() As Variant) As rangeDic

    Dim obje As Object
    Dim zonesL As Integer
    Dim valsL As Integer
    Dim i As Integer
    
    zonesL = UBound(zones) - LBound(zones)
    valsL = UBound(vals) - LBound(vals)
    
    If zonesL <> valsL Then
        Err.Raise vbObjectError + 5, "", "The key and value arrays are not the same length.", "", ContextId
    End If
    
    Set obje = New rangeDic

    obje.pZone = zones()
    
    For i = 0 To 5
        obje.linkDico.add zones(i), vals(i)
    Next i
    
    Set createRangeDic = obje
End Function

Take a look at line 2 of Public Function createRangeDic. I have to declare my object as "Object", if I try declaring it as "rangeDic", Excel crashes at line obje.pZone = zones(). Upon looking in the Windows Event Log, I can see a "Error 1000" type of application unknown error resulting in the crash, with "VB7.DLL" being the faulty package.

Why so ? Am I doing something wrong ?

Thanks for your help

Edit: I work under Excel 2016

vba excel oop
1 Answers

It looks like this is a bug. My Excel does not crash but I get an "Internal Error".

Let's clarify a few things first, since you're coming from a Java background.

Arrays can only be passed by reference

In VBA an array can only be passed by reference to another method (unless you wrap it in a Variant). So, this declaration:

Property Let pZone(a() As String) 'Implicit declaration

is the equivalent of this:

Property Let pZone(ByRef a() As String) 'Explicit declaration

and of course, this:

Public Function createRangeDic(zones() As String, vals() As Variant) As rangeDic

is the equivalent of this:

Public Function createRangeDic(ByRef zones() As String, ByRef vals() As Variant) As rangeDic

If you try to declare a method parameter like this: ByVal a() As String you will simply get a compile error.

Arrays are copied when assigned

Assuming two arrays called a and b, when doing something like a = b a copy of the b array is assigned to a. Let's test this. In a standard module drop this code:

Option Explicit

Sub ArrCopy()
    Dim a() As String
    Dim b() As String
    
    ReDim b(0 To 0)
    b(0) = 1
    
    a = b
    a(0) = 2
    
    Debug.Print "a(0) = " & a(0)
    Debug.Print "b(0) = " & b(0)
End Sub

After running ArrCopy my immediate window looks like this:
enter image description here

As shown, the contents of array b are not affected when changing array a.

A property Let always receives it's parameters ByVal regardless of whether you specify ByRef

Let's test this. Create a class called Class1 and add this code:

Option Explicit

Public Property Let SArray(ByRef arr() As String)
    arr(0) = 1
End Property

Public Function SArray2(ByRef arr() As String)
    arr(0) = 2
End Function

Now create a standard module and add this code:

Option Explicit

Sub Test()
    Dim c As New Class1
    Dim arr() As String: ReDim arr(0 To 0)
    
    arr(0) = 0
    Debug.Print arr(0) & " - value before passing to Let Property"
    c.SArray = arr
    Debug.Print arr(0) & " - value after passing to Let Property"
    
    arr(0) = 1
    Debug.Print arr(0) & " - value before passing to Function"
    c.SArray2 arr
    Debug.Print arr(0) & " - value after passing to Function"
End Sub

After running Test, my immediate window looks like this:
enter image description here

So, this simple test proves that the Property Let does a copy of the array even though arrays can only be passed ByRef.

The bug

Your original ran variable (Sub test) is passed ByRef to createRangeDic under a new name zones which is then passed ByRef again to pZone (the Let property). Under normal circumstances there should be no issue with passing an array ByRef as many times as you want but here it seems it is an issue because the Property Let is trying to make a copy.

Interestingly if we replace this (inside createRangeDic):

obje.pZone = zones()

with this:

Dim x() As String
x = zones
obje.pZone = x

the code runs with no issue even if obje is declared As rangeDic. This works because the x array is a copy of the zones array.

It looks that the Property Let cannot make a copy of an array that has been passed ByRef multiple times but it works perfectly fine if it was passed ByRef just once. Maybe because of the way stack frames are added in the call stack, there is a memory access issue but difficult to say. Regardless what the problem is, this seems to be a bug.

Unrelated to the question but I must add a few things:

  1. Using ReDim Preserve in a loop is a bad idea because each time a new memory is allocated for a new (larger) array and each element is copied from the old array to the new array. This is very slow. Instead use a Collection as @DanielDuĊĦek suggested in the comments or minimize the number of ReDim Preserve calls (for example if you know how many values you will have then just dimension the array once at the beginning).
  2. Reading a Range cell by cell is super slow. Read the whole Range into an array by using the Range.Value or Range.Value2 property (I prefer the latter). Both methods returns an array as long as the range has more than 1 cell.
  3. Never expose a private member object of a class if that object is responsible for the internal workings of the class. For example you should never expose the private collection inside a custom collection class because it breaks encapsulation. In your case the linkDico exposes the internal dictionary which can the be modified from outside the main class instance. Maybe it does not break anything in your particular example but just worth mentioning. On the other hand Property Get pZone() As String() is safe as this returns a copy of the internal array.
  4. Add Option Explicit to the top of all your modules/classes to make sure you enforce proper variable declaration. Your code failed to compile for me because none does not exist in VBA unless you have it somewhere else in your project. There were a few other issues that I found once I turned the option on.
user_84889130
Related