Create a Logbook For Your Excel Workbook Using Simple VBA Code
After my last story was published on freeCodeCamp two weeks ago, it is time for a new tutorial using the most dreaded programming language of the world again.
If you are working in a team and have multiple members using the same set of documents, it might become handy to maintain a worksheet that registers the people opening or closing a certain workbook. Although version control might help you a lot already in certain cloud platforms, such as OneDrive, VBA could help you store other information, which might be more relevant to you.
I would like to keep this explainer as short and easy as possible to show how you could use simple VBA code for the most simple tasks you would not imagine using the language for. To follow this tutorial, I do not expect you to have a any prior experience using VBA or any other programming languages, but it might be easier to understand certain aspects.
Preparing Our Workbook
We will start by opening a new workbook, renaming the initial worksheet to ‘Logbook’ and defining some headers for our logbook, which are shown in Figure 01.
In this tutorial, I will show you how to store the date, user and action (whether the workbook has either been closed or opened), but feel free to add your own headers and data at the end of the tutorial with your new superpowers.
Developer Tab
Before we will start writing code, it is important to get the Developer tab available in your Excel window. A quick heads-up on how to get these functions available in the ribbon is available in the article linked in the beginning of this article under the subtitle ‘How to Build a Macro with Visual Basic for Applications’.
Writing VBA Code
Once you have that ready and the Visual Basic editor open, it is time to start writing some code.
In order to start writing a macro, you will always start with the Sub keyword, which is short for subroutine and defines the beginning of a function. If you then enter a name for your macro, end with two parentheses and press the enter key, the editor will conclude the subroutine with the words ‘End Sub’.
The macro will only start doing something after we have entered some code within the two Sub keywords, so let’s do that now.
First of all, I would like to add another row in our Logbook worksheet above the second row. This might not be needed with the initial log, but will prevent you from overriding the data with later insertions.
To do so, we can call the Rows object that live on the Worksheets Object and then call the Insert method, which be default will just add one extra row above the row number specified in the Rows object.
Now that we have a clear row, we could start adding the actual information. To change the value of a cell, you could directly use the Range object, which takes a cell or multiple cells as a parameter.
As the Value property is the default property of the Range object, it is not needed to enter this as well. But for your convenience, please understand that the two lines of code will do exactly the same.
The Now function is built-in within Excel and will return the current date and time of your device. This timestamp is then used as the value for the next cell in the column.
If we would now run this macro, by clicking the green arrow while having the cursor somewhere waiting in your subroutine, you will see the data being inserted in your Logbook worksheet.
Adding the Action Data
But hey, we also defined an action header before, why do we not use that? Let me explain.
At this point a user would have to run the macro manually before the data is actually added to the screen. Though, to prevent people from forgetting this, Excel thought of something clever. They came up with a functions which are automatically executed upon opening and closing the workbook.
Although they look like regular functions, the two macros below will be executed on opening and closing the workbook, based on their name.
Please note that the latter includes a parameter. Despite that we will not be using it for now, it is important to leave it in as your program will crash without.
Apart from adding the action by using a hard coded string, not that much has changed to our macro.
For the closing macro, we have also added the Save method. This saves the workbook and is a method that lives on the ThisWorkbook object. Though just as with the Value property before, the ThisWorkbook is the default which means there is no need to include that in our code. Leaving out the Save keyword would prompt the user with the question whether they would actually like to save their changes, which we want disregard their preference to track the usage of the workbook.
If you would now close and re-open the workbook again, two new lines should be inserted with the timestamp, your username and whether you opened or closed the document.
Refactor Using a VBA Function
The code above should be easy to follow, even if you have never coded before. If you have though, you could argue that we are repeating ourselves quite a bit in both macros.
Just like in many other programming languages, we could therefore define a function that we could call in our macro. Might we ever want to change something to our macros, we will not need to do that at in several places, but in one only.
To define a new function, you use the Function rather than the Sub keyword. Name the new defined function to something you like, I am choosing for WriteLog. Might you hit enter again after the parentheses, Excel will finish your function definition with End Function.
In between these parentheses, define a parameter called Action with the string datatype. Copy the earlier used code in the function statement and replace the value for cell C2 to the parameter Action.
Now we have a function that we could use to insert a new log whereby the action that will be inserted depends on the string we give in as a parameter.
To give in a parameter when calling a function, you would name the parameter, followed by := and the value you would like to insert.
Conclusion
The code above will now be enough to insert a new row with the earlier defined information on every closing and opening of the workbook.
Congratulations, you have now made yourself an automated logbook in Excel using VBA!
Feel free to experiment with this code to add your own information instead and please do not forget to leave a bit of applause if you liked this tutorial.
