vba Excel - passing public variables

  Chris the Ancient 21:02 30 Nov 2003

I have found what, to me, is an unusual problem!

I can pass public declared boolean flags between procedures with absolutely no hassle.


In Excel, the Sub Auto_Close() procedure seems to set any of these flags back to 'False' as the sub proc starts running - no matter what I do! And I need them to stay true :o(

Has anyone had/cured/got round this problem?


  VoG II 21:46 30 Nov 2003

I've not come across this but - as far as I can recollect - I've never tried using public variables in an Auto_Close routine. I only use Auto_Close to delete custom toolbars for example, and they are referenced by name rather than variables.

A workaround that might be feasible - depending on how many of these variables there are - would be to write their values to a (hidden) worksheet each time they are set. Then the Auto_Close routine could read the values from that sheet.

  Chris the Ancient 22:07 30 Nov 2003

With you on that..

However, in this case, my Auto_Close has...

a dialog box to allow for saves or not

reset all toolbars etc.

ThisWorkbook.Saved = True


I did it all this way because I found that if I allowed the standard Excel 'Save Changes?' to run, the toolbar resets had already taken place and by cancelling, a user could get into bits they shouldn't.

In this particular case, I'm using a password system that I generated and an error trap that if the wrong password is input too many times, the application should quit without the option of the designed save dialog box.

It looks as though I may have to do a quite serious redesign in this area if I can't find a way!

Hope that all makes sense after a couple of glasses of falling down water!


  VoG II 22:14 30 Nov 2003

I'm assuming that you guessed I'd been partaking of the falling down water.

I think that when the user clicks the X all variables are set to their defaults - i.e. 0, "", or "False" for example. I cannot see an alternative to writing the variables to a sheet as they are set.

  pc moron 22:50 30 Nov 2003

As Vog says, the only way to preserve information is to write it to a worksheet.

When a workbook is opened all the VBA variables are initialised to their default state- False for boolean values.

Chris, don't use the Auto_close procedure- use one of the following event procedures instead:



These event procedures will allow Excel's default Save Changes Message Box to function normally i.e. any custom toolbars will remain in place and won't be removed until the workbook/worksheet is actually deactivated.

With your current set-up, what happens when a User presses Ctrl+F6 to cycle through the open workbooks?

  Chris the Ancient 23:08 30 Nov 2003

What an interesting situation. Even through the bottom of a glass darkly.

VoG... I was guessing that that might be what was happening - darn it.

pc moron... I shall try your route tomorrow after work and see where I can go there. Another new idea for me is the deactivate one. Also, I've never tried cycling through open workbooks! Hmmm...

But I guess we'll never find a foolproof way of making a spreadsheet totally tamper-proof. At least with the one I'm doing, they'll only get part way into the Auto_Open() before they come across the password block, and the vba is protected. And that, for me, is a fairly important bit.

If, though, someone really wants to muck about, they'll always find a way!

Shall close the thread and toddle off to my beddy-byes.


  Chris the Ancient 23:15 30 Nov 2003

I cheated a bit and also posted the same thread at click here.

This idea has been proposed using Before_Close()
However, it will have to wit until tomorrow!

The other thread reply went...

I got my global variable staying True in the following set up..

A. In ThisWorkBook Object I declared the Variable AND coded the Before_Close Event

Public GLB_TestVariable As Boolean

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox GLB_TestVariable
Response = MsgBox("Close", vbQuestion + vbOKCancel, "Close ?")
If Response = vbCancel Then Cancel = True
End Sub

B. In a module I place the following Code that First Sets the Global Variable to true and then closes the workbook.

Public Sub TestClose()
ThisWorkbook.GLB_TestVariable = True
End Sub

C. When the Workbook begins to close the Before_Close Event brought up a message box indicating that the Global Variable was still true.

VoG and pc moron...

Your thoughts on this idea would be received in a most grateful manner!


  pc moron 03:12 01 Dec 2003

Sorry, I misunderstood, I thought you were trying to preserve variables for the next time the workbook was opened.

Now I see what you're trying to achive, I fully agree with the response from mrexcel.

Try this:

Paste the following two procedures into a module and setup buttons on the worksheet to call them.

Option Explicit

Public TrueFalse As Boolean

Sub TrueFalseEqualsTrue()

TrueFalse = True

End Sub

Sub TrueFalseEqualsFalse()

TrueFalse = False

End Sub

Select Workbook in the VBA Project Pane and paste this procedure in:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If TrueFalse = False Then Cancel = True

End Sub

While TrueFalse is false you can't close the workbook.

  Chris the Ancient 09:24 01 Dec 2003

Don't you sleep?

An interesting routine! I like the concepts.

The big thing that that has come out of all this is that I had forgotten completely about the BeforeClose() availability. I did try this a v-e-r-y long time ago when I was trying to find a way of offering a chance of saving before the toolbars etc were reset. Then I completely forgot about it. But with my brain, that's easy ;o)

Today is a 'bitty' one, so it will probably be well into this evening before I get a chance to have a go. But I will!

Again, many thanks for the time and effort that you put in to help me out in my fumblings.


  Chris the Ancient 17:17 01 Dec 2003

Guess where I had been going wrong in the past with my use of Workbook_BeforeClose()...

I had been doing it as a module rather than as a Workbook Procedure! No wonder I could never get it to work properly.

Now I've done all that PROPERLY and put things in the right place, I've got another rather swish working procedure. And the public variable passes across to the BeforeClose module very nicely.

Thanks for all the help, folks, I'd be lost without you. And an extra thanks to pc moron for persevering at 3:00 am!


  pc moron 23:59 01 Dec 2003

Glad you've got it sorted.

I'm currently working some odd hours so I'll be putting in another semi-nightshift.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Best phone camera 2017

Stunning new film posters by Hattie Stewart, Joe Cruz & more

iPad Pro 10.5in (2017) review

28 astuces pour profiter au mieux de votre iPhone