Save a global variable in an Excel workbook

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

Tushar Mehta

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>