Groups keyboard shortcuts have been updated
Dismiss
See shortcuts

Getting value of variables or arrays from the .Net / ExcelDNA to VBA

68 views
Skip to first unread message

JonS

unread,
Dec 10, 2024, 7:59:24 PM12/10/24
to Excel-DNA
Hi,

I am a newbie (and sorry, still VB.Net oriented). Apologies for this somewhat clumsy question.

Using ExcelDNA, I understand you can create and use WinForms,, variables, arrays, calculations and subroutines within the '.Net' code environment and you can call ExcelDNA subroutines and functions from within VBA quite easily. Really great.

Bu can you access the value of specific '.Net' variables or arrays from within VBA?

In other works, can you get access to the current value of those '.Net' variables and arrays from within in VBA. I guess you could write specific functions to return some of those values'

But is there another elegant way to get the value of these variables (just the way you call a '.Net' procedure from within VBA?

Many thx, JonS

Govert van Drimmelen

unread,
Dec 11, 2024, 3:37:43 AM12/11/24
to exce...@googlegroups.com

--------------------------------------------------
Excel-DNA is registered on GitHub Sponsors.
Your contributions directly fund further development.
--------------------------------------------------

 

Hi Jon,

 

You can expose classes from your .NET code as COM classes to VBA.

The details get a bit tricky depending on what features you want (e.g. early-binding in VBA, which .NET runtime are you using etc.).

But once you have it working, that;s a good way to use your add-in code from VBA.

There are various sample project and scattered documentation, but I’m also happy to help if you get stuck.

I think the samples haven’t been checked to work recently, and project files were ported to a new format.

So the samples might not quite work as is.

 

Here is a small VB.NET project you can look at:

Samples/ComServerVB at master · Excel-DNA/Samples

I think the project file needs:

    <ExcelAddInComServer>true</ExcelAddInComServer>

 

 

Then the Readme here is a bit more instructive, though I’ve not gone through it recently to see if everything is still valid:

Samples/DnaComServer at master · Excel-DNA/Samples

(Things are a bit more complicated if you’re targeting .NET core (.NET 6+) compared to .NET Framework 4.x.)

 

The main steps I can think of off-hand are:

* The classes you want to expose should be public, marked with ComVisible(true), a ProgId and maybe a Guid.

* The topic of ClassInterface is a bit complicated – maybe mark as AutoDispatch if targeting .NET Framework, but need to be very careful when changing the class methods.

* The project needs a property                                 <ExcelAddInComServer>true</ExcelAddInComServer>

* Your project must definitely not be marked as “Register for COM Interop”

* You need to call ComServer.DllRegisterServer() or register the .xll (with regsvr32) from command line. This is the C#

 

Public Class AddInEvents

    Implements IExcelAddIn

 

    Public Sub AutoOpen() Implements IExcelAddIn.AutoOpen

        ComServer.DllRegisterServer()

    End Sub

 

    Public Sub AutoClose() Implements IExcelAddIn.AutoClose

        ComServer.DllUnregisterServer()

    End Sub

End Class

 

OK, so you might need some patience to get it working, but that’s the plan for exposing a rich interface from the add-in to VBA.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/exceldna/090a33cd-d663-49f1-b91f-95d87e431fb1n%40googlegroups.com.

Message has been deleted

JonS

unread,
Dec 13, 2024, 3:47:07 AM12/13/24
to Excel-DNA
Thank you Govert!
Reply all
Reply to author
Forward
0 new messages