Call ExcelDna From VBA Module And Return Array

[Axl West]

See example below. Returning a string works great! Returning a string
array is strange. Excel either says "type mismatch" or "object
variable or with block variable not set".

Is there another way other than Application.Run to call Excel DNA from
VBA? The functions show up in the Excel function browser but not in
the Excel macros (VBA) object browser.

Or does the ExcelFunction attribute need to change? Or cast / convert
between types? Returning an object array works slightly better than
returning a string array but the code must be missing something.

Thanks!

Axl



[ExcelFunction()]
public static string ExcelDnaReturnString(string url)
{
return "This is the string: " + url;
}

[ExcelFunction()]
public static object[] ExcelDnaReturnArray(string a, string b)
{
string[] ary = new string[2];
ary[0] = "string a is " + a;
ary[1] = "string b is " + b;
return ary;
}


Sub TestExcelDnaString()
Dim str As String
str = Application.Run("ExcelDnaReturnString", "http://
www.google.com")
MsgBox (str)
End Sub

Sub TestExcelDnaArray()
Dim ary() As String
Dim obj As Object
obj = Application.Run("ExcelDnaReturnArray", "Hello", "World")
MsgBox (obj)
End Sub

[Chris Spicer]

Hi Axl,

I'm wondering if this is a problem with your VBA? If I use your C#
code and VBA code as written, I get an error at

obj = Application.Run("ExcelDnaReturnArray", "Hello", "World")

However, if I alter your VBA slightly to be:

Sub TestExcelDnaArray()
Dim ary() As String

Dim var As Variant
var = Application.Run("ExcelDnaReturnArray", "Hello", "World")
' MsgBox (obj)
End Sub

... and then add a watch on 'var', I can see the ExcelDnaReturnArray
functioning returning the expected result.

(I commented out the MsgBox as it can only handle strings, not
variants).

Hope this helps,

Chris

On 6 June, 01:41, Axl West <aweslow...@gmail.com> wrote:
> See example below. Returning a string works great! Returning a string
> array is strange. Excel either says "type mismatch" or "object
> variable or with block variable not set".
>
> Is there another way other than Application.Run to call Excel DNA from
> VBA? The functions show up in the Excel function browser but not in
> the Excel macros (VBA) object browser.
>
> Or does the ExcelFunction attribute need to change? Or cast / convert
> between types? Returning an object array works slightly better than
> returning a string array but the code must be missing something.
>
> Thanks!
>
> Axl
>
>                 [ExcelFunction()]
>                 public static string ExcelDnaReturnString(string url)
>                 {
>                         return "This is the string: " + url;
>                 }
>
>                 [ExcelFunction()]
>                 public static object[] ExcelDnaReturnArray(string a, string b)
>                 {
>                         string[] ary = new string[2];
>                         ary[0] = "string a is " + a;
>                         ary[1] = "string b is " + b;
>                         return ary;
>                 }
>
> Sub TestExcelDnaString()
>     Dim str As String

>     str = Application.Run("ExcelDnaReturnString", "http://www.google.com")

[Chris Spicer]

I've looked at this closer and I see what you mean - the call to the
ExcelDna function returns the array as a variant/variant type, whereas
the equivalent function in VBA would return as a variant/string.

I guess this is related to the ExcelDnaReturnArray function returning
an object[] array... I don't think ExcelDna will handle returns types
of string[] but you could try it.

Regards,

Chris

> > End Sub- Hide quoted text -
>
> - Show quoted text -

[François]

Hi,

I've had a similar problem for a while now and I don't manage to solve
it.
I have plenty of C# function returning object[,]. I can call them fine
in excel.
I decided to move the call in vba rather than directly in excel.
I am using a variant to receive the result of my functions.
It was working fine.
Suddently it stopped once. My only change is that I had added some
conditional formatting in excel. Somehow, the conditional formatting
on the spreadsheet was incompatible with the vba... My variant did no
longer contained an array but only the first element of my 2D array.
So I removed my conditional formatting and now I do my formatting in
vba.
I was ok with that until now. The error comes back again on some other
people's machine whereas there is no conditional formatting. The same
spreadsheet runs fine on my machine. I don't manage to derive any
logic about that behavior.
Any help/idea would be very much appreciated.
Thank you.

François

On 7 June, 10:10, Chris Spicer <chris.spi...@technicana.com> wrote:
> I've looked at this closer and I see what you mean - the call to the

> ExcelDna function returns thearrayas avariant/varianttype, whereas

> > >arrayis strange. Excel either says "typemismatch" or "object

> > > variable or with block variable not set".
>
> > > Is there another way other than Application.Run to call Excel DNA from
> > > VBA? The functions show up in the Excel function browser but not in
> > > the Excel macros (VBA) object browser.
>
> > > Or does the ExcelFunction attribute need to change? Or cast / convert

> > > between types? Returning an objectarrayworks slightly better than
> > > returning a stringarraybut the code must be missing something.

>
> > > Thanks!
>
> > > Axl
>
> > >                 [ExcelFunction()]
> > >                 public static string ExcelDnaReturnString(string url)
> > >                 {
> > >                         return "This is the string: " + url;
> > >                 }
>
> > >                 [ExcelFunction()]
> > >                 public static object[] ExcelDnaReturnArray(string a, string b)
> > >                 {
> > >                         string[] ary = new string[2];
> > >                         ary[0] = "string a is " + a;
> > >                         ary[1] = "string b is " + b;
> > >                         return ary;
> > >                 }
>
> > > Sub TestExcelDnaString()
> > >     Dim str As String
> > >     str = Application.Run("ExcelDnaReturnString", "http://www.google.com")
> > >     MsgBox (str)
> > > End Sub
>
> > > Sub TestExcelDnaArray()
> > >     Dim ary() As String
> > >     Dim obj As Object
> > >     obj = Application.Run("ExcelDnaReturnArray", "Hello", "World")
> > >     MsgBox (obj)
> > > End Sub- Hide quoted text -
>

> > - Show quoted text -- Hide quoted text -