Persistent user-defined object and serialisation

[rizwindu]

Hi all,

I hope you can help me and that the wording of my problem makes sense to you.

I'm creating a worksheet that has a form where the user inputs some information. The user closes the form and in doing so it creates a new object of a class I have defined and stores the information in the class. A number of calculations are then performed using data from the class and the results written to a worksheet. If the user is happy with the results of the calculations they can decide to save the data, or re-edit the information they inputted. In the future I'd like them to be able to open the workbook and retrieve the data they have stored.

I have a couple of questions.

A) Where can I store the object that is created so that it can be recalled when the user either re-edits the inputs or stores the results? In theory I could just 'hide' the form rather than unload it and reference the object as form.object (or similar). However, this doesn't seem very neat to me and doesn't solve the problem of retrieving the data at a later date when the form isn't open in the first place.

I tried the simple solution of just saving my object to a cell reference, hoping that VBA was nice and clever, but it isn't and that didn't work.

It may be possible to write out the value of each individual class property to a series of hidden cells and then reload them if necessary. However, this seems rather crude and although would work for the current problem, is not a universal solution if I were to encounter this in the future (i.e. would not work if the object contained references to other objects whose values can't just be written to a cell).

B) If I were using Java I would just use implement the Java.io.serializable interface and write the object somewhere. It could then be recalled whenever I needed it. Is this possible in Excel somehow? This would be an ideal solution as I could write each object to a hidden cell and it would allow me to easily store lots of objects and keep them persistent each time the workbook is loaded.

Any advice on solving this problem would be much appreciated.

Thanks in advance,

Ben.

 

[rizwindu]

It was responsive to this:


It's just listing a few properties of cell A1 below it.

I see, it is the use of the 'CallByName' function I didn't quite get. Looked this up, understand now. Am thinking I will use a simpler version to output a different property into adjacent cells (for ease of reading/required for some backwards compatibility).

Thanks for the input.

Ben.

 

[rizwindu]

OK, one (hopefully) last question.

Using the code suggested above I have produce an 'objWrite' function that writes the value of each property of my class to a set of cells in a worksheet.

Something like this:

public function writeObj()
  classProperties = Array("Name","Age","Address")
  With Worksheest("Sheet 1")
    For i = LBound(classProperties) to UBound(classProperties)
      vProp = classProperties(i)
      .Cells(1,i+1).value = CallByName(Me, vProp, VbGet)
    Next
  End With
End Function

The question is, how do I do the reverse and read the values back into an object? I was hoping that I could just change the line that does the writing around into

CallByName(Me,vProp,VbLet) = .Cells(1,i+1)

But this doesn't work.

I've had a quick look around the internet, but haven't found the solution yet. Any input would be much appreciated.

Thanks (again),

Ben.

 

[RoryA]

Try:

CallByName Me, vProp, VbLet, .Cells(1, i+1).Value

 

[rizwindu]

Thanks, appreciate the speedy response.

Ben.