Thread: VBA - Memorizing values of variables

Hi everybody,

In my VBA code, I have set that when the excel is opened (Private sub Workbook_open), VBA code changes some values in a few cells. However, I want that before closing excel (Private sub Workbook_BeforeClose(Cancel As Boolean)) values in those cells would be changed back to their original values. I tried to use global variable (Public Back as Boolean). When excel is opened the value of this variable would be set to True if VBA code had to change some values in excel on opening the file. And before closing workbook, VBA code would check if the value of variable Back is equal to True - in that case VBA code would change values to their original. But it appears that after VBA code in Private sub Workbook_open is run excel doesn't memorize the value of variable Back although it is a global variable.

Does anyone know how to make VBA code memorize values?

Hello walduxas,

Global VBA variables retain their values only for as long as VBA is running. To retain values between openings of a workbook, you would need to save the variables to a file or to a worksheet in the workbook. The easiest method is to add a worksheet and make it "very hidden". This will hide the sheet the user and only be accesible through VBA code. You store the values on this sheet before closing and restore the values when the workbook is reopened.