4

When my project starts, the Workbook_Open event initializes several structures and instantiates variables. During the run state, the user can change the values of these variables and structures.

When the user closes the project, I want to somehow save the value of these variables (save the current state), so the user can reopen the project and run from the last saved state before closing.

At this stage, even if excel is saved, when the project is reopened, the variables and structures are reset because of the Workbook_Open event.

UPDATE

I've investigated using a hidden sheet and saving values to cells, but I don't know if this is really feasible for my data.

The data I want to save are dictionaries of objects. These objects in turn may contain other objects are single-valued data. If I opt to use a hidden sheet, I would have to save each and every single value of data in a cell, which would be tedious to write and read back if I want to preserve the ordering of items in my dictionary.

I am wondering if there is a way to export the data for permanent storage onto the disk, and a way to import back the data? Please let me know of alternate methods or if my understanding is lacking.

UPDATE 2

Could XML offer me a viable solution? What is the best strategy for writing my collections and objects to disk and reimporting them later?

Community
  • 1
  • 1
Ehudz
  • 613
  • 10
  • 22
  • 33

2 Answers2

2

You'll have to decide on a persistence strategy.. I've used a hidden workbook, a SQL connection, an .ini file, or even a flat text file in different situations. Then in your Workbook_Open, you'll have to load the last saved data into all of your variables/structures.

Andy Raddatz
  • 2,792
  • 1
  • 27
  • 29
1

Like I mentioned in my comment, you can store the variables in a hidden sheet. See this example. I am storing the variable in Sheets("Temp"). Change as applicable in the code

In ThisWorkbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Sheets("Temp").Range("A1").Value = MyVariable
End Sub

Private Sub Workbook_Open()
    MyVariable = Sheets("Temp").Range("A1").Value
    MsgBox MyVariable
End Sub

In a Module

Public MyVariable As Long

'~~> Some operation that you do with the variable
Sub Sample()
    MyVariable = MyVariable + 1
End Sub

Now every time you open the workbook, your variable will be set to the last value.

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • Thanks! I also have some dictionary objects and collections that I would like to save. If I use the same method of using an external sheet, how would I save these objects? – Ehudz Aug 08 '12 at 17:08
  • Reserve a column for your collection and store the data there by looping through the items of the collection. Same goes with dict. Give it a try and if you get stuck lemme know and I will give you an example? – Siddharth Rout Aug 09 '12 at 00:39
  • the problem I am foreseeing is - my dictionary contain objects and these objects also contain collectiond. how would I save objects and their properties...and so forth? – Ehudz Aug 09 '12 at 02:04
  • I was thinking...since each worksheet is an object...I could try and save each of my variables and structures in the code of this object, without using the spreadsheet? – Ehudz Aug 10 '12 at 15:30
  • Well, I cannot confirm anything till the time you give me an example of what you are trying to do... – Siddharth Rout Aug 10 '12 at 16:15
  • Ok, I've done some research but still cannot find a good strategy. See update above – Ehudz Aug 27 '12 at 14:33