Developers who have done any kind of programming with the Office 2007 (and later) Ribbon architecture have encountered almost certainly a scenario that resulted in the loss of their pointer to the ribbon. This happens because the ribbon object has to be stored in a global variable and any kind of unhandled error leads to a “loss of state,” which includes the loss of all global variables.
Rory Archibald came up with an ingenious way to protect the ribbon object by saving the pointer address in an Excel cell.
This note fleshes out Rory’s approach and makes it compatible to 32-bit and 64-bit Office platforms. It also demonstrates, in a reasonably compact example, how to write code that is compatible with (1) different versions of Office (2010 as well as earlier versions) and (2) both 32-bit and 64-bit Office 2010 platforms.
For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/1017%20Save%20a%20global%20in%20an%20Excel%20workbook.shtml
If you need to store something persistently in VBA behind the scenes, why not just create a hidden sheet property like so
Sheets(1).CustomProperties.Add Name:=”Test”, Value:=”ABCD”
after it’s created, you can set and get it like so
Sheets(1).CustomProperties(1).Value = “ABCDE”
MsgBox Sheets(1).CustomProperties(1)
These values are saved with the workbook
Custom sheet properties seem a bit limited in that you can’t refer to them by name, only by number, but they do the job
That’s an interesting idea and there are other places to save information: another add-in, a text file, and the Windows registry being some of them.
You could also use the Hidden Name space which is Global to the Current Instance of Excel
To create
Application.ExecuteExcel4Macro “SET.NAME(“”RibbonState””,””1234567890?”)”
To retrieve
rState = Application.ExecuteExcel4Macro(“RibbonState”)
To Delete
Application.ExecuteExcel4Macro “SET.NAME(“”RibbonState””)”
Hi Tushar,
Clear description of how to use this technique!
One question. You write: “A key limitation with this technique is that the object variable of interest must be declared outside of the VB project of the workbook containing this code.”. This is contradictory to my experience: the technique works just fine for me, with the object variable being in just one VBProject (the one that may get “zapped”).
Hi Jan Karel,
Thanks.
Here’s the scenario where I would not trust this technique.
There’s a class, say, Class1. There’s a global object X of type Class1.
So, there is only 1 handle (pointer) to the pointer that points to the memory allocated to X.
The code saves the value of the handle in X in a worksheet cell (or a name).
The project suffers a loss of state and X is lost.
At this point, there is no handle to the pointer to the memory location of X. Since there are zero handles pointing to the pointer to X, X is now subject to garbage collection.
The code re-establishes the handle to X using the technique in this discussion. But, now, there is no guarantee that X is still hanging around in memory! I’d say that if this technique works it’s sheer luck.
Well, I’ve been using the trick to re-instate the ribbon object for a cuople of weeks now and it seems to work all the time, so sounds like my luck is unsurpassed :-)
Hi Tushar
I have an idea I’d like to fly by you (or anyone else listening).
I have filled a VBA collection object with items and I’d like this collection object retained, even if VBA loses its state. This is much the same problem as you have with ribbons.
It wouldn’t be enough to store a pointer address in an Excel cell because VBA garbage collection might have cleaned up.
Then I thought of the experiences with creating a Parent property against child classes – memory leaks could occur.
Here’s the sneaky idea.
What if I could purposely create a reference loop (memory leak) so VBA skips that object for garbage collection, then save the pointer address out to an Excel cell?
My object is then always available, even after loss of state, and I can keep my collection of items alive for whenever I need them.
Thoughts?
@Rob, if the members of the collection were identified by pointers, could those pointers change due to garbage collection upon state reset? That is, could the locations in memory of objects within a collection change upon state reset and garbage collection? If so, saving such collections would be problematic.
As I understand it, each object contains a reference count – a count of how many other things are referencing the object.
So long as the collection reference count is not zero, I’d hope that items within the collection are be safe.
I developed the experiment and Excel crashed pretty quickly. Either I’m not creating the loop properly or the theory is flawed.
Never mind!
Rob: Why not save the pointer to the collection in another project (workbook) a la http://www.dailydoseofexcel.com/archives/2011/11/01/protect-a-global-variable-in-another-vb-project/
You will need to save only the pointer to the collection, i.e., the collection variable.
That’s a way better idea. I don’t remember reading that article. Good work!
Dear Tushar
Thank you so much for this article. I have spend hours figuring out why the technique described by Ron de Bruin wasn’t sufficient. At one point I was almost sure that I had problems with my home installation as well as the installation at my workplace. Couldn’t believe that microsoft would make it so cumbersome to create custom ribbon with different states (just recently upgraded from Office 2003 to Office 2010 at work).
Do you recommend one global storage add-in for multiple projects or would you make a project specific global storage add-in to each project? I have implemented the technique in three different projects and have three identical global storage add-ins, one in each project folder.
Regards
Thomas Ellebæk