• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 61
  • Last Modified:

Reinitiate all vba variables in excel file without closing the file

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
Kelvin4
Asked:
Kelvin4
  • 2
1 Solution
 
SimonCommented:
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
 
Saqib Husain, SyedEngineerCommented:
I am not sure but I think that the

End

statement can do what you want. Give it a try.
0
 
Kelvin4Author Commented:
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
 
Kelvin4Author Commented:
Thanks!
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now