CODEWITHSUNDEEP
arraysexcelvbaclassbyref
Fred Cailloux
Fred Cailloux

Reputation: 195

Why VBA Class cannot pass ByRef Array()?

I am using EXCEL 2007 I built this code that takes data from EXCEL cells and fill an Array() called MesPoints() as clsPoint. The code also use an Array called MaCurve as clsCurve. This Class internally define a Variant Array called PointsXY. I want to avoid copying all data into the Class so I am trying to pass the whole Array ByRef. In a Module I am filling the data into MesPoints and then attempt to pass MesPoints into the Class into PointsXY as Variant. By default this should be done ByRef.

The code below show how the Property Let AllDots is called to pass the whole Array as a Variant clsPoint Class.

Private MaCurve As New clsCurve 
Private MesPoints() As New clsPoint
Dim MyRange As Range
Dim MySheet As Worksheet
Dim StartRow As Long, EndRow As Long, StartCol As Long

Sub FillClasses() ' This code in into a separate module Main
Dim Pcount As Long, Scount As Long, Ccount As Long, Dcount As Long
Dim i As Integer, j As Integer, k As Integer, m As Long, pr As Long, pc As Long
Dim Y As Double, X As Double

StartRow = 4: EndRow = 9: StartCol = 2
Set MySheet = Application.Worksheets("Data1")
Set MyRange = Range(MySheet.Cells(StartRow, StartCol), MySheet.Cells(EndRow, StartCol + 1)) ' Défini le range des données
Pcount = MyRange.Rows.Count: Scount = Pcount - 1: Ccount = Scount - 1: Dcount = Ccount - 1

ReDim MesPoints(Pcount)
    For i = 1 To Pcount ' Population des valeurs X et Y des points
        j = 1: k = 2
        MesPoints(i).X = MyRange.Cells(i, j)
        MesPoints(i).Y = MyRange.Cells(i, k)
    Next i

Let MaCurve.AllDots = MesPoints()

'..._____________________________________________________________________________________________

'  Class MaCurve
Dim PointsXY As Variant
Public Property Let AllDots(ByRef AP() As clsPoint)
Let PointsXY = AP()
DataCount = UBound(PointsXY())
Pcount = DataCount + 1
ReDim Preserve PointsXY(Pcount)
Scount = DataCount: Ccount = Scount - 1: Dcount = Ccount - 1
    For i = 1 To Scount ' Population des droites
        Set Segments(i).P1 = PointsXY(i) ' The code will choke here
    Next i


'...

End Property

The code will not work. Instead, as soon as one of the data in PointsXY is called for computation the processor exit the Class code with no error, no warning, no nothing, return the focus in the Main code and continue.

I'm I calling the Property Let properly ? Where is the mistake, as this is a strange VBA behavior ?

Upvotes: 1

Views: 127

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71247

The value parameter of Property Let (or Property Set, for that matter) is always passed by value, regardless of the specifier. This isn't unique to arrays at all, but you can work around it by passing a Variant pointer instead of an explicit, typed array:

Public Property Let AllDots(ByVal Values As Variant)

I lost the link, but here's the relevant language spec section:

§ If the <value-param> of a <property-LHS-declaration> does not have a <parameter-mechanism> element or has a <parameter-mechanism> consisting of the keyword ByRef, it has the same meaning as if it instead had a <parameter-mechanism> element consisting of the keyword ByVal.

See discussions here and here (GitHub links to Rubberduck repository).

Upvotes: 3

Related Questions