In VBA, you can save/read data as/from binary file. This is particularly useful when you want to backup any intermedia data generated during your procedure runtime, or if you need to save configuration outside the excel workbook but you want to take care of reading the configuration files.
Function savedata(ByVal filefullName As String, ByRef data As Variant) Open filefullName For Binary Lock Read Write As #1 Put #1, , data Close #1 End Function Function saveconfig(ByVal filefullName As String, ByRef data As my_config) Open filefullName For Binary Lock Read Write As #1 Put #1, , data Close #1 End Function Function getdata(ByVal filefullName As String, ByRef data As Variant) Open filefullName For Binary Lock Read As #1 Get #1, , data Close #1 End Function Function getconfig(ByVal filefullName As String, ByRef data As my_config) Open filefullName For Binary Lock Read As 1 Get #1, , data Close #1 End Function
The above four function will save and load data for you(savedata() and getdata() are for Variant type data, saveconfig() and getconfig() are for User_Defined_Type), let’s see how we can use them:
Copy the above two function together with the following code into your VBA module:
Type my_config db_path As String password As String back_up_path As String some_other_config As Integer End Type Sub test_save_and_load() Dim str_var As String: str_var = "Hello!" Dim int_var As Integer: int_var = 169 Dim bol_var As Boolean: bol_var = True Dim arr_var As Variant Dim test As Variant Dim config As my_config Dim new_config As my_config Dim cell As Object Dim i As Integer 'create a large array i = 0 For Each cell In ThisWorkbook.Sheets(1).Range("A1:H50").Cells cell = i i = i + 1 Next arr_var = ThisWorkbook.Sheets(1).Range("A1:H50") Debug.Print arr_var(1, 7) config.back_up_path = "D:\Backup" config.db_path = "D:\DB" config.password = "a12356" config.some_other_config = "7" 'save data to binaryfile savedata "C:\TEMP\str.anyextension", str_var savedata "C:\TEMP\int.anyextension", int_var savedata "C:\TEMP\bol.anyextension", bol_var savedata "C:\TEMP\arr.anyextension", arr_var saveconfig "C:\TEMP\config.anyextension", config 'get data from binaryfile getdata "C:\TEMP\str.anyextension", test Debug.Print test getdata "C:\TEMP\int.anyextension", test Debug.Print test getdata "C:\TEMP\bol.anyextension", test Debug.Print test getdata "C:\TEMP\arr.anyextension", test Debug.Print test(1, 7) getconfig "C:\TEMP\config.anyextension", new_config Debug.Print new_config.password End Sub
Basically, the above procedure is trying to save data(string, integer, boolean, array) and a user_defined_type into binary.