<

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

Published on
27,836 Points
16,136 Views
12 Endorsements
Last Modified:
Awarded
Editor's Choice
Community Pick
Scott Helmers
Visio enthusiast, teacher, and MVP. Co-inventor of TaskMap: process mapping for the rest of us. Avid cyclist. Father of two daughters.
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"")"

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")

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"")"

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 VBA code in one Excel instance creates hidden data, it is NOT accessible in another Excel instance.
If you quit the Excel instance that holds the data, the data disappears.
However, if you close the workbook that created the hidden data but don't close Excel, the data still exists.
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

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

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

Open in new window


To try a working example, download the files below then follow these three steps:
Open "Example -- READ.xls" and note that cell B7 is empty
Open "Example -- WRITE.xls" and run the WRITE macro contained in this workbook
Make "Example -- READ.xls" the active workbook and run the READ macro. Note that the value from the WRITE workbook appears in cell B7
Example----WRITE.xls  Example----READ.xls
12
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free