Best way to pass cell data between Excel and Excel-DNA

[Forna]

Hello everybody,
congratulations for the project.

I would like to migrate to Excel-DNA an Excel financial application
that downloads data related to several stock symbols from different
websites.
I have an excel worksheet with all symbols in the column "A" (e.g.
IBM, GOOG, AAPL,etc...)
The user can select one or multiple rows at the same time and clicking
a button will update the financial data.
I have a sub that cycles through all the rows the user has selected
(single rows and selection areas both work) and retrieves the data
corresponding to those symbols.
I am wondering what is the best way to pass the selected symbols from
Excel to Excel DNA and the best way to write the results back to
excel.

Here are the simplified versions of the subs.

'************************************************************************************
' Sub UpdateSelectedRows
' The first sub identifies all rows selected by the user and cycles
through
' the corresponding symbols from the "A" column.
'************************************************************************************
Sub UpdateSelectedRows()
Dim Ws As Worksheet
Dim Wsname, sym As String
Dim singleRange, srange, cell As Range
Dim rownum As Integer

'Get the current Worksheet
Wsname = ActiveSheet.Name
Set Ws = ThisWorkbook.Worksheets(Wsname)

With Ws
' identify all areas selected by the user
For Each singleRange In Selection.Areas
' number of rows for each selected area
rownum = singleRange.Rows.Count
' range of selected rows (only the "A" column is returned)
Set srange = .Range(Cells(singleRange.Row, "A"),
Cells(singleRange.Row + rownum - 1, "A"))
' iterate through all "A" columns cells in the selected
range
i = 0
For Each cell In srange
i = i + 1
If cell.Value <> "" Then
' get the current symbol - the symbol is used to
download the data from the website
sym = cell.Value
' get the current row - the row is used to write
the result back to the correct excel row
rownum = cell.Row
' Call the sub on the current symbol
Call DownloadData(Ws, sym, rownum)
End If
Next cell
Next singleRange
End With
End Sub

'************************************************************************************
' Sub DownloadData
' The second sub connects to the websites, retrieves the data based on
the value of sym
'************************************************************************************
Sub DownloadData(Ws As Worksheet, sym As String, rownum As Integer)
Dim data1, data2 As String
' Get data from the web based on the sym value...
data1 = "Data 1 for " & sym
data2 = "Data 2 for " & sym
' Write back result to the specific excel row
Call DistributeData(Ws, rownum, data1, data2)
End Sub

'************************************************************************************
' Sub DistributeData
' The third sub writes the data back to excel based on the original
row number
'************************************************************************************
Sub DistributeData(Ws As Worksheet, rownum As Integer, ByVal data1 As
String, ByVal data2 As String)
With Ws
.Cells(rownum, 2) = data1
.Cells(rownum, 3) = data2
End With
End Sub


I think I have the following options to integrate Excel-DNA in the
project:

1. Call an Excel-DNA UDF for each symbol, so replacing the
DownloadData sub with a Excel-DNA function.
This is the easiest way but maybe not a good idea since it requires
a lot of context switching.
Anyway, consider that the UDF downloading the data will be an
asynchronous webclient function
(so multiple rows can be updated at the same time).
The context switching time may be insignificant compared to the
data download time that requires
about 0.5 - 1 sec. Also, the async update allows the delays to
overlap and not queue...

2. Put all symbols in an array and pass it from Excel to the Excel-DNA
UDF.
Then pass an array back with the results from the UDF to Excel
Even better for me would be Collections.
I currently use a similar method to pass data between functions
using VBA Collections.
Is passing a VBA Collection back and forth an Excel-DNA UDF also a
good idea?

3. Get the data from the Excel-DNA UDF directly. Then write the data
to the worksheet directly from the UDF.
In this case Excel would only trigger the update action without
passing any data to Excel-DNA.
In this case I should build the functionality to identify the
selected rows in the Excel-DNA function.

Thanks a lot
Paolo

[Govert van Drimmelen]

Hi Paolo,

All the options you mention are viable.

