Basic Code Examples for passing Excel arrays into and out of Excel DNA

[Caspar]

Apologies for the basic nature of this enquiry.

Does anyone have some basic code examples of passing Excel Arrays into
VB.NET and returning them through ExcelDNA? I can see the excellent
array handling examples in C# in the MoreSamples.dna file, but
couldn't find any equivalents in VB.NET.

I'm trying to pass in column vectors and use VB.NET to query the size
of the input column vector, but the various methods I've tried to call
on Object Types which would correspond to
the .Columns.Count / .Rows.Count methods which can be called on
Variant types in VBA keep failing. What is the best way of querying
the dimensions of arrays passed in?

Many thanks,

Caspar

[Govert van Drimmelen]

Hi Caspar,

Your array arguments must be of type Object(,) or Double(,). There is
also support for one-dimensional arrays Object() and Double(), with
some detection to deal with both rows and columns. To limit confusion,
I would probably suggest you always use the two-dimensional arrays,
and deal with the sizes of both dimensions yourself.

From a 2D array you can retrieve the dimensions with
myArray.GetLength(0) and myArray.GetLength(1).

There is also the notion of allowing a reference to be passed to your
function, instead of the dereferenced values as in the examples below.
Getting the reference is closer to the VBA concept of a Range argument
and is sometimes useful. But I won't elaborate on that now.

Note that string arrays are not currently supported – you have to do
the conversion to strings yourself. I think they will be some day.

I paste a .dna file below that defines some functions you can try out.
Please post back if you have more specific questions.

Regards,
Govert


<DnaLibrary Name="ExcelDna TestVBArrays">
<![CDATA[

Public Module Test

Function AddThemAll(values(,) As Double) as Double
Dim i As Integer
Dim j As Integer

For j = 0 To values.GetLength(1) - 1
For i = 0 To values.GetLength(0) - 1
AddThemAll = AddThemAll + values(i,j)
Next i
Next j
End Function

Function ConcatThem(values(,) As Object) as String
Dim i As Integer
Dim j As Integer

ConcatThem = ""
For j = 0 To values.GetLength(1) - 1
For i = 0 To values.GetLength(0) - 1
ConcatThem = ConcatThem & values(i,j).ToString()
Next i
Next j
End Function

Function ReturnAnArray() as Object(,)
Dim result(2,3) as Object

result(0,0) = 123.456
result(0,1) = "asd"
result(0,2) = DateTime.Now ' Will look like a number
result(1,0) = ExcelError.ExcelErrorValue
result(1,1) = Nothing ' null is converted to 0 by Excel
result(1,2) = "" ' Looks empty but is a string

ReturnAnArray = result
End Function

End Module
]]>
</DnaLibrary>

[ajwillshire]

Hi,

As you're working with vectors etc I thought I'd highlight this post
that I wrote a few weeks ago:

http://groups.google.com/group/exceldna/browse_thread/thread/e2857619a1fadc7e/0573b1e0f7e51002?lnk=gst&q=mathnet#0573b1e0f7e51002

Since then I've been working heavily with the Mathnet linear algebra
library and it's brilliant.

Once you've passed an array (in the way that Govert demonstrates) you
can convert it into a "vector" object or a "matrix" object and then
use matrix operations on them.

It took me a little while to get it right but I'm now fairly confident
in using it so if you want some specific code examples to help you
achieve something then I can probably put it together for you.

(Mathnet is written in C# but, of course, VB.NET can use C# libraries)

Andrew