Solved

Reinitiate all vba variables in excel file without closing the file

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

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…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

713 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