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.
In http://www.dailydoseofexcel.com/archives/2011/08/14/save-a-global-variable-in-an-excel-workbook/ I describe a way, first proposed by Rory Archibald, to save the handle to an object in an Excel cell (or named constant). That is an easy to implement and relatively self-contained approach.
At the same time, I wanted a solution that worked not only with Excel but also other applications such as PowerPoint and Word. I also wanted a solution that worked with variables other than objects declared outside of the VBA project. Consequently, I opted to use a separate add-in that did nothing more than save global variables in a VBA collection. While this requires coordination between two add-ins, the benefits include the ability to extend the solution to platforms other than Excel, the ability to save the state of any global, and also a solution that does not require a Windows API. This note documents such an approach.
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/1018%20Protect%20a%20global%20variable%20in%20another%20VBProject.shtml
Word? Powerpoint? You mean there’s more to Office than Excel?
I remember reading once that you can create a in-memory-only workbook and store values there. Don’t remember which book or website I read that but it is another novel approach. What about using the add-in to store it. For Excel add-ins they have the worksheets, do add-ins for word and powerpoint have a page on them?
OK, found it. It is the hidden name space where you can do it:
http://www.cpearson.com/excel/hidden.htm
Pretty nice way of saving values with names and everything!
Tushar,
I am interested in knowing what you think of the alternative methods I presented. I don’t know if they would be better or worse, or not work at all. If you have any thoughts on the subject I would like to read them.
Thanks, Jon
Jon: It’s a nice suggestion. Use of the hidden namespace was discussed in one of the other discussions on the subject. I don’t recall if it was here on dailydoseofexcel or on mrexcel or in a private forum.
It’s still an Excel-centric way to address the issue, it still requires an API, and it still works only with an object declared in another stack (i.e., outside of the VBA project). In addition, it uses an uncommon Excel feature, with the associated risks.
Other stores that have been mentioned include the Windows registry, workbook (custom) properties, and text files (TXT, DAT, or INI). All of them have one major limitation, i.e., they will work only with an object declared outside of the VBA project.
I don’t follow Mr Excel, so maybe it was in there. So does Word and PowerPoint not have the option of storing the information on a hidden page/slide inside the VBA? When I stored the value I just stored it in an excel worksheet inside the VBA workbook.
Lot’s of solutions for this it appears.
Rob,
The answer is “nothing important, no”.