I suggest
1. Try to port your VBA code to VB.NET, maybe looking at the
discussions and document here: http://sysmod.wordpress.com and th
recent discussions with Patrick O'Beirne on this newsgroup. With some
small changes (e.g. ThisWorkbook doesn't exist, but you might look at
ActiveWorkbook) you should be able to get your existing plan working
in VB.NET. That might be a good start.
2. Have a look at the FinAnSu example (a slightly older but leaner
example here: http://code.google.com/p/finansu/ and the current
version here https://github.com/brymck/finansu). There is a more
sophisticated RTD-base approach that makes the UDFs update
asynchronously. You might even be able to use the add-in as is for
your purposes.

Then, if you have more specific questions, please post again.

Regards,
Govert

[Forna]

I have been reading in the forum about passing variables from Excel
VBA to an Excel DNA C# function.
I would just like to get a confirmation on the passable types...

- basic types (string, integer...) and two-dimensional arrays can be
passed fine;

- collections cannot be passed from VBA to C# since there is no
equivalent as a VBA collection in C# (but they may be passed from VBA
to VB.Net).

Can you confirm this is correct?

Thank you

On Feb 12, 5:22 pm, Govert van Drimmelen <gov...@icon.co.za> wrote:
> Hi Paolo,
>
> All the options you mention are viable.
>
> I suggest
> 1. Try to port your VBA code to VB.NET, maybe looking at the

> discussions and document here:http://sysmod.wordpress.comand th

> recent discussions with Patrick O'Beirne on this newsgroup. With some
> small changes (e.g. ThisWorkbook doesn't exist, but you might look at
> ActiveWorkbook) you should be able to get your existing plan working
> in VB.NET. That might be a good start.
> 2. Have a look at the FinAnSu example (a slightly older but leaner
> example here:http://code.google.com/p/finansu/and the current

> version herehttps://github.com/brymck/finansu). There is a more

[Govert van Drimmelen]

Hi Paolo,

The simplest way to call your Excel-DNA user-defined functions or
macros from VBA is to use Application.Run.
For this, at least the following types work, maybe a few more.
String
Double
Integer
Date (careful - passed as string?)
Variant (with nothing strange inside - only string, double etc)
Array of Double
Array of String (though Excel-DNA does not allow string array type to
be used as a parameter, so you need to define it as object array
in .NET)
Array of Variant (object array in .NET)

Collections won't work - you should convert to arrays.
There is no difference in this respect between VB.NET and C#, it has
to do with the data types that the Excel C API supports. Excel does
not know how to convert your VBA Collection class into a type it can
pass along with the C API.

--------

Another, more advanced, approach to integrating VBA and your Excel-DNA
add-in is to make a COM Server which you can use from VBA. In this
case the integration and rules would be completely different, since we
are using the .NET/COM integration instead of the Excel C API for
linking VBA and .NET. I would not suggest you start with this, but
it's good to know there is another integration option too.

Regards,
Govert

On Feb 22, 11:22 pm, Forna <paolo.fornar...@gmail.com> wrote:
> I have been reading in the forum about passing variables from Excel
> VBA to an Excel DNA C# function.
> I would just like to get a confirmation on the passable types...
>
> - basic types (string, integer...) and two-dimensional arrays can be
> passed fine;
>
> - collections cannot be passed from VBA to C# since there is no
> equivalent as a VBA collection in C# (but they may be passed from VBA
> to VB.Net).
>
> Can you confirm this is correct?
>
> Thank you
>
> On Feb 12, 5:22 pm, Govert van Drimmelen <gov...@icon.co.za> wrote:
>
>
>
>
>
>
>
> > Hi Paolo,
>
> > All the options you mention are viable.
>
> > I suggest
> > 1. Try to port your VBA code to VB.NET, maybe looking at the
> > discussions and document here:http://sysmod.wordpress.comandth
> > recent discussions with Patrick O'Beirne on this newsgroup. With some
> > small changes (e.g. ThisWorkbook doesn't exist, but you might look at
> > ActiveWorkbook) you should be able to get your existing plan working
> > in VB.NET. That might be a good start.
> > 2. Have a look at the FinAnSu example (a slightly older but leaner

> > example here:http://code.google.com/p/finansu/andthe current