Inconsistent behavior when assigning array to a property let

Question

Consider following code:

Option Explicit

Public Property Let Dummy(v() As Integer)

End Property

Public Sub LetDummy(v() As Integer)

End Sub

Sub Foo()
    ReDim i(0 To 2) As Integer
    i(0) = 3
    i(1) = 45
    i(2) = 10

    Bar i ' internal error 51
End Sub

Sub Foo2()
    ReDim i(0 To 2) As Integer
    i(0) = 3
    i(1) = 45
    i(2) = 10

    Bar2 i ' no error
End Sub

Sub Foo3()
    ReDim i(0 To 2) As Integer
    i(0) = 3
    i(1) = 45
    i(2) = 10

    Dummy = i ' no error
End Sub

Sub Bar(j() As Integer)
    Dummy = j
End Sub

Sub Bar2(j() As Integer)
    LetDummy j
End Sub

When I run macro 'Foo' i get message 'internal error 51' but 'Foo2' and 'Foo3' runs fine. What's the reason for this behavior? Or is it simply a bug in VBA? How to fix this error?

Background: In my application I want to assign array provided as function argument to property of type array.

Answer 1

It looks like a bug to me since Error 51 says to contact MS if you get it.

If I create a local variable, assign it, then pass it along it seems to work.

Very hacky.

Option Explicit

Private ary() As Integer

Public Property Get Dummy() As Integer()
    Dummy = ary
End Property

Public Property Let Dummy(v() As Integer)
    Debug.Print "In Dummy" & UBound(v)
    ary = v

End Property

Public Sub LetDummy(v() As Integer)
    Debug.Print "In LetDummy" & UBound(v)
End Sub

Sub Foo()
    ReDim i(0 To 2) As Integer
    i(0) = 3
    i(1) = 45
    i(2) = 10

    Call Bar(i)  ' internal error 51
End Sub

Sub Foo2()
    ReDim i(0 To 2) As Integer
    i(0) = 3
    i(1) = 45
    i(2) = 10

    Bar2 i ' no error
End Sub

Sub Foo3()
    ReDim i(0 To 2) As Integer
    i(0) = 3
    i(1) = 45
    i(2) = 10

    Dummy = i ' no error
End Sub

Sub Bar(j() As Integer)
    Dim i() As Integer
    i = j

    Dummy = i

    Dim x As Integer
    Dim myary() As Integer
    myary = Dummy
    For x = 0 To 2
        Debug.Print myary(x)
    Next
End Sub

Sub Bar2(j() As Integer)
    LetDummy j
End Sub

Answer 2

That's quite an interesting bug. While trying to reproduce it I came up with this:

Public Property Let Foo(ByRef values() As Long)
End Property

Public Sub Test()
    Dim values(0 To 1) As Long
    values(0) = 1
    values(1) = 2
    IllegalByRefArray values
End Sub

Private Sub IllegalByRefArray(ByRef values() As Long)
    Stop 'break here or crash Excel
    Foo = values
End Sub

Instead of run-time error 51, I got a hard crash. "Excel has stopped working", and poof, gone. Running 64-bit Excel 2010.

Running Excel with a Visual Studio debugger attached, I could see how the EXCEL.EXE process completely blows up on the Foo = values assignment:

Unhandled exception at 0x00007FFF834E4B90 (ntdll.dll) in EXCEL.EXE: 0xC0000028: An invalid or unaligned stack was encountered during an unwind operation

So there's definitely something fishy going on. Thing is, a property that exposes an array is kind of a weird thing in the first place: normally you would encapsulate the array and expose an indexed property, i.e. something like this:

Private internal() As Long 'todo initialize

Public Property Let Foo(ByVal index As Long, ByVal value As Long)
    internal(index) = value
End Property

Public Property Get Foo(ByVal index As Long) As Long
    Foo = internal(index)
End Property

Still, the language doesn't explicitly forbid exposing an array as a property... or does it?

This is a compile-time error, namely, can't assign to array:

Public Property Let Foo(ByRef values() As Long)
End Property

Public Sub Test()
    Dim values(0 To 1) As Long
    values(0) = 1
    values(1) = 2
    Foo = values ' compile error / can't assign to array
End Sub

So passing the array to a procedure that then relays it to the Property Let seems like a hack to leverage the VBE's rather poor static code analysis in order to get around a compile error that's probably there for a reason.

We know we can't pass an array ByVal in VBA. Trying to do so isn't even a compile error, it's a syntax error and the VBE is quite obviously unhappy with it:

This points back to Rory's comment about properties implicitly passing parameters ByVal. Here's some proof:

Public Property Let Foo(ByRef values As Variant)
    Debug.Print "Foo: " & VarPtr(values)
End Property

Public Sub Test()
    Dim values(0 To 1) As Long
    values(0) = 1
    values(1) = 2

    Dim v As Variant
    v = values

    Debug.Print "Test: " & VarPtr(v)
    IllegalByRefArray v
End Sub

Private Sub IllegalByRefArray(ByRef values As Variant)
    Debug.Print "IllegalByRefArray: " & VarPtr(values)
    Foo = values
End Sub

Running the Test procedure prints this (or similar) output:

Test: 196542488
IllegalByRefArray: 196542488
Foo: 196542352

Notice the variable pointer is the same in Test and IllegalByRefArray, but different in Foo, regardless of the ByRef modifier. If you change the modifier in IllegalByRefArray to ByVal, you'll get 3 different values instead:

Test: 196542488
IllegalByRefArray: 196542384
Foo: 196542336

From the accepted answer:

If I create a local variable, assign it, then pass it along it seems to work.

Of course it does; you're now passing a different pointer than the one you received, and VBA tries by several means described above, to discourage you from passing a pointer to an array into a Property Let mutator: VBA is trying to tell you in so many ways, that what you're doing isn't something you should be doing.

Do things the right way, keep the array encapsulated, and expose an indexed property instead of allowing the array itself to be overwritten.

Or make it a public field if you don't care for encapsulation: that property serves literally no purpose.

Public Values() As Long

There, assign at will.

If you must have a Property Let mutator for that array, the clean solution is to pass the array as a Variant; yes, this means you need to adjust the rest of your code.

---

TL;DR:

A ByRef modifier on a Property Let mutator makes no sense: it's a blatant lie (it's not passed by reference!) and makes your code very confusing. Don't stick a ByRef modifier on a Property Let value parameter just to satisfy the language syntax which otherwise refuses to pass an array parameter - you're twisting VBA's arm, jeopardizing your users' unsaved work, and making your code tell lies.

Pass a Variant instead, or properly encapsulate your array through an indexed property. Anything else is just a dirty stinking hack.

Answer 3

use Variant

Public Property Let Dummy(v As Variant)

End Property

'rest is the same