Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Reinitiate all vba variables in excel file without closing the file

Posted on 2015-02-03
4
Medium Priority
?
60 Views
Last Modified: 2016-02-10
Is it possible to re-initiate all Public variables (created by me in vba) in an excel file, so the file is returned to its status when it was originally opened?

My excel file carries out a repetitive process that creates new visio files with different content. Often I close the excel file between making each visio file. However, I would like sometimes to re-initiate the excel variables so I can create more than one visio file without closing excel.

To do this, I plan that the code in ThisWorkbook.Workbook_Open()  would be moved into std_Open(), which would be standard code module.

When  the file is first opened, std_Open would be called from ThisWorkbook.Workbook_Open() .

When the proposed re-initiation is carried out, it would be followed by executing the std_Open code, to simulate the opening of the file.

Is there a standard procedure for this requirement?

Kelvin
0
Comment
Question by:Kelvin4
  • 2
4 Comments
 
LVL 18

Accepted Solution

by:
Simon earned 2000 total points
ID: 40587008
I think standard would be to have a routine that you call to set the public variables to a known, desired state

e.g.
Function INIT() as boolean
myVar1="Microsoft"
myVar2=10.23
'etc etc
INIT=TRUE 'set the return value of the function
End Function

Then call that from your main routine, whether that is std_Open or Workbook_Open()
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40587022
I am not sure but I think that the

End

statement can do what you want. Give it a try.
0
 

Author Comment

by:Kelvin4
ID: 40587163
Thanks to Both:
The excel file contains over 100 public variables, so I rather hoped for a more compact solution than redim'ing them.

As regards the END statment, I saw this @ https://msdn.microsoft.com/en-us/library/0wt87xba.aspx

"You can place the End statement anywhere in a procedure to force the entire application to stop running. End closes any files opened with an Open statement and clears all the application's variables. The application closes as soon as there are no other programs holding references to its objects and none of its code is running."

I wonder if there is a function that can help me?
K
0
 

Author Closing Comment

by:Kelvin4
ID: 40590430
Thanks!
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question