Integer in VB.net turns into String in VBA

Question

I have a list of objects that are "keys" into a table - the keys are simply the items in the first column. They can be Integers or Strings, depending on what DB table we read it from. Since we use them a lot, we cache that column in an ArrayList called "Keys".

We wrote cover methods to return Row, one that uses strings and the other integers. If you call the integer version it simply returns that row by index. If you call the string, it looks down Keys for a match, and uses that index to pull out the row.

Ok, so now I pass Keys to Excel, pull out one of them in a loop, and ask it what it is...

TypeName(DB.Keys(i))

And the object returns...

Long

Great, the keys must have been integers! So now I'll try to get the row for that key, by calling the accessor method, Row...

DB.Row(DB.Keys(i))

And it calls into the version that takes a String.

Whoa!

Can anyone think of a reason that VBA calling back into our VB.net DLL ends up calling the wrong accessor?

ADDED CODE: I can't figure out how to put the code in as a reply, so I'm editing this. Here is the code in the VB.net class:

Public Function Row(ByVal K As String) As DataRow
    Dim R As DtaRow = DB.Tables(0).Rows(K)
        Return New DataRow(R)
End Function
Public Function Accounts(ByVal K As Integer) As DataRow
    Dim R As DtaRow = DB.Tables(0).Rows(K)
    Return New DataRow(R)
End Function

If you're wondering, there's two versions of Rows, which take strings or ints.

This code works perfectly from VB.net. You can ask for a row by key string or by the row number, that invokes the proper Row, which calls the proper Rows, and out comes the proper answer.

But in VBA, it always calls the method with the string input. If I rename it to RowIHATEYOUALL then I can call the Integer version just fine. But when there are two of them, differing only in signature, no such luck.

And the A and i (see comments) was my typo.

Is there any way you could provide a bit more code so we can try to reproduce it? — djv, Jan 29 '14 at 20:13
Yes clerification would help. I see there one says DB.Keys(A) and the other DB.Keys(i). Are these calls both in different methods? "where 1 has the index named "A" and the other "i" or is that a mistake? — user1759942, Jan 29 '14 at 20:22
DB.Tables(0).Rows(K) - the K has to be an integer as it is an ordinal into the row set. Look to .Select() on the DataTable to find the row(s). — rheitzman, Jan 29 '14 at 20:52

score 1 · Accepted Answer · edited May 23 '17 at 10:25

The interop layer does not support overloaded methods. Whenever you call Row, the first declared method with that name is used. The .NET overload resolution algorithm does not apply here.

Other overloads are exposed to VBA as Row_2, Row_3, etc. Thus, the following code should do what you expect:

DB.Row_2(DB.Keys(i))

This implicit dependency on the order of declaration has a high potential for error. Thus, I would suggest to either

give the methods unique names if they are called from VBA,

or, if you want to retain the "nice" overloaded version for .NET, add compatibility methods for VBA:

<Obsolete("Compatibility method for VBA, use Row instead.")>
Public Function RowByKeyVBA(ByVal K As String) As DataRow
    Return Row(K)
End Function

<Obsolete("Compatibility method for VBA, use Row instead.")>
Public Function RowByNumberVBA(ByVal K As Integer) As DataRow
    Return Row(K)
End Function

Further information can be found in the following question:

COM->.NET - can't access overloaded method

Fascinating! I never would have expected this! Well I'm happy to know anyway, thanks! — Maury Markowitz, Jan 29 '14 at 21:00

score 0 · Answer 2 · answered Feb 03 '14 at 15:36

Following Heinzi's notes (above) I fixed this by making three method signatures for each call, one takes an Object and then attempts to figure out what it is, the others take the String and Integers. Within VB/C#/etc the proper String or Integer methods get called as expected, from VBA the Object version is called, as Heinzi noted

This causes the very minor issue that a user may have a "number like value" that is actually a String. For instance, the array keys might be "User3232" or "3232", both of which are String objects in the table. So you have to be careful, simply asking if the Object can be converted to an Int32 is not enough. This is unlikely to be something that effects most users.

Integer in VB.net turns into String in VBA

2 Answers2