Definition: An Excel VBA consultant will provide your company with expert advice on automating repetitive tasks, increasing efficiency, reducing errors, and streamlining your workflows within Microsoft Excel.
A good Excel VBA consulting firm should also have experience with various related skills such as SQL, SharePoint, and enterprise tools such as SAP. These additional skills will ensure the VBA consulting firm can solve all related problems related to the flow or management of information.
Tools like Power Query and Power Automate are often discussed as an alternative to VBA. While these tools have their place, VBA will always give your company more flexibility for automation of data related processes. Along with keeping the code and logic all in one place, we recommend using VBA for many business, accounting, or finance related workflows.
The most significant need for an Excel VBA consultant is to help a company streamline workflows within Excel. Many times, a task can be reduced to a single button click. This button click will run a Macro that is coded using VBA. The macro will pull data, manipulate different sheets, add dynamic formulas, and send automated notifications like emails.
These automated VBA processes can include automating commission reports, uploading files to SharePoint, calculating payroll, or creating a monthly financial statement file. Below are some common examples of VBA projects.
Using VBA to streamline workflows often involves the need to refresh the primary data source. Usually, this data comes from an accounting or ERP system. The preferred approval for automated data import would be a VBA script to import data directly from SQL. The next best option would be a VBA formula to convert a worksheet to an array. The VBA code would then manipulate the array and replace the data in the workbook.
An automated system should send emails to stakeholders when a workflow is complete. These emails can be sent in VBA using one of two methods. The CDO method can be used to send emails without Outlook being open. The other method would create an Outlook Object, and emails can be drafted while Outlook is open. The emails can either e sent automatically or dynamically created and not sent; sending manually gives the user the ability to edit wording on the fly before sending.
Pulling data from third-party tools such as pricing or survey data can be time-consuming. The use of an API can help streamline repetitive tasks like imports. Your VBA consultant must be well versed in different programming languages as some API might use HTTPS requests, and others might need the use of a server-side language.
A typical VBA project is creating quote pricing tools. These tools will generally have a few buttons to import and clean data, do various calculations, and then export the data. This export is sometimes a clean copy of the workbook sent to clients or a clean and professional-looking PDF.
A common need for an Excel VBA consultant is to create custom formulas. Custom formulas allow you to increase the native functionality of excel while reducing the length of the formula in your formula bars. In addition, these formulas will lay out the logic simply and concisely while allowing you to edit and expand the formula as business processes change.
Here are some examples of custom VBA formulas our consultants have built for different clients.
Because an Excel VBA consultant will come across a wide variety of business problems, a good consulting firm should have the resources to help you with other systems besides Microsoft Excel. Here are some of the most common tools that an Excel VBA consultant should be using:
Any company managing large amounts of data should be using a relational database. SQL is how you access that data.
A consultant fluent in SQL will be able to provide your company with updated queries for source data or even trouble issues with source data.
Many companies use SharePoint to upload files for payroll, a company budget, or even company metrics.
A firm familiar with SharePoint can help create a custom site that can be used for uploads and even downloads.
Accounting and Finance workflows will often use SAP as the data source. A consultant familiar with SAP can help your company create custom reports that can be exported.
When choosing an Excel VBA consultant, the rate can tell you about the firm. A higher rate generally indicates more overhead. Conversely, more overhead could be a sign of an efficient company, resulting in lower quality projects. For Excel Complete, our rates are low because we have streamlined processes in place and little to no overhead.
Our rates are $50 an hour. No minimum project size and no hidden fees. For jobs that require a retainer or on-demand support, the rate would still be $50 but is usually billed in one simple monthly amount.
Other firms have a wide variety of price ranges, but generally, $75 - $125 is the average rate. Many consultants will also have a minimum project amount of $500 or more.
To better understand how we approach our work, here are the general steps we take for each engagement. These steps will help ensure high-quality work.
The first step in a consulting engagement is to understand your business requirements and identify the pain points in your current process. We will also take a deep dive into the flow of information in your company. This can include reviewing current VBA code or Excel formulas, asking staff for recommendations, and looking at SQL queries or stored procedures.
As with anything tech-related, a single problem can have many solutions. The best solution might not be time or cost-efficient. Depending on your business needs, we'll ask you about the pros and cons of each suggested solution.
This is where our consultants will start to work and create your new workbooks. One of the key development techniques is to create a single dashboard for each Excel workbook. Generally, this dashboard will include all information that is used to run the VBA code. This dashboard can also include options to run files locally or just run for a few regions. This gives your company flexibility in testing before pushing a new process live.
Once the draft is complete, your project will be finalized. This step will include a code review and tie-up any loose project ends.
Depending on the project, go-live could include additional support or training to help acclimate your staff to the new process or Excel Workbook.