Re: Best way for Excel DNA to call VBA code?

[Govert van Drimmelen]

Hi,

Firstly, any 3rd party COM/DLL files used from VBA should be quite
easy to use from the .NET solution too. The COM support in .NET is
very good, both in VB.NET and in C# 4+.

Otherwise, if you want to call back to VBA you have a few options:
1. Call a VBA function or macro via Application.Run, something like:
Dim xlApp As Object
xlApp = ExcelDnaUtil.Application
result = xlApp.Run("MyFunction", param1, param2)

where MyFunction could also be a Sub.

2. Use the Excel-DNA COM Server support to make a helper class that
you can CreateObject from VBA, and pass some VBA object to .NET via
this helper.

3. Instead of the Excel-DNA COM Server support, define an
ExcelComAddIn in your managed code, which you load as an add-in into
Excel. On the ComAddIn object create by Excel you can then set the
Object property, and retrieve it from VBA.

Options 2. and 3. will give you more powerful integration, but some
headaches in getting it to work the first time. Start with
Application.Run.

-Govert



On Mar 8, 7:37 pm, w2jc...@gmail.com wrote:
> Hi Govert and the Forum,
>
> My application replies on a few 3rd party COM/DLL that's manipulated by VBA
> code. Unfortunately I cannot touch these 3rd party code while moving my own
> VBA code to dotNet. So naturally I would need XLDNA to call those VBA
> sub/function defined both on the worksheets and in modules. Or even have
> XLDNA to read/update VBA global variables..
>
> I've searched for a long time but couldn't find XLDNA examples on this
> topic.
>
> Will XLDNA be able to call back VBA code? Since VBA is basically COM
> interfaced, I started wondering if NetOffice or native PIA is the way, but
> not so sure...
>
> It's likely I'm not the only one having this type of issue, how do you work
> around it?
>
> Thanks for any input on that. Thoughts? links? examples? All are welcomed!
>
> JC