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.

 

[shg]

Not knowing what type of data you're trying to store, I would still think that you could save it to a hidden sheet, even if that means including some ancillary information to restore the state.

Solver uses a simple method to store a model that may give you inspiration.

 

[xenou]

I don't believe vba has support for serialization of objects. You have to create your own persistence procedures, whether that means storage in a spreadsheet, database, or other means. VBA will not have as much class support as you are used to - its more procedural in nature. If you can, I'd recommend you work with Visual Studio Tools for Office to created solutions with .NET support - it will give you a wider base of .NET tools to work with.

 

[rizwindu]

Hi,

Thanks for your advice guys.

I would love to move to something like .NET, however my employer requires compatibility with Windows XP, Office 2003 and old software in general.

My aim was to avoid writing/reading information from cells during the execution of the actual program as it goes against my idea of best practice (i.e. someone can in theory come and edit what I've written to the cells before I read it back). However, if it has to be done then so be it. As an additional question along these lines, is it possible to automatically iterate through all the properties a class has i.e.

For Each property in Class
' Write the property somewhere
Next

Would make it very easy to write the details of the class somewhere, rather than writing ~30 individual statements (for that is how many properties my class has) and make sure that I remember to write new ones if the class gets updated in the future. From my brief digging I can't find an obvious 'yes' answer to this, so fear it may be a little bit of wishful thinking.

Thanks,

Ben.

 

[xenou]

That's a good question - not sure that I've seen it done before and offhand can't think of how. If it was a custom class then I suppose you could create a "serialization" method to capture all the properties - awkward but it wouldn't take too long.

Excel is a little free form in that you are constantly faced with all the things a user might do to "screw things up". Among the endless examples of this are changing/moving cells, inserting/deleting rows, deleting or renaming worksheets, as well as typing in any kind of data into a cell - text/numbers/dates. It's a real art, or science, learning how to deal with all of this. It's easier if users are nearby, few in number, and can be trusted or educated to use the workbook as intended. Then you can do less "defensive" coding.

With regard to storing data on worksheets, you can hide a worksheet (Excel has a property call Very Hidden which means the hidden sheet doesn't show up in the hide/unhide sheet dialog. Only a user with some knowledge of VBA is likely to find them, but then they are hopefully also users who understand there is a reason the sheet is hidden and will leave it alone.

ξ

 

[Norie]

Ben

VBA doesn't really 'do' objects or classes in the way you want, definitely not in the way something like Java does anyway.

I don't quite see the problem about somebody changing the data because you've stored it in a worksheet.

Couldn't they change the data in an 'object'?

Also, you don't need to store data in a worksheet and even if you did you could protect it to stop unauthorised changes.

Mind you Excel's security isn't very secure.

PS I was interested by the .NET thing. I've only used it in Vista and Win7 but with various versions of Excel etc.

I can't recall any major compatibility issues.

 

[xenou]

I was vaguely thinking that .NET would be a nice "fit" to Java programmers - but that's probably only due to surface similarities in C# syntax and Java syntax. I don't think there's issues with compatibility either - but VBA is a very quick entry point into Excel programming (namely, Open Excel - start coding )

 

[shg]

There is no "Properties" collection of Excel objects that you could iterate through. You could do something like this:

Sub x()
    Dim rOut        As Range
    Dim vProp       As Variant
 
    With Range("A1")
        Set rOut = .Cells
        For Each vProp In Array("Address", "Value", "Text", "Locked", "ColumnWidth")
            Set rOut = rOut.Offset(1)
            rOut.Resize(, 2).Value = Array(vProp, CallByName(.Cells, vProp, VbGet))
        Next vProp
    End With
End Sub

 

[rizwindu]

There is no "Properties" collection of Excel objects that you could iterate through. You could do something like this:

Sub x()
    Dim rOut        As Range
    Dim vProp       As Variant
 
    With Range("A1")
        Set rOut = .Cells
        For Each vProp In Array("Address", "Value", "Text", "Locked", "ColumnWidth")
            Set rOut = rOut.Offset(1)
            rOut.Resize(, 2).Value = Array(vProp, CallByName(.Cells, vProp, VbGet))
        Next vProp
    End With
End Sub

I don't quite understand what this code is aiming to do, or how it is supposed to work. Could you give me an insight into it? I'm far from an expert Excel programmer.

 

[shg]

It was responsive to this:

... is it possible to automatically iterate through all the properties a class has i.e.

For Each property in Class
' Write the property somewhere
Next

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