2

I currently have a VB.NET dll that returns a jagged array of double. This is the declaration:

Public Function CalcMatching(ByRef dataArray1 As Object, 
    ByRef dataLen1 As Integer, ByRef dataArray2 As Object, 
    ByRef dataLen2 As Integer, ByRef matchingType As String) As Double()()

It works well inside VB.NET, but when I insert it into a VBA project, I noticed that after the execution of the function, while retrieving the data, the "Type mismatch' exception is raised inside VBA.

I searched over the internet, but I could not find a declaration of a jagged array inside VBA. is that possible? If yes, how can I do it?

BenMorel
  • 34,448
  • 50
  • 182
  • 322
bbouzan
  • 31
  • 8
  • 1
    I have probably misunderstood your point, but perhaps dataArray1 as Variant? – Fionnuala Mar 01 '10 at 20:06
  • Is this the VB.Net declaration? – wqw Mar 01 '10 at 22:45
  • wqw, yes this is the .NET declaration. – bbouzan Mar 03 '10 at 12:16
  • Remou, Variant is not a type available at VB.NET. But it's not generating any problems, the issue is with the return type of the function, that is not accepted by VBA. – bbouzan Mar 03 '10 at 12:18
  • There is no VBA syntax for a strongly typed array of array of double (or anything). To get a jagged array in VBA you use an array of Variants, with elements of the array containing arrays that can be different lengths. (See this for the basics: http://stackoverflow.com/questions/3428811/pass-multidimensional-array-into-excel-udf-in-vba/3430854#3430854) My .NET is pretty rusty, but I suspect it's not possible to do what you want without gymnastics. You'll probably have to return something VBA *will* take, like a 2-D array, or a Collection (of arrays or Collections), and crunch it yourself. – jtolle Dec 07 '10 at 16:28

1 Answers1

1

Just a guess without seeing the calling VBA code, but I believe this is being caused by having ByRef arguments instead of ByVal. There is stronger type checking when using ByRef arguments which you can read about here.

Steve Danner
  • 21,818
  • 7
  • 41
  • 51
  • Steve, the parameters are not generating any problems, the issue is with the return type of the function, that is not accepted by VBA. – bbouzan Mar 03 '10 at 12:19