Excel VBA: Variables resetting to 0 for some reason

Question

here's my current code:

Private Sub Workbook_Open()
sumn1 = Sheets("Main").Cells(1, 1).Value
sumn2 = Sheets("Main").Cells(2, 1).Value
sumn3 = Sheets("Main").Cells(3, 1).Value
sumn4 = Sheets("Main").Cells(1, 2).Value
sumn5 = Sheets("Main").Cells(2, 2).Value
sumn6 = Sheets("Main").Cells(3, 2).Value
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Sheets("Main").Cells(1, 1) = sumn1
    Sheets("Main").Cells(2, 1) = sumn2
    Sheets("Main").Cells(3, 1) = sumn3
    Sheets("Main").Cells(1, 2) = sumn4
    Sheets("Main").Cells(2, 2) = sumn5
    Sheets("Main").Cells(3, 2) = sumn6
End Sub

So, while the workbook is open and it's being worked in, after some actions the variables sumn1, sumn2..etc. (which are global variables) are getting added values, like +10 to sumn1, or +5 to sumn2 and so on. Since I want them saved after closing the workbook, I save them in a cell that I have hidden with ";;;".

The problem is, sometimes it works correctly, but sometimes (usually after longer time since workbook has been closed since) the variables reset to 0.

So first of, is my approach an alright one on how I am saving the data or its just not working because this approach is bad? If it's an alright approach then I guess I should search for my mistake somewhere else then.

Could you use a worksheet as the storage option rather than always referring to variables? Of course, this way could slow performance a small amount, but it would give you a much more consistent experience in terms of the values being stored. edit: Bathsheba beat me to it :) — jonifen
– jonifen, Commented May 6, 2016 at 12:14
Sounds like this bit of code is in one workbook "A" and the variable values are being saved in another workbook "B" without this VBA code. Is it possible that your workbook "B" is later opened without workbook "A"'s VBA and the cell with the variable moved? Then when you reopen the workbook "B" some other cells with different values have been moved into their place? — Forward Ed
– Forward Ed, Commented May 6, 2016 at 12:37

Bathsheba · Accepted Answer · 2016-05-06 12:12:37Z

1

All your variables are also reset to zero when "reset" is clicked within the VBA editor, or an "End" statement is reached. There could be other occasions too although I can't think of any. You are currently only guarding against the workbook being closed and reopened.

So your approach is brittle.

What you could do is remove the global variables altogether, and always refer to the worksheet for the setting and retrieval of their values.

answered May 6, 2016 at 12:12

Bathsheba

235k35 gold badges376 silver badges504 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

yk11 · Accepted Answer · 2016-05-06 13:06:43Z

You can improve your approach with a couple of helper routines

Dim gVars as Variant

Sub LoadVariables()
    ' Load data from s/s into variable
    gVars = Sheets("Main").Range(Cells(1, 1), Cells(3, 2)).Value
End sub

Sub SaveVariables()
    ' Save data from variable into variables
    Sheets("Main").Range(Cells(1, 1), Cells(3, 2)).Value = gVars
End sub

Then in your code use LoadVariables when you need to use the data (usually at the start of whatever sub needs it) and SaveVariables when you have updated the values. Obviously this needs more error handling, etc.

The loading of the entire variables set into a single array also makes it easier to maintain the code (less chance of typos) and handle iterations if needed.

Collectives™ on Stack Overflow

Excel VBA: Variables resetting to 0 for some reason

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related