2

I read a while ago that I can use Excel Memory to store the Value of a Variable using VBA.

That value would stay there even if I close all workbooks but leave the application open.

I cant remember how to do it and cant find the webpage with the info.

(Please avoid to suggest storing values on an excel hidden sheet.)

Improve this question
2
  • 1
    Create a Public variable you mean? Commented May 12, 2015 at 13:54
  • No, the value stays there even if there are no modules. Commented May 12, 2015 at 13:58

1 Answer 1

Reset to default
2

In Windows, a window maintains a Property List, which can contain string values and numeric data associated with each string value. You can add string values and associated numeric data to a window's Property List using the SetProp API function. The window's Property List is maintained as long as the window exists. The GetProp API function is used to retrieve the value of an existing element in the window's Property List. By storing string values and associated numeric data in the main Excel application window's Property List, you can create named values that will exist as long as Excel is open. The variable will be destroyed with Excel itself shuts down. Since these values are stored with the main Excel application's window, they will exist as long as Excel is open, regardless of what workbooks you open and close, including the workbook that created the variable.

I knew I'd heard this before somewhere: http://www.cpearson.com/excel/trulyglobalvariables.htm

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

The link provided guided me to the page I was looking for.. Hidden Name Spaces - cpearson.com/excel/hidden.htm

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.