How Excel VBA Can Automate Your Accounting Tasks and Save You Time and Money
Last updated on April 27, 2023 By Victor Chan
Are you an accountant who spends hours on tedious and repetitive tasks in Excel? Do you wish there was a way to automate your accounting tasks and save time and money? If so, you may need to learn Excel VBA.
Excel VBA is a programming language that’s free and built right into Microsoft Excel. With VBA you can create macros and custom functions in Excel. Macros are sequences of commands that can perform complex tasks for you in a single click. Custom functions are formulas that you can create and use in your own worksheets. Like having your own powerful Excel power tools!
VBA stands for “Visual Basic for Applications”. It’s able to help you automate your accounting tasks such as data entry, reconciliation, reporting, and analysis. By automating these tasks, you can:
- reduce errors
- improve accuracy
- enhance quality
- increase productivity
You can also free up your time for more value-added activities such as strategic planning, decision making, and problem solving. Or so you could have a bit of fun!
In this article, we’ll show you how Excel VBA can automate accounting tasks and save you time and money. We’ll give you some sample VBA code to try out. And we’ll show you how to get your hands on our special Excel VBA course. It quickly teaches the skills you need to become a fluent Excel VBA user.
How Excel VBA Can Automate Your Accounting Tasks
There are probably millions of people around the world using Excel VBA in ways large and small. In all sorts of fields from aviation to zoology. Let’s hear how it can help with automating your accounting tasks. You’ll see that it’s like magic. But better!
Case Study 1: Monthly financial report creation
Meet Mike Jones. Every month, he has to create a financial report that summarizes the income statement, balance sheet, and cash flow statement of the company. Sounds time consuming, right? Not for Mike. He uses Excel VBA to automate most of the process, and make his life easier.
"Hi, I’m Mike, an accountant who works for a manufacturing company. Every month, I have to create a financial report that shows the income statement, balance sheet, and cash flow statement of the company."
Let's hear how Mike uses Excel VBA for monthly financial report creation.
"It’s a big responsibility, because the report has to be accurate, consistent, and follow all the relevant GAAP standards. It also has charts and graphs to show how the company is doing financially.
Before I started using Excel VBA, creating the monthly financial report was a nightmare. I had to manually import data from different sources into a worksheet. Then I had to format and style the data by hand, making sure it followed the GAAP standards.
Then I had to create and update charts and graphs by myself, which was time-consuming and tedious. And I had to print and save the report as a PDF file. It took me hours to finish the report, and sometimes I made mistakes or missed deadlines. It was stressful and frustrating.
To make my job easier and faster, I took the leap and started to use Excel VBA to automate the whole process.
I started with a macro to import data from different sources like CSV files, databases, etc. into a worksheet. Then I wrote another macro to format and style the data. Next, I put together a macro to update charts based on the data. And finally, I found and adapted a macro to print and save the report as a PDF file.
With Excel VBA, I can create the first draft of my monthly financial report in less than an hour. The report is accurate, consistent, and compliant with GAAP. It has charts showing the financial performance of the company.
The report is always well received by my manager as it helps to reduce the time to month-end close. In fact he’s so impressed, I’m now promised a bonus and raise."
Case Study 2: Revenue cycle analysis and dashboard
Now let’s meet Jenna Lee. She’s an accounting consultant who works with various clients in different industries. She loves to crunch numbers. One of her clients is a healthcare company that wanted to analyze their revenue cycle and identify areas of improvement. Let’s hear from Jenna on how she used Excel VBA.
"Hi! I’m Jenna. I’m an accounting consultant who’s also a mom of two kids. We loved the TV show Wednesday. Saw all of season 1… creepy and funny! Plus the actress who plays Wednesday is also called Jenna, so that’s a bonus. I’m also a fan of classical music, and I love how they had Wednesday play Paint It Black on the cello. Anyway, back to accounting!"
Here's what Jenna has to say about using Excel VBA:
"One of my clients is a healthcare company that wanted me to analyze their revenue cycle and identify areas of improvement. The revenue cycle is the process of generating revenue from patients, from scheduling appointments to collecting payments. It’s a complicated process that involves multiple data sources such as patient records, insurance claims, billing systems, etc.
To make my job easier and faster, I decided to use Excel VBA to automate the analysis of the revenue cycle. I created a macro that can connect to different data sources and extract relevant data into a worksheet. It was like magic, how the data appeared in front of me with just a click of a button.
Then I created another macro that updates numbers to feed into formulas on the data to measure KPIs like days in accounts receivable, net collection rate, denial rate, etc. It was like solving a puzzle, how the numbers revealed the strengths and weaknesses of the revenue cycle.
Next, I wrote some VBA to create and update pivot tables and charts. It was like painting a picture, how the data transformed into visuals showing trends and patterns in the cycle.
And finally, I learned more VBA and put together a masterful piece of work that updates dashboards to monitor the performance of the revenue cycle. This part was like composing a symphony. How the data harmonized into a coherent and comprehensive report. Took a lot of fine-tuning but eventually I delivered.
So, with Excel VBA, I was eventually able to run multiple analyses of the revenue cycle in less than a day. This was well received by my client and their management team. And it means I can now use similar automations when working for other clients."
Want to Learn Excel Macros and VBA today?
Excited about the opportunities that Excel VBA could open up for you? But unsure of how you can learn it for yourself because it seems so technical?
Yes, I get it. For someone without a formal programming background, learning how to code in Excel can seem daunting. There’s just SO MUCH you can do with VBA it can feel overwhelming.
But I also know that it’s not as hard as you might fear. The reason for this is that Excel and VBA go hand-in-hand. It’s not like other programming languages where you need to create an interface for the code you write. VBA has Excel as its interface.
And it gets better. You can record your own actions in Excel, and have it generate sample code. It’s not always perfect code, but it helps you understand the language faster. Click here, copy this, change to a different worksheet. Excel can record your steps in the VBA language.
We put together a free book to take you through the first steps of recording your own VBA macros in Excel. Get it now and learn how to harness the power of automation.
FREE EBOOK ($25 value)
Discover the Ultimate Excel Shortcut!
Get my FREE 28-page guide and unleash the power of Excel Macros and VBA to automate your workflow and save time.
Get awesome at automating Excel!
But that’s not all. If you’re already familiar with recording macros, or have even played with some VBA code, then you can check out our Excel VBA Course.
It’s designed to teach you the skills you need to master macros and VBA and apply them to your work. It covers topics such as variables, loops, conditions, arrays, objects, events, user forms, error handling, debugging, and much more.
The course also comes with practical exercises and projects that will help you apply what you learn to real-world scenarios. You’ll build up your VBA skills in no time.
Click to learn more about Excel Macros and VBA School.
Excel VBA in Accounting: Specific Use Cases & VBA Code
So we saw in the above case studies that Mike and Jenna used Excel VBA to great effect. Now don’t get me wrong, it’s not a magic bullet by any means. For cases when there’s too much data, or if it’s too complex, then other automation solutions work better.
But for certain automation tasks like Mike’s and Sarah’s, Excel VBA can be a real handy tool that saves time and effort. It can also improve accuracy and consistency. This is especially true for repetitive tasks that happen on the desktop and don’t involve too much interaction with the web.
Let’s look at sample use cases.
Data Entry: Import, Copy, Format, Validate
Data entry is one of the most common and time-consuming tasks in accounting. You often have to enter data from various sources such as invoices, receipts, bank statements, etc. into Excel worksheets. This can be prone to errors and inconsistencies.
With Excel VBA, you can create macros that can automate data entry tasks such as:
- Importing data from external files such as CSV, TXT, XML, etc.
- Copying and pasting data from one worksheet to another
- Formatting data such as dates, numbers, currencies, etc.
- Validating data such as checking for duplicates, errors, outliers, etc.
- Filling in missing data such as blanks, zeros, defaults, etc.
For example, you can use this code ImportCSV() to import data from a CSV file into a worksheet:
Sub ImportCSV()
Dim FileName As String
FileName = Application.GetOpenFilename("CSV Files (*.csv), *.csv")
If FileName <> "False" Then
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & FileName _
, Destination:=Range("$A$1"))
.Name = "CSV"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End If
End Sub
The above code is a VBA macro that allows the user to import a CSV file into the active worksheet.
It will open a dialog box where you can select a CSV file to import (using the Application.GetOpenFilename).
If you select a file, it will then create a query table in the worksheet that will import the data from the CSV file (using ActiveSheet.QueryTables.Add).
The query table is set up to read the CSV file and insert the data into cells starting at A1 on the active worksheet. If you want to import the data into a different worksheet, you’d need to modify the code accordingly.
You can adjust the parameters of the query table according to your needs. The various properties of the query table are set using the chained dot notation.
For example, .TextFileParseType = xlDelimited specifies that the CSV file is delimited, and .TextFileCommaDelimiter = True specifies that the delimiter is a comma.
Finally, the macro will refresh the query table using the .Refresh method.
You could run this macro by assigning it to a button on your worksheet. Then every time you click the button, the macro imports the selected CSV file.
Note: before you run any macros on your own workbooks, please ensure you have a backup of your data. There’s no built-in way to undo a macro. Better safe than sorry!
Here’s a heavily commented version of the macro to help you understand how it works:
Sub ImportCSV()
' Declare a variable to store the file name
Dim FileName As String
' Prompt the user to select a CSV file
FileName = Application.GetOpenFilename("CSV Files (*.csv), *.csv")
' Check if the user has selected a file (FileName will be "False" if the user cancels the file selection)
If FileName <> "False" Then
' Create a query table to import the CSV file into the active worksheet
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & FileName, Destination:=Range("$A\$1"))
' Set various properties of the query table
.Name = "CSV"
.FieldNames = True ' Include field names (column headers) from the CSV file
.RowNumbers = False ' Do not include row numbers
.FillAdjacentFormulas = False ' Do not fill adjacent cells with formulas
.PreserveFormatting = True ' Preserve the formatting of the data
.RefreshOnFileOpen = False ' Do not refresh the data when the file is opened
.RefreshStyle = xlInsertDeleteCells ' Refresh style: insert/delete cells
.SavePassword = False ' Do not save password (not applicable for CSV files)
.SaveData = True ' Save the data with the workbook
.AdjustColumnWidth = True ' Adjust column width based on the imported data
.RefreshPeriod = 0 ' No automatic refresh interval
.TextFilePromptOnRefresh = False ' Do not prompt the user when refreshing the data
.TextFilePlatform = 437 ' Set the text file platform (usually 437 for Windows)
.TextFileStartRow = 1 ' Start importing data from the first row of the CSV file
.TextFileParseType = xlDelimited ' Set the parse type as delimited (CSV files are delimited by commas)
.TextFileTextQualifier = xlTextQualifierDoubleQuote ' Use double quotes as the text qualifier
.TextFileConsecutiveDelimiter = False ' Do not treat consecutive delimiters as one
.TextFileTabDelimiter = False ' Tab is not the delimiter
.TextFileSemicolonDelimiter = False ' Semicolon is not the delimiter
.TextFileCommaDelimiter = True ' Comma is the delimiter (CSV stands for Comma Separated Values)
.TextFileSpaceDelimiter = False ' Space is not the delimiter
.TextFileColumnDataTypes = Array(1) ' Set the data type for each column (1 = General)
.TextFileTrailingMinusNumbers = True ' Recognize numbers with trailing minus signs
' Refresh the query table to import the data from the CSV file
.Refresh BackgroundQuery:=False
End With
End If
End Sub
Reconciliation: Compare, Highlight, Calculate, Summarize
Reconciliation is another common and important task in accounting. You often have to reconcile data from different sources such as bank accounts, credit cards, invoices, receipts, etc. to ensure accuracy and completeness. This can be tedious and complex. Hear a pattern?
With Excel VBA, you can create macros that can automate reconciliation tasks such as:
- Comparing data from different worksheets or workbooks
- Highlighting or deleting matching or mismatching data
- Calculating differences or variances between data sets
- Creating summary reports or dashboards for reconciliation results
For example, you can use this code CompareWorksheets() to compare two worksheets and highlight the matching data in green and the mismatching data in red:
Sub CompareWorksheets()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim cell As Range
Dim diffCount As Long
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
'Compare worksheets cell by cell
For Each cell In ws1.UsedRange
If cell.Value <> ws2.Range(cell.Address).Value Then
'Highlight mismatching cells in red
cell.Interior.Color = vbRed
ws2.Range(cell.Address).Interior.Color = vbRed
'Count number of differences
diffCount = diffCount + 1
Else
'Highlight matching cells in green
cell.Interior.Color = vbGreen
ws2.Range(cell.Address).Interior.Color = vbGreen
End If
Next
'Display number of differences
MsgBox "Total differences: " & diffCount
End Sub
The above code will compare two worksheets named “Sheet1” and “Sheet2” cell by cell. It will highlight the matching cells in green and the mismatching cells in red. It will also display the number of differences in a message box.
Note: before you run any macros on your own workbooks, please ensure you have a backup of your data. There’s no built-in way to undo a macro. Better safe than sorry!
Here’s a heavily commented version of the macro to help you understand how it works:
Sub CompareWorksheets()
' Declare variables for the two worksheets and a range variable for cells
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim cell As Range
Dim diffCount As Long
' Set the variables to reference the two worksheets ("Sheet1" and "Sheet2")
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
' Loop through each cell in the used range of the first worksheet (ws1)
For Each cell In ws1.UsedRange
' Compare the value of the current cell in ws1 with the corresponding cell in ws2
If cell.Value <> ws2.Range(cell.Address).Value Then
' If the values are different, highlight the cells in both worksheets in red
cell.Interior.Color = vbRed
ws2.Range(cell.Address).Interior.Color = vbRed
' Increment the count of differences
diffCount = diffCount + 1
Else
' If the values are the same, highlight the cells in both worksheets in green
cell.Interior.Color = vbGreen
ws2.Range(cell.Address).Interior.Color = vbGreen
End If
Next
' Display a message box showing the total number of differences found
MsgBox "Total differences: " & diffCount
End Sub
Reporting: Updating, Populating, Formatting, Saving
Reporting is another essential task in accounting. You often have to create reports for various purposes such as financial statements, budgets, forecasts, audits, taxes, etc. These reports have to be accurate, consistent, and compliant with the standards and regulations. This can be challenging and time-consuming.
With Excel VBA, you can create macros that can automate reporting tasks such as:
- Creating and updating report templates
- Populating report data from various sources
- Formatting and styling report elements such as headers, footers, tables, charts, etc.
- Applying conditional formatting and data validation rules to report data
- Printing and saving report files in different formats such as PDF, XLSX, etc.
For example, you can use this code CreateReportTemplate() to create a report template with a header, a footer, and a table:
Sub CreateReportTemplate()
Dim ws As Worksheet
Dim tbl As ListObject
Set ws = Worksheets.Add
ws.Name = "Report"
'Create header
With ws.PageSetup
.CenterHeader = "&""Arial,Bold""&14Report Title"
.LeftHeader = "&""Arial""&10Date: &D"
.RightHeader = "&""Arial""&10Page: &P of &N"
End With
'Create footer
With ws.PageSetup
.CenterFooter = "&""Arial""&10Report Footer"
.LeftFooter = "&""Arial""&10Prepared by: &U"
.RightFooter = "&""Arial""&10Approved by: "
End With
'Create table
Set tbl = ws.ListObjects.Add(xlSrcRange, Range("A1:D10"), , xlYes)
tbl.Name = "Table1"
tbl.TableStyle = "TableStyleMedium2"
'Populate table headers
tbl.HeaderRowRange(1) = "Account"
tbl.HeaderRowRange(2) = "Debit"
tbl.HeaderRowRange(3) = "Credit"
tbl.HeaderRowRange(4) = "Balance"
End Sub
The above code will create a new worksheet named “Report” and add a header, a footer, and a table to it. You can customize the parameters of the header, the footer, and the table according to your needs.
Note: before you run any macros on your own workbooks, please ensure you have a backup of your data. There’s no built-in way to undo a macro. Better safe than sorry!
Analysis: Calculating, Filtering, Pivots, Dashboards
Analysis is another important task in accounting. You often have to analyze data from various sources such as financial statements, budgets, forecasts, audits, taxes, etc. to gain insights and make recommendations. This can be complex and difficult.
With Excel VBA, you can create macros that can automate analysis tasks such as:
- Performing calculations and formulas on data sets
- Creating and updating charts and graphs to visualize data trends and patterns
- Applying filters and slicers to data sets to drill down and slice and dice data
- Creating and updating pivot tables and pivot charts to summarize and aggregate data
- Creating and updating dashboards and scorecards to monitor and measure performance
For example, you can use this code CreateChartSheet() to create a chart sheet that shows the monthly revenue of different products:
Sub CreateChartSheet()
Dim ws As Worksheet
Dim rng As Range
Dim cht As Chart
Dim i As Long
Set ws = Worksheets("Data")
Set rng = ws.Range("A1:E13")
'Delete existing chart if it exists
On Error Resume Next
Charts("Chart_1").Delete
On Error GoTo 0
'Create chart
Set cht = Charts.Add
cht.Name = "Chart_1"
'Set chart source data
cht.SetSourceData Source:=rng
'Set chart type
cht.ChartType = xlColumnClustered
'Set chart title
cht.HasTitle = True
cht.ChartTitle.Text = "Monthly Revenue by Product"
'Set chart axis titles
cht.Axes(xlCategory).HasTitle = True
cht.Axes(xlCategory).AxisTitle.Text = "Month"
cht.Axes(xlValue).HasTitle = True
cht.Axes(xlValue).AxisTitle.Text = "Revenue"
'Set chart legend
cht.HasLegend = True
cht.Legend.Position = xlBottom
End Sub
The above code will create a new chart sheet named "Chart_1" and set its source data, type, title, axis titles, and legend. Be aware that it also deletes any existing “Chart_1” chart sheet before creating a new chart sheet with that name. You can customize the parameters of the chart according to your needs.
Note: before you run any macros on your own workbooks, please ensure you have a backup of your data. There’s no built-in way to undo a macro. Better safe than sorry!
How to Get Started with Automating Excel using VBA?
Do you like the idea of automating your accounting tasks with VBA? All the potential time and money savings you can get? But you don’t know how you can learn to create Excel VBA by yourself and use it in your accounting role?
That's where Excel Macros & VBA School comes in. We designed this course to teach you the skills you need to master Excel automation from the ground up.
Here are some of the benefits and features of our Excel VBA course:
- Online access: You can access our course anytime and anywhere from your computer, tablet, or smartphone.
- Lifetime updates: You get access to all future updates and additions to our course for free.
- Certification: You get a certificate of completion after finishing our course.
- Support: You get dedicated support from through our course website and email.
- Satisfaction guarantee: You have a full 30 days to try out our course. If you aren’t satisfied for any reason we’ll give you a full refund, no questions asked.
Our Excel VBA course covers topics such as:
- Variables, constants, and data types: create more flexible code that handles different scenarios and inputs
- Loops, conditions, and arrays: control the flow of your code and automate complex calculations and operations
- Objects, properties, methods, and events: interact with Excel and other Office applications like Word, Powerpoint, and Outlook
- User forms, controls, and dialogs: enhance the usability and accessibility of your solutions with user-friendly interfaces
- Error handling, debugging, and testing: prevent and fix errors in your code to ensure reliability and accuracy of your results
- And much more!
You also get practical exercises and projects that will help you apply what you learn to real-world scenarios.
- Click here to learn more about Launch Excel Macros and VBA School!
Learning Excel VBA Can Improve Your Productivity
As you can see from our two case studies with Mike and Jenna, plus the sample code for specific use cases, you can use Excel VBA to automate accounting tasks and save time and money. Given the right macros, it's a powerful and convenient way to help you with data entry, reconciliation, reporting, analysis, and more.
If you want a simple way to get started with Excel Macros, check out our free book on how to record your first macro. This is the most stress-free introduction to Excel VBA you can get!
FREE EBOOK ($25 value)
Discover the Ultimate Excel Shortcut!
Get my FREE 28-page guide and unleash the power of Excel Macros and VBA to automate your workflow and save time.
Get awesome at automating Excel!
And if you feel more confident and want to take a full online course complete with over 10 hours of high quality video instruction, hands-on projects, and code samples, check out Launch Excel Macros and VBA School.
Hey, I'm Victor Chan
Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?
Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.
That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.
Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!
Recommended Posts
Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.
Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.
Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!
Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.
Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.
RECOMMENDED READING
Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.
Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!
Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.