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.