0

I have an object wrapper of some XLL functions in a VBA object, which object I am trying to translate to vb.net, as a COM dll add-in. The VBA declares the XLL functions like so:

Private Declare Function ExternalFunction Lib "C:\MyLibrary.xll" _
(ByVal x as double, ByVal y as double, ByVal Arr1D() as double, _
ByVal Arr2D() as double, ByVal ResultArray() as double, ByVal ErrorMssg as string) _
As Long

Of the first two array arguments, the first is one-dimensional and the second is two-dimensional. Because I will be sending a 2D array, vb.net forces me to change this part of the declaration from:

Arr2D() as double

to

Arr2D(,) as double

Should this work ok in the context of COM? I've spent a long time vetting every single input and I still get an error about the object disconnected from its clients. I could be missing something, but I"m worried that Function Declare and array rank, which were happy together in VBA, might not be happy now.

(Also, on a side note, curiously, I note that all the arguments are declared as ByVal--while the function most definitely will change ResultArray and ErrorMessage (those are the real returns...). How is it that the ByVal declaration even works in VBA--which it does. COuld my problem be that those should be ByRef, and vb.net is less forgiving?)

EDIT: Ok going back to the VBA. I see now that in the dozen or so functions that are wrapped, some have ResultArray sent ByRef and some send it unqualified. So Visual Studio stuck that ByVAl in there. That is probably my problem I guess. I'd still appreciate any reassurance if possible that the vb.net form of declaring rank of an array argument should be ok, if I could. (Curiously, to me, all the ErrorMessage strings are declared in VBA as ByVal--and they do work to return error messages from the XLL...)

Alex K
  • 8,269
  • 9
  • 39
  • 57
RomnieEE
  • 618
  • 3
  • 12
  • I changed ResultArray and ErrorMessage to ByRef. Still getting object disconnected. As an aside, the actual functions are a lot bigger than the example I gave. So it has been hard to pore over every input. The parameters are all value types (long, double, string (just for error message)) or arrays of double, 1D or 2D. An earlier version of these functions were in DLL form, and used no array declarations (send initial position and length for 1D arrays only). And those I had working in vb.net. The xll form, which now takes 1D and 2D array params, is, again, working as declared in VBA. – RomnieEE Jun 10 '14 at 00:55

1 Answers1

0

The comma was not the problem. The problem was the meaning of "Long" in Excel vs in VB.Net. I screwed up my question a bit by only showing Doubles and a String as parameter examples. I focused in on what I thought was the problem and obscured the real problem. Long is 32-bit in VBA. Long is 64 bit in .Net. For my Function Declare to be correct, I had to change Longs to Integers, going from VBA to VB.Net, in the actual declaration.

Because I think he always has an eye open for when he or his project Excel DNA is mentioned, I'm going to thank Govert here. It was his reply in this thread:

https://exceldna.codeplex.com/discussions/468416

that got me the answer. The...(what--funny? embarrassing?)--thing is that Govert saved me once before on this very same problem, a few years ago, when I asked him more directly. Thanks again, and again, Govert. (I didn't think to go to the DNA forum this time because other people made this project without it.)

RomnieEE
  • 618
  • 3
  • 12