Solved

Reinitiate all vba variables in excel file without closing the file

Posted on 2015-02-03
4
53 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 500 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

777 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