Tag Archives: save

vba data

Saving your VBA data (VBA data dump)

1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 3.67 out of 5)
Loading...

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

save vba dataThe 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.

save lync conversations

Automatically save Lync conversations (when feature is blocked)

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Honestly, I cannot seem to explain why do some corporations block the Save IM conversations in my email Conversation History folder. What is more funny is that blocking this feature (at least in Ms Lync 2013) will not prevent you from saving your conversations manually (hit CTRL + S).

Automatic conversation saving feature - disabled by some corporations
Automatic conversation saving feature – disabled by some corporations

Unfortunately there is no way to somehow manually enable this option and so the only way around this for me was developing my own MS Lync conversation logger.

The Lync Logger – save Lync conversations

Save Lync conversations: LoggerThe Lync Logger is simple app that requires MS Lync 2013 (not tested with 2010) and at least .NET 4.5 installed. It runs in the background as a notification icon listening for MS Lync IM conversations. Once a conversation concludes the entire contents as saved as an MS Excel file where each row represents a single message of the conversation.

All files are saved in the C:UsersUSERNAMEAppDataRoamingLyncLogger folder which can be opened at any time when you right-click on the LyncLogger notification icon and select “Open log folder”.

Installation

Download the ZIP from here:


Unpack the contents to any preferred location. Next create a link to the “LyncLogger.exe” file in your Windows Start – “Startup” folder. That is it!

The app is licensed under a standard GNU Public Licence.

Features

Once you run the LyncLogger it will appear as an icon in your taskbar notification area:
Save lync conversations: Notification
The app will run logging all your conversation automatically.

By right-clicking on the icon you will see the following options:

  • Open log folder – will open the C:\Users\USERNAME\AppData\Roaming\LyncLogger containing all the logs;
  • How to Search – a short explanation of how to turn on file indexing in the folder above to easily search through the contents of all you conversations
  • Exit – closes the app
vba data

Save and Get Excel VBA settings

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 4.50 out of 5)
Loading...

Ever had an issue of sharing data (VBA settings) between your workbooks? Not many Excel VBA users know that VBA allows you to easily save and certain variables to and from the Windows Registry, just as if setting permanent VBA settings within your VBA Project. What is more this is already built into VBA that is why you do not have to reference any external libraries or functions. When is this useful:

  • Saving VBA settings of Excel Add-Ins – when using some VBA variables that you would like to save permanently for the user, and you would like to be able to get them at any time. Usually this applies only to saving configurations.
  • Sharing VBA data between workbooks – say you have multiple Excel workbooks which share some simple data fields e.g. certain variables, settings etc. Saving them into the registry is sometimes more convenient especially if the location of all workbooks can change over time.
  • Sharing VBA data between Excel and other applications (MS Office but not only) – technically it should be possible to get these settings from any other MS Office application e.g. MS Access or MS Word.

Source code – Saving VBA Settings to registry

Global myVariable as Integer
Sub GetMyVariable
  myVariable = GetSetting("MyApplication", "MySettings", "myVariable", "0")
End Sub
Sub SaveMyVariable(newValue as Integer)
  call SaveSetting("MyApplication", "MySettings", "myVariable", newValue)
End Sub

Simple right?

See more information on Getting and Saving variables in the Windows Registry at http://msdn.microsoft.com/en-us/library/kb0c3wb9(v=vs.90).aspx