Inconsistent behavior when assigning an let property to an array

Question

Consider the 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 the macro "Foo", I get the message "internal error 51", but "Foo2" and "Foo3" are working fine. What is the reason for this behavior? Or is it just a bug in VBA? How to fix this error?

Background: in my application, I want to assign an array, provided as an argument to a function, to a type array property.

Answer 1

It seems like an error, since Error 51 says to contact MS if you receive it.

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

Very khaki.

 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

use Variant

 Public Property Let Dummy(v As Variant) End Property 'rest is the same 

Answer 3

This is a pretty interesting mistake. Trying to reproduce it, I came up with the following:

 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 a run-time error 51, I was hit hard. "Excel stopped working," and the puff disappeared. Run 64-bit Excel 2010.

Starting Excel with the Visual Studio plug-in debugger, I saw how the EXCEL.EXE process exploded completely when assigning Foo = values :

Unhandled exception in 0x00007FFF834E4B90 (ntdll.dll) in EXCEL.EXE: 0xC0000028: An invalid or unstacked stack was detected during the unwind operation

So definitely something suspicious. The thing is, the property that the array exposes looks strange in the first place: usually you have to encapsulate the array and expose the 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 

However, the language does not explicitly prohibit exposing the array as a property ... or is it?

This is a compile-time error, namely it cannot assign an 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 

Thus, passing an array to a procedure, which then passes it to Property Let , seems to be a hack to use a rather weak static analysis of VBE code to circumvent a compilation error, which probably exists for some reason.

We know that we cannot pass a ByVal array to VBA. Trying to do this is not even a compilation error, it is a syntax error, and VBE is clearly clearly unhappy with this:

This indicates a Rory comment about properties that implicitly pass ByVal parameters. Here is some evidence:

 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 

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

 Test: 196542488 IllegalByRefArray: 196542384 Foo: 196542336 

From the accepted answer:

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

Of course it is; now you pass a different pointer than the one you received, and VBA tries in several ways, described above, to prevent the pointer from being passed to an array in the Property Let mutator: VBA tries to tell you in different ways that you are not doing what you should do .

Do it right, keep the array encapsulated, and set the indexed property instead of letting the array itself be overwritten.

Or make it a public field if you don’t need encapsulation: this property literally has no purpose.

 Public Values() As Long 

There, appoint as desired.

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

---

TL DR:

But the ByRef modifier of the Property Let mutant does not make sense : this is a blatant lie (it is not passed by reference!) And makes your code very confusing. Do not attach the ByRef to the Property Let parameter value only in order to satisfy the language syntax, which otherwise refuses to pass the array parameter - you twist the VBA arm, compromising the unsaved work of your users and making your code false.

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