CallByName Unexpected results if the argument is a Variant array set for a range of cells

Question

I use CallByName in a specific application and get results that I cannot explain. They are reproduced in a simple test with the following conditions.

• The class object property is of type Double Double
• The added value (Let) comes from a variant of the array that has been set to a range of several cells

I would appreciate an explanation of this behavior. The following code should reproduce it (at least in Excel 2007 / Windows 7)

A1 worksheet cell contains 5.8

A2 contains 1.3 , and the remaining cells in column A are empty.

Class module (class1)

Private pMyData Public Property Get MyData() MyData = pMyData End Property Public Property Let MyData(Value) pMyData = Value End Property 

Regular module

 Option Explicit Sub foo() Dim class1 As class1 Dim V(1 To 2, 1 To 1) As Variant V(1, 1) = [a1] V(2, 1) = [a2] Set class1 = New class1 CallByName class1, "MyData", VbLet, V(1, 1) Debug.Print V(1, 1), class1.MyData ' <-- 5.8 5.8 Dim W As Variant W = Range("A1:A2") Set class1 = New class1 CallByName class1, "MyData", VbLet, W(1, 1) Debug.Print W(1, 1), class1.MyData ' <-- 5.8 312080296 CallByName class1, "MyData", VbLet, CDbl(W(1, 1)) Debug.Print W(1, 1), class1.MyData ' <-- 5.8 5.8 End Sub 

Note that the second line of debug.print shows that the value stored in class1.MyData is 312080296, not 5.8.

Answer 1

The same thing here. Getting 145842640. If this helps you not to use CallByName. Using the line below helped me set the value to 5.8 correctly.

 class1.MyData = W(1, 1) 

May also help declare pMyData double, as well as in Let / Get statuses. You will then receive an error message when you try to assign, for example, the first V (1,1), which will force you to explicitly declare the conversion, which seems good (or necessary) in this situation.

Could not find a good quick reason why this is done, or what the conversion is doing. I hope someone knows, I'm wondering right now.

EDIT . It looks like CallByName is actually passing the address of W (1,1) to the Let statement. (Passing the value of the pointer, in other words.) It would seem that conversion through CDbl leads to dereferencing the pointer, getting the value, so it works with an explicit conversion. (Or so I still think.)

Try adding this feature:

 Public Declare PtrSafe Function VarPtrArray Lib "VBE7" Alias _ "VarPtr" (Var() As Any) As LongPtr 

Then do debug.pring for W (1,1) and debug.print for VarPtr (W (1,1)). I found that the value of myData and VarPtr for W (1,1) was the same. I assume that this is part of the behavior of the CallByName function, because it is an address transfer, not a value, but I do not have time for further investigation. Hope this helps.

Answer 2

I correctly understood this line,

CallByName class1, "MyData", VbLet, CVar(W(1, 1))

The only thing I can think of is that CallByName expects Args() as Variant , and W(1,1) cannot be implicitly discarded .. (for some reason)