<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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

Published on
27,081 Points
15,381 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
Comment
6 Comments
 

Administrative Comment

by:mbizup
Awarded Editors' Choice


mbizup
EE Page Editor
0
LVL 26

Expert Comment

by:redmondb
scott,

Many thanks. I had no more than a vague recollection of this functionality, so I did a verbatim search on Google for "excel hidden data storage" - you're no. 1!

Voted up.

Regards,
Brian.
0
LVL 31

Author Comment

by:Scott Helmers
I'm glad the article was useful, Brian -- and thanks for the vote!
0
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

LVL 18

Expert Comment

by:Simon
Good article. Seems I've come across Chip Pearson many a time during my time using VBA, but I'd never read about this particular feature.
0

Expert Comment

by:Amin Jinnah
I know this blog is old.

Scott If you can help me with this I'll appreciate.

I'm trying to pass values across different excel instances. Let me explain in detail so you'll get the whole idea what my purpose is.

- I have one excel file which asks for ID &  PW.
- If it matches,
----- it opens a new 2nd  excel instance
----- Opens the main WB in 2nd instance
----- Run WB Open macro. It's a very large macro
----- Then after 20 seconds in 1st  instance it closes ID & PW  file
----- Minimizes Application window in 1st  instance

This way if user open any other excel file, it would open in 1st excel instance and leave the 2nd instance untouched.

Now my problem:

I want to pass a string value from 1st instance to 2nd. Value is for 1st file's name & Path.
I know I can copy values from 1st to 2nd instance by code in 1st instance

Dim XL2nd As Excel.Application
Set XL2nd = New Excel.Application

const MainWB_NnP="C:\filepath\MainWB.xlsm"
const NameAndPath= "C:\filepath\filename.xlsm"

Set MainWB_NnP= XL2nd .Workbooks.Open MainWB_NnP

XL.Visible = True

MainWB_NnP.sheets("SheetName").Range("A1").Value = NameAndPath

But last line code in the 1st instance,
"MainWB_NnP.sheets("SheetName").Range("A1").Value = NameAndPath"
runs after 2nd instance's WBOpen macro finishes.
I want those values before 2nd instance file runs WB Open macro.

Now If I can give name to 1st excel instance, I can then put that line code in WBOpen macro of 2nd instance.
But how do I name current instance? AndI don't even know if that would work.

I'm open any other suggestion that would do the same.
Thanks
0
LVL 31

Author Comment

by:Scott Helmers
Amin -- I think you will receive much more help by posting this as a general question in the Excel and VBA topic areas (please include the Visio topic area also so I'm sure to see the question). When you do post the question, please include a link to this article so anyone reading the question will have this context. Thanks.
0

Featured Post

Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Join & Write a Comment

This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month