Use Excel's hidden data store to share data across VBA projects

Have you ever wanted to pass data from a VBA project running in one Excel workbook to a VBA project in another workbook? Sure, the code in one workbook can write data into another workbook, but wouldn't it be nice if you had a place to store inter-workbook data that was hidden from view? And that persisted even if all workbooks were closed?

For example, I have a spreadsheet macro that is only allowed to run if the user's PC contains a special license key -- the macro validates the license whenever a workbook is opened. However, there is a noticeable delay while checking the license so I don't really want to do it more than once, even if the user opens multiple copies of my spreadsheet. Can I set a "valid license exists" flag so all workbooks can see it when they are opened? Even better, can I preserve the setting of the valid license flag even if the user closes all workbooks but leaves Excel running? The answer is yes...

The solution lies in knowing that every Excel instance has an application-level, hidden data repository that is accessible from every VBA project running in that instance. The techniques for using Excel's hidden data store are a bit unconventional, and there are some restrictions, but it does work.

The main restriction you need to know about is that you can only store text strings. That's not as much of a limitation as it might seem initially, because you can, of course, store a value of "42" and interpret it in any way you'd like after retrieving it.

There are also restrictions on the characters you can use in the name of a hidden data item; you can't use any of these:
    / - : ; ! @ # $ % ^ & *( ) + = , < >

The easiest way to think of the hidden data store is as a repository for pairs of strings: the first string is the name of the value and the second string is the value itself. You'll use the ExecuteExcel4Macro method of the Excel application object to read and write the name/value pair to the hidden data area.

To create a hidden data item and assign a value (or to assign a value to an existing item):

ExecuteExcel4Macro "SET.NAME(""MyValueName"", ""Value of my item"")"
                      

Select all Open in new window

NOTE: the name and value strings are surrounded by double quotes because they are contained within another string.

To retrieve a hidden data value:

MyValue = ExecuteExcel4Macro("MyValueName")
                      

Select all Open in new window

NOTE: the argument for the ExecuteExcel4Macro method is a string   it can either be text in single quotes or a VBA string variable.

To delete a hidden data item:

ExecuteExcel4Macro "SET.NAME(""MyValueName"")"
                      

Select all Open in new window

NOTE: compare this to the code above for writing a value; for deletion, the second argument is omitted.

It's important to keep in mind that Excel's hidden data is only held in memory and is unique to each Excel instance. This has several implications:
  If you want more information about the ExecuteExcel4Macro method and the hidden data store, don't bother with the Excel SDK. It doesn't even mention the capabilities described above. The only good source I've found is the incredibly valuable Excel website maintained by Chip Pearson. In particular, read his page on the hidden name space. Not only does this page show you how to use the hidden data store from VBA, C and even in spreadsheet formulae, but Chip has provided a set of really valuable VBA modules that make using the hidden data methods nearly foolproof.

I've provided code snippets to WRITE, READ and DELETE:

Sub WriteHiddenData()
                      ' creates and assigns a value to a data item in Excel's hidden data store
                      ' or
                      ' assigns a value to an existing named item in the hidden data store
                          Application.ExecuteExcel4Macro "SET.NAME(""MyValueName"", ""Value of my item"")"
                      End Sub
                      

Select all Open in new window

Sub ReadHiddenData()
                      ' reads a data value from Excel's hidden data store
                          Dim MyValue As String
                      '
                          MyValue = Application.ExecuteExcel4Macro("MyValueName")
                          MsgBox "From hidden data: " & MyValue
                      End Sub
                      

Select all Open in new window

Sub DeleteHiddenData()
                      ' deletes a data value from Excel's hidden data store
                          Dim MyValue As String
                      '
                          Application.ExecuteExcel4Macro "SET.NAME(""MyValueName"")"
                      '
                          ' the line below will produce an error because the
                          ' item no longer exists
                          MyValue = ExecuteExcel4Macro("MyValueName")
                          MsgBox MyValue
                      End Sub
                      

Select all Open in new window

To try a working example, download the files below then follow these three steps: Example----WRITE.xls   Example----READ.xls