Solved

Reinitiate all vba variables in excel file without closing the file

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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.

912 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now