Sometimes VBA algorithms take a long time to execute and you would want to be able to maintain those calculations in memory when you close and reopen you Excel Workbook or Access Database. Why not save your VBA project data and recover it when reopening your VBA project? Actually you can do just that…
Saving VBA data to binary files
The trick to saving your VBA workspace variables is dump them into a binary file. This is not as complex as it seems, and if you are reading this post you probably already have mastered reading/writing data to text files. Binary files as opposed to text files can store any data type(s), while text files can only contain strings (you might try dumping data as strings and then parsing them, but its not exactly a pleasant job).
If you stumbled across my post on Writing files in VBA (the proper way!) you might be aware of how simple saving data to a binary file is (in this case the “testVar” Integer variable):
Dim fileName As String, fileNo As Integer, testVar As Integer testVar = 4 fileNo = FreeFile Open fileName For Binary Lock Read Write As #fileNo Put #fileNo, , testVar Close #fileNo
Reading the variable back to your VBA project is equally simple:
Dim fileName As String, fileNo As Integer, testVar As Integer fileNo = FreeFile Open fileName For Binary Lock Read As #fileNo Get #fileNo, , testVar Close #fileNo
I hope this demonstrates how easily you can dump your VBA project variables to a binary file and then recover them.
The proper way to dump and recover your VBA data
Now the example above will work fairly well if your variables are of constant byte-length. For example an Integer will require 2 bytes, a Double 8 bytes… No problems yet right? Well issues start when you start dumping Objects and/or Strings. Why? Strings for example are simply a sequence of Characters (bytes). Therefore the string “Hello” will require 5 bytes of space, while “Hello World” will use 11 bytes of space. If you think you can manage this by preceding your strings with an Integer header – you are right. But you will have much more issues with dynamic arrays, Classes and other objects which will require additional headers.
How to manage your VBA project variables then? Encapsulate them using the Type statement. When dumping Types to binary files, the object will include a header with all the information needed to recover the whole Type back to your VBA project. Making a second attempt at the code above, your final methods should look like this:
Type TestType intVar As Integer strVar As String End Type Sub SaveVBAVariables() Dim fileName As String, fileNo As Integer, testVar As TestType '...Some code here... testVar.intVar = 4 testVar.strVar = "Hello!" '........ 'Now save all testVar variables to a binary file fileName = "C:test.bin" fileNo = FreeFile Open fileName For Binary Lock Read Write As #fileNo Put #fileNo, , testVar Close #fileNo End Sub Sub ReadVBAVariables() 'Read the testVar variables back to your VBA project Dim fileName As String, fileNo As Integer, testVar As TestType fileName = "C:test.bin" fileNo = FreeFile Open fileName For Binary Lock Read As #fileNo Get #fileNo, , testVar 'Recovered the TestType to testVar Close #fileNo End Sub
The SaveVBAVariables procedure demonstrates how you can create and work with your variables, and
save your results to a binary file.
The ReadVBAVariables procedure can be executed even after you close and reopen the workbook – recovering your Test data type along with all enclosed variables.