Handling Excel VBA events using ExcelDNA

[guruteja jahagirdhar]

Hi,

We are planning to move all the existing VBA code in our Excel related application to VB.NET. 

While exploring we found out that Excel-DNA is very useful and are looking into its capabilities.

A basic question, how do we handle Excel Workbook/Worksheet events like SheetChange,SheetseletionChange,SheetActivate etc events in Excel-DNA? is it as simple as handling events in VB.NET like

 

Public Sub OpenWorkBook() Handles ExcelObj.WorkbookOpen

...

...

...

End Sub

Where ExcelObj is the Excel Object or something similar that is returned from ExcelDNA.

Any code samples/examples or documentation in this direction would be helpful.

Thanks

Teja

[Govert van Drimmelen]

Hi Teja,

COM events work will fine with your Excel-DNA add-in. You need to reference the Primary Interop Assembly (via NuGet you can add the Excel-DNA.Interop package).

Then to get hold of the root Application object, you cann ExcelDnaUtil.Application. This returns the right COM Application object for your Excel instance.

I paste a small example below of how your event handler might work.

Regards,

Govert

'''''''''''''''''''''''''' VB.NET event handler sample '''''''''''''''''''''''''''

Imports Microsoft.Office.Interop.Excel

Imports ExcelDna.Integration

' This class has a WithEvents Application field, allowing it to easily handle

' any Application events.

' An instance is created in the AutoOpen handler below.

' A singleton instance could also be created in a 'Shared Sub New' constructor.

Public Class EventTest

    Dim WithEvents Application As Application

    Public Sub New()

        Application = ExcelDnaUtil.Application

    End Sub

    Private Sub Application_WorkbookOpen(Wb As Workbook) Handles Application.WorkbookOpen

        MsgBox("Opened workbook: " + Wb.Name)

    End Sub

End Class

' This class implements the Excel-DNA interface IExcelAddIn,

' so the AutoOpen will be called when the Add-In is loaded.

' We just create a new instance of the event handling class.

Public Class AddInTest

    Implements IExcelAddIn

    Dim applicationEvents As EventTest

    Public Sub AutoOpen() Implements IExcelAddIn.AutoOpen

        applicationEvents = New EventTest

    End Sub

    Public Sub AutoClose() Implements IExcelAddIn.AutoClose

    End Sub

End Class

[guruteja jahagirdhar]

Hi Govert,

Thanks for the quick reply.

I tried the the below code, but however i couldn't get the message box when i open then .xll addin. Did i miss anything? 

Imports ExcelDna.Integration

Imports ExcelDna.Integration.ExcelDnaUtil

Imports Microsoft.Office.Interop.Excel

Public Class TestingEvents

        Implements IExcelAddIn

        Dim WithEvents Application As Application

        Public Sub New()

            Application = ExcelDnaUtil.Application

        End Sub

        Private Sub Application_WorkbookOpen(Wb As Workbook) Handles Application.WorkbookOpen

            MsgBox("Opened workbook: " + Wb.Name)

        End Sub

        Public Sub AutoClose() Implements IExcelAddIn.AutoClose

           

        End Sub

        Public Sub AutoOpen() Implements IExcelAddIn.AutoOpen

            Application = ExcelDnaUtil.Application

        End Sub

    End Class

Thanks

Teja

[Govert van Drimmelen]

Hi Teja,

Your code should work fine.

The WorkbookOpen event only fires when you open an existing Workbook, not when opening the add-in or making a new workbook.

So you would not expect the message to show when you open the add-in.

-Govert

[guruteja jahagirdhar]

Hi Govert,

Yes, when i open an existing workbook i could see the MessageBox. I will try various events and come back if i'm struck any where.

Thanks

Teja

[guruteja jahagirdhar]

Hi Govert,

I just checked out that the Excel Interop assemblies are only for intellisense support. If i do not want to use them how do i handle the events SheetselectionChanged etc in the above quoted class ?

[Govert van Drimmelen]

The interop assembly is not just for IntelliSense support.

Doing the events without it is very hard.

If you are targeting .NET 4, then the bits of the Interop Assembly that you use are (by default) embedded into your assembly when you compile. So you need not distribute anything extra. (This is the Embed Interop Types=True setting in the Reference).

Do you have any reason not to use the PIA assemblies?

-Govert

[guruteja jahagirdhar]

Thanks Govert. No specific reason, just wanted to know any other ways of calling the event apart from the interop.

Thanks

Teja

[Govert van Drimmelen]

It can be done with reflection or by creating your own interop types instead of using the Interop Assembly, but that's hard. Here's a C# version: http://www.codeproject.com/Articles/10262/Receiving-Events-from-late-bound-COM-servers

If you are using the Interop Assemblies, you can either use the WithEvents syntax with the Handles clause, or call the AddHandler statement.

-Govert

[guruteja jahagirdhar]

Thanks Govert. I will stick to using the Interops its easy and more convenient.

> --
> You received this message because you are subscribed to a topic in the
> Google Groups "Excel-DNA" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/exceldna/Ntnh6dxzLNM/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> exceldna+u...@googlegroups.com.
> To post to this group, send email to exce...@googlegroups.com.
> Visit this group at http://groups.google.com/group/exceldna.
> For more options, visit https://groups.google.com/d/optout.



--
Thanks & Regards
J. Guruteja

[guruteja jahagirdhar]

Hi Govert,

Excel-DNA is amazing and we are planning to move our existing VBA code using it. However, we saw that NetOffice can also be used for migrating the VBA solution.

Just curious to know the difference between Excel-DNA and NetOffice and what are the advantages of using Excel-DNA over NetOffice? 

Thanks

Teja

> exceldna+unsubscribe@googlegroups.com.

[Govert van Drimmelen]

Hi Teja,

NetOffice is a set of version-independent Interop Assemblies. So you get the union of all the COM type definitions from all the Excel versions, with IntelliSense to tell you under which Excel versions a particular class or method is available. A disadvantage is that it does not support the 'Embed Interop Types' options. NetOffice supports all the Office application, not just Excel. NetOffice has a COM reference management layer that helps if automating the Office app from outside, but not useful for an add-in running inside Excel on the main thread.

Excel-DNA allows you to make an Excel add-in using the native Excel C API. This is required for making high performance worksheet UDF functions. Excel-DNA also supports non-admin ribbon and RTD installation. 

So for making an Excel add-in that provides UDFs, you should use Excel-DNA. In your Excel-DNA add-in you can use the NetOffice assemblies instead of the Primary Interop Assemblies if you need to use COM object model features that are only available in specific Excel versions. If you're happy using only COM object model features available on all the Excel versions where your add-in will run, using the PIA is a bit simpler.

Excel is a special case in the Office suite, since it has a separate native API that is very useful, and has some quirky COM bits, like the RTD servers. For all the other Office apps things are simpler - the extension model is just the COM interfaces. NetOffice is great for making version-independent Office add-ins for the other apps.

For making Excel add-in you want to use Excel-DNA, either with the PIA assemblies or NetOffice.

-Govert

[guruteja jahagirdhar]

Hi Govert,

Thanks for the detailed info. :)

Thanks

Teja

> exceldna+u...@googlegroups.com.

[guruteja jahagirdhar]

Hi Govert,

Our .NET application writes Data in XML format into an Excel sheet and save it at a location, when we open this excel sheet an XLA addin reads this XML data and populates the excel sheet it also shows data loading indicator while data is loading and also once the data is loaded it shows a form shoeing the properties of the selected cell. We now want to move this XLA code to VB.NET, do i have to write a ComAddin in Excel-DNA for this? we also write some Macros in the excel  using late binding in our .NET application, can i still do this and take advantage of calling methods in VB.NET using Excel-DNA?

[Govert van Drimmelen]

Hi Teja,

I'm not sure exactly what you're asking.

Your Excel-DNA could show a form for the progress (though you might have to run the form on a separate thread if it's not modal and Excel must still operate).

You can call macros using Application.Run.

-Govert

[guruteja jahagirdhar]

Thanks Govert. 

Can i call VB.NET code from VBA code ? using Excel-DNA?

Thanks

Teja

[Govert van Drimmelen]

Yes. There are different ways.

You can call any registered macro <ExcelCommand> via Application.Run.

You can also make COM classes in the Excel-DNA add-in that you can Tools->Reference in your VBA projects.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of guruteja jahagirdhar [tejaja...@gmail.com]
Sent: 05 June 2014 04:24 PM
To: exce...@googlegroups.com
Subject: Re: [ExcelDna] Re: Handling Excel VBA events using ExcelDNA

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.

[guruteja jahagirdhar]

Great !!!

So, i have to write a ComAddin using Excel-DNA to replace my .XLA addin. I will try that and will get back to you if i'am struck any where. 

Thanks for the very quick reply.

Thanks

Teja

-Govert

To post to this group, send email to exc...@googlegroups.com.

[Govert van Drimmelen]

No. Nothing to do with making a COM add-in.

You make a regular Excel-DNA add-in. Your Public Subs can be called from VBA via Application.Run.

If you want to also make some COM classes that can be referenced from VBA, you follow the instructions here: 

http://mikejuniperhill.blogspot.com/2014/03/interfacing-c-and-vba-with-exceldna-no.html

http://mikejuniperhill.blogspot.com/2014/03/interfacing-c-and-vba-with-exceldna_16.html

Making a COM Add-in (I'm not sure exactly what you mean by that) will cause great confusion.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of guruteja jahagirdhar [tejaja...@gmail.com]

Sent: 05 June 2014 04:32 PM

[guruteja jahagirdhar]

Wow, thats amazing..!! I was mostly worried about calling my VB.NET code (some public subs and functions) from VBA, this solves my problem. 

Thanks a ton Govert..!!

[guruteja jahagirdhar]

Hi Govert, 

Just wanted to check with you about the performance. 

I wrote a sample program which will populate an Excel sheet with some sample data, i'm trying to populate 450 rows having 150 columns in WorkBook_Open event in VB.NET.

i see that the time taken in VB.NET and the time taken in VBA are same ( the same code is written under workbook_open event of VBA) i assumed VB.NET code to be more perfomant but it is not. Is this the limitation of Excel or there are any other ways to over come it? some thing like loading the data asynchronously etc that EXCEL-DNA can provide.

Thanks

Teja

[Govert van Drimmelen]

Hi Teja,

 

If you are using the COM interfaces to populate the cells, the overhead of the COM calls to Excel overwhelm any other aspect of the performance, and you should expect similar performance whether using .NET or VBA.

 

Using the C API, you can improve the performance by using the native ExcelReference type, and settings the values all at once with a 2D object array.

One snag is that you need to be in a ‘macro’ context to call the C API, which your Worksheet_Open event handler is not. Easiest is to make a Sub and then call it via Application.Run.

Another approach is to use the ExcelAscynUtil.QueueAsMacro helper to switch to a macro context, but that’s a bit harder to explain now.

 

So your code might look like this:

 

Imports ExcelDna.Integration

Imports Microsoft.Office.Interop.Excel

 

Public Class TestingEvents

    Implements IExcelAddIn

 

    Dim WithEvents Application As Application

 

    Public Sub AutoOpen() Implements IExcelAddIn.AutoOpen

        Application = ExcelDnaUtil.Application

    End Sub

 

    Public Sub AutoClose() Implements IExcelAddIn.AutoClose

          

    End Sub

 

    Private Sub Application_NewWorkbook(Wb As Workbook) Handles Application.NewWorkbook

        MsgBox("Opened workbook: " + Wb.Name)

        Application.Run("DumpValues")

    End Sub

End Class

 

Public Module DumpData

    Sub DumpValues()

       

        ' Set target range (0-based row and column indices)

        Dim target As New ExcelReference(0, 449, 0, 149, "Sheet1")

        Dim values(,) As Object = New Object(449, 149) {}

 

        ' Fill in values

        For i = 0 to 449

            For j = 0 to 149

                values(i,j) = String.Format("({0}, {1})", i, j)

            Next

        Next

 

        Target.SetValue(values)

    End Sub

End Module

 

 

-Govert

[guruteja jahagirdhar]

Hi Govert,

we tried this approach and saw few limitations.

1. If there are any merged cells in the Excel Sheet, the below approach treats them as two cells and writes the data into them causing erroneous output. 

2. If we want to decide the cell color based on the value that needs to be written into it, this approach can't do that.  

Is there any way to write the value directly into the cell with out calling the COM interfaces? and also provide good performance. 

Alternatively, Is there a way to capture the Excel Scroll event so that dataloading happens dynamically when the user explicitly scrolls down?

Thanks 

Teja

On Friday, 6 June 2014 19:57:39 UTC+5:30, Govert van Drimmelen wrote:

Hi Teja,

 

If you are using the COM interfaces to populate the cells, the overhead of the COM calls to Excel overwhelm any other aspect of the performance, and you should expect similar performance whether using .NET or VBA.

Teja

 

&n

...

[Govert van Drimmelen]

Hi Teja,

 

The ExcelReference.SetValue(…) does not use the COM API, but goes through the native C API instead.

I’m not sure what the performance would be when doing one cell at a time, relative to using the COM Range.Value call.

I know nothing about merged cells, aside from the fact that they’re to be avoided completely.

 

If you have to write formatting, that can be done through the C API as well, but it’s more difficult than using the COM interfaces, and still only one cell at a time.

(Maybe you can set Conditional Formatting for the range, instead of set the colours explicitly.)

 

Another way to do this would be to create a workbook file directly (e.g. using the ClosedXML  library), then copy and paste the data (with formatting).

I still don’t know how merged cells would work with a copy/paste.

 

I don’t think it’s easy to detect window scroll events.

 

It all sounds rather complicated …

But to summarize the answer to your original question:

Excel-DNA gives you access to the C API, which is a more restricted than the COM object model, but can sometimes be faster. For using the COM object model, the .NET code should have similar performance to VBA code.

 

Regards,

--

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.

[guruteja jahagirdhar]

Thanks Govert. I will use the Excel Reference for data population. 

I will work on the formatting cells option and let you know if i get any break through.

Thanks

Teja

[guruteja jahagirdhar]

Hi Govert,

Just wanted to learn about the debugging with the existing workbooks. I have all my code in WorkBook_Open Event and addin reads the data and manipulates it in the event. 

This applies only to an existing workbook that is already created by some other application. How can i debug an already existing workbook so that the break point i have in workbook_open event is hit?

Thanks

Teja

[guruteja jahagirdhar]

Hi Govert,

I found a way to debug the below scenario, i added the file path of the excel workbook in the command line argument in the debug tab of Visual studio along with the excel addin path. I added a break point in workbook_Open Event and upon hitting F5 i see that the break point is hit twice, though the workbook is opened only once, is it expected? or did i do any thing wrong? or is there any other way to debug an existing workbook?

Thanks 

Teja

[Sebastian Widz]

Hi Govert,
How to implement the same in c#?
Regards,
Sebastian

...

[Ben Mcmillan]

that's a good question, how does that translate into C#?

Specifically, how what's the equivalent of "Handles" in C#, eg in this method definition:

 Private Sub Application_WorkbookOpen(Wb As Workbook) Handles Application.WorkbookOpen

[Caio Proiete]

Hi Ben,

I answered you on StackOverflow:

How do I create a WorkbookOpen event for every workbook?

https://stackoverflow.com/a/55602709/211672

Thanks,

Caio Proiete

https://github.com/caioproiete

--

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 post to this group, send email to exce...@googlegroups.com.

Visit this group at https://groups.google.com/group/exceldna.

[Duyet Le]

How to convert that code in c#.

I am trying to do but not succeed.

Thanks,

Duyet Le

On Wednesday, June 4, 2014 at 6:36:44 PM UTC+7 Govert van Drimmelen wrote:

Hi Teja,

COM events work will fine with your Excel-DNA add-in. You need to reference the Primary Interop Assembly (via NuGet you can add the Excel-DNA.Interop package).

Then to get hold of the root Application object, you cann ExcelDnaUtil.Application. This returns the right COM Application object for your Excel instance.

I paste a small example below of how your event handler might work.

Regards,

Govert

'''''''''''''''''''''''''' VB.NET event handler sample '''''''''''''''''''''''''''

