TAKEAWAYS
Visual Basic for Applications (VBA) in Excel is a programming language that enables users to automate tasks, customise workflows, and create functions tailored to specific needs. It’s particularly useful for accountants who deal with repetitive tasks like data manipulation, report generation, and financial statement preparation. With VBA, manual effort is reduced, allowing users to improve efficiency and accuracy.
In this article, we’ll explore a simple VBA use case: exporting a report from a working file.
A typical working file starts with raw data, undergoes complex computations, and ends with a final output in a separate worksheet. Sharing the full file can expose unnecessary data and computation logics.
The “final mile” here refers to the step of decoupling the output or report worksheet from the working file for distribution. This involves removing formula linkages to ensure that the final report is independent of the original data and calculations. While performing this task once a month may not take much time, doing it repeatedly can quickly become tedious and error-prone. Automating this process with VBA helps avoid mistakes, saves time, and ensures the report is properly formatted and ready for distribution.
If you do not see the Developer tab in your Excel menu, follow these steps to unhide it:
1. Click on the File tab.
2. Select Options at the bottom of the menu
3. In the Excel Options window, choose Customize Ribbon.
4. On the right side, under Main Tabs, tick the box next to Developer.
5. Click OK.
The Developer tab should now be visible in your Excel ribbon.
To programme a macro, you can write the VBA codes from scratch, use AI to help, or simply do a macro recording. My journey into VBA coding began with macro recording, which I believe is the same for many Excel users who don’t have a technical background.
For a beginner, the process typically follows these steps:
1. Plan: It is important to plan your steps before you click the recording button, so you don’t record unnecessary actions. First, clearly outline what you want to automate. Break it down into specific steps or actions. Then, do a quick run-through of the tasks manually to ensure you know the exact steps required, and to avoid mistakes during recording.
2. Record: Once you’re confident with the process, hit the Record Macro button and perform the actions efficiently.
3. Review the code: Once the recording is done, you can review the code generated by Excel.
4. Amend if necessary: The recorded code may not always be perfect or fully optimised. This is where you can make adjustments to improve efficiency, remove unnecessary lines, or add new functionality. You can also enhance the recorded macro by adding conditions, loops, or error-handling logic.
This process allows you to start automating simple tasks, even if you do not have a programming background. Over time, you will gain more confidence to edit and improve your VBA code.
1. Go to the Developer tab, click on Record Macro.
2. Give your macro a name in the Macro name field.
3. (Optional) You can assign a shortcut key to the macro by entering a letter in the Shortcut key field.
4. Click OK to start recording.
From this moment onwards, your actions in Excel will be recorded, including scrolling down the worksheet.
1. Select the range of cells: Start by selecting the data you want to copy and distribute. This could be a specific table or a range of cells in your current workbook that you want to transfer.
2. Copy the range: Once the range is selected, copy it using Ctrl + C or by right-clicking and selecting Copy. This copies both the data and its formatting.
3. Add a new workbook: Open a new workbook to paste the copied range. You can do this by pressing Ctrl + N or by selecting File > New.
4. Paste column widths: First, paste the column widths to maintain the layout of the original data. Go to the new workbook, right-click where you want to paste, and select Paste Special > Column Widths.
5. Paste format: Next, paste the format of the copied range, such as colours, borders, fonts, etc. Right-click again and select Paste Special > Formats.
6. Paste value: Finally, paste just the values of the data to ensure that no formulas or links to the original workbook are carried over. Again, right-click and select Paste Special > Values.
7. Stop the recording: Go to the Developer tab and click Stop Recording. This will finalise your macro and save the steps you performed, so you can reuse them anytime.
This method ensures that the data in the new workbook is formatted identically to the original and has only the required values, without any unnecessary links or formulas from the source file.
To review the recorded macro codes, follow these steps:
1. Go to the Developer tab, click on Macros.
2. Select the macro you just recorded.
3. Click Edit.
This will open up the VBA Editor:
This is what I have. How about you?
VBA codes are very readable. Even if you have no prior programming knowledge, it would not be too difficult to understand what these codes are trying to do. It is written in plain English, though the grammar may seem a bit strange.
The last four lines of my code are about selecting cell B9 and a bit of scrolling, then selecting cell B33 and scrolling up. These are not necessary. I will delete them.
The code should work, but if you want to make it more flexible, consider this.
Sub ExportSheet()
Dim newWorkbook As Workbook
' Force calculation on the current sheet
ActiveSheet.Calculate
' Copy the used range of the current sheet
ActiveSheet.UsedRange.Copy
' Add a new workbook
Set newWorkbook = Workbooks.Add
' Copy column widths
newWorkbook.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
' Copy formats
newWorkbook.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteFormats
' Copy values
newWorkbook.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues
' Clear the clipboard
Application.CutCopyMode = False
End Sub
Those texts behind single quotation marks are comments. They are a good and recommended way to explain what the code does without affecting the execution. Adding comments helps others (and your future self) understand the code better and maintain it more easily. It’s a best practice, especially for complex code.
Recorded macros in Excel are easy to create but have limitations. They produce inefficient, linear code, capturing unnecessary actions, and lack flexibility to handle dynamic data. Since they cannot use control structures like loops or If statements, recorded macros follow a strict sequence, making them unsuitable for complex tasks requiring decisions or repetition. Additionally, they lack error handling and are harder to maintain or optimise.
Efficiency in the long term starts with a small investment today. There are many resources available to empower you to enhance work efficiency with VBA – from classes to forums and documentation – helping you transition from manual tasks to automation.
Have you read my article “Streamline Consolidation With Power Query”? Power Query, a function in Microsoft Excel, allows users to create connections between a workbook and various data sources through an application programming interface (API). Once the connection is defined, any changes to the data can be updated simply by clicking the refresh button in Excel to repull the data.
Ng Xian Hui, CA (Singapore), is Founder of Backbone Pte Ltd.
© 2024 Institute of Singapore Chartered Accountants