CODEWITHSUNDEEP
excelvba
Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

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

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

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.

Upvotes: 7

Views: 649

Answers (2)

user3696061
user3696061

Reputation: 135

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.

Upvotes: 3

Rusan Kax
Rusan Kax

Reputation: 1894

I got this line to work correctly,

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

Only thing I can think of is CallByName expects Args() as a Variant, and W(1,1) cannot be implicitly cast..(for some reason)

Upvotes: 0

Related Questions