CallByName Unexpected results if argument is Variant array set to cell range

Question

I have been using CallByName in a particular application and getting results which I cannot explain. They are reproducible on a simple test with the following conditions

• The property of the class object is of Type Double
• The value being added (Let) comes from a variant array that has been set to a multiple cell range

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

Worksheet Cell A1 contains 5.8

A2 contains 1.3 and the rest of the cells in column A are blank.

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 the 2nd debug.print line shows that the value stored in class1.MyData is 312080296 and not 5.8.

Answer 1

Same thing here. Getting 145842640. If it helps you don't have to use CallByName. Using the below line worked for me to set it correctly to 5.8.

class1.MyData = W(1, 1)

Might also help to declare pMyData a double, and also in Let/Get statments. Then you'll get an error when attempting to assign, like the first V(1,1), which will force you to explicitly declare the conversion, which appears to be a good thing (or necessary even) in this situation.

Couldn't find a good quick reason why it is doing that though, or what the conversion is actually doing. Hopefully someone knows, I'm curious now.

EDIT - It would appear that 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 appear converting via CDbl dereferences the pointer, getting the value, which is why it works with the explicit conversion. (Or so I think anyway.)

Try adding this function:

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

Then do a debug.pring for W(1,1), and a debug.print for VarPtr(W(1,1)). I found that the myData and the VarPtr value for W(1,1) were one and the same. I assume this is part of the behavior of the CallByName function, as far as passing the address, not the value, but I don't have time to research further. Hope that helps.