Separate State Management in Multiple Workbooks for the same add-in

[Thisara Ranadewa]

Hi All,

First, thanks for this fantastic piece of tech that I've been enjoying for the past 2 weeks. 

I have a use case where I want to have a UDF that POSTs to an end point and has a global enable/disable tick box in the excel ribbon. I've implemented and tested this successfully for a single excel session.

The way I have implemented this in C# is by changing a static boolean from the ribbon and referring to that boolean from my  UDF implemented as a static method.

The issue I have is with multiple  excel workbooks opened at the same time using the add-in simultaneously. It seems like if I enable POSTs in one workbook, that information is leaked to the other work book as well.

I would like to have separate states on the static boolean on separate workbooks. How can I achieve this? 

When I open up two excel work books is it considered two excel instances or a single instance? 

I ran this mail through chat-gpt and it says I will have to use a dictionary to manage the different work books. Is that the case?

Cheers,

Thisara 

[Govert van Drimmelen]

Hi Thisara,

 

Normally, different Excel workbooks are loaded into the same process, even though they are shown in different top-level windows.

You can confirm this in TaskManager by checking that you have a single Excel.exe process running.

There is a command-line switch that lets you start a new Excel instance (/x) and when debugging an Excel-DNA add-in, the debugging setup will use this to start debugging in a new process. But additional workbooks you open will be in the same process.

 

There is a single ribbon manager object in your Excel-DNA add-in for the whole process, so it is shared by all workbooks.

ChatGPT is right that one approach for per-workbook information displayed is to:

• Keep track of the state you want to show in a Dictionary with the Workbook or Workbook name as key
• Set the various ribbon callbacks to be aware of the info to track for the active workbook
• Listen to the Application.WorkbookActivate, and invalidate the ribbon with IRibbonUI.Invalidate()

 

I’ve made a small sample that shows how you might do this:

govert/TestRibbonWorkbookTracking (github.com)

 

One quirk about this scheme relates to the activation and invalidation when a new workbook is created. In this case, the activate event and ribbon invalidate call will happen on the ‘deactivating’ workbook first.

So you’ll see the deactivated workbook show the wrong information, behind the new windows for the new workbook. This is a long-standing Excel bug related to the sequence of activation and ribbon tracking, and is only a cosmetic problem. It doesn’t affect things when you are switching between open workbooks. I don’t remember a workaround for this.

 

Anyway, I hope this  helps get you on the right track.

 

Regards,

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 on the web visit https://groups.google.com/d/msgid/exceldna/2d482b5f-c36a-41ee-b4e9-130bb69034can%40googlegroups.com.

[Thisara Ranadewa]

Hi Govert, 

Thanks for the quick response and the sample repository. I changed it for my use case a bit and its working as expected. 

Much obliged. 

Cheers,
Thisara