Imports Microsoft.Office.Interop.Excel

Imports ExcelDna.Integration

' This class has a WithEvents Application field, allowing it to easily handle

' any Application events.

' An instance is created in the AutoOpen handler below.

' A singleton instance could also be created in a 'Shared Sub New' constructor.

Public Class EventTest

    Dim WithEvents Application As Application

    Public Sub New()

        Application = ExcelDnaUtil.Application

    End Sub

    Private Sub Application_WorkbookOpen(Wb As Workbook) Handles Application.WorkbookOpen

        MsgBox("Opened workbook: " + Wb.Name)

    End Sub

End Class

' This class implements the Excel-DNA interface IExcelAddIn,

' so the AutoOpen will be called when the Add-In is loaded.

' We just create a new instance of the event handling class.

Public Class AddInTest

    Implements IExcelAddIn

    Dim applicationEvents As EventTest

    Public Sub AutoOpen() Implements IExcelAddIn.AutoOpen

        applicationEvents = New EventTest

    End Sub

    Public Sub AutoClose() Implements IExcelAddIn.AutoClose

    End Sub

End Class

[Govert van Drimmelen]

Maybe something like this.

 

-Govert

 

// ---------------------

using Microsoft.Office.Interop.Excel;

using ExcelDna.Integration;

 

public class AddIn : IExcelAddIn

{

    Application _app;

 

    public void AutoOpen()

    {

        _app = (Application)ExcelDnaUtil.Application;

        _app.WorkbookOpen += _app_WorkbookOpen;

    }

 

    public void AutoClose()

    {

    }

 

    void _app_WorkbookOpen(Workbook Wb)

    {

        System.Windows.Forms.MessageBox.Show($"Opening {Wb.FullName}");

--

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 on the web visit https://groups.google.com/d/msgid/exceldna/a3e9102c-0e2e-4978-96d3-7dba189255f2n%40googlegroups.com.

[Duyet Le]

Thanks  Govert