Constructing Custom CRMs with Microsoft VBA


♦ We recently published a handful of blog posts centered around Microsoft VBA (Visual Basic for Applications, aka macros) and how the various Microsoft desktop productivity applications that make up Microsoft Office can use this code to get the maximum power and ease of use out of the various applications. This post takes it in a different direction in that this post discusses constructing custom CRMs with Microsoft VBA.


♦ How VBA is used to build a specific application such as a CRM allows you to envision how your organization can use VBA and one of the Microsoft applications to build your own custom CRM. VBA is used in


Below is an example of what a CRM might look like, when built in Microsoft Access.
Excel and Access, LLC CRM examples


♦ As we said in three of our recent blog posts, Microsoft envisioned VBA as the glue that tied the Office applications together; Microsoft envisioned VBA as being the code that enabled the automation and/or integration of their applications. While a CRM can be built in one or more of the Microsoft desktop tools, the thing that each of these solutions share is that their backbone is the code that makes-up VBA.


    A few of our recent posts covering Microsoft VBA:

  1. Integration and Automation with Microsoft’s VBA
  2. Making Microsoft’s Vision a Reality, VBA
  3. What You Might Not Know About Microsoft’s Vision, VBA
  4. Should You Integrate Microsoft Excel and Access, Via VBA


According to Wikipedia: Customer relationship management (CRM) is an approach to managing a company’s interaction with current and potential customers. It uses data analysis about customers’ history with a company and to improve business relationships with customers, specifically focusing on customer retention and ultimately driving sales growth.


♦ At Excel Consultant, we’re passionate about helping our clients harness the full potential of Microsoft programs. One of the best instruments for doing so is VBA, or Visual Basic for Applications, the coding language that allows for nearly limitless customization, automation, and integration.

We recently delved into the details of VBA on our blog, describing how we use it to write masterful macros. These codes effortlessly handle long series of tedious tasks in Microsoft programs, freeing up time, money, and energy for our clients.


♦ We’re delighted to help our clients optimize their operations with VBA. In our previous blog, we explained that VBA is the glue that binds Microsoft programs together, allowing these applications to achieve even better results.

One of the best examples of what we can do with this language is our custom-coded CRM (Customer Relationship Management) software, built in Microsoft Access (See screen shot below). In the following blog, we showcase this service and demonstrate the value VBA can bring to your company’s customer service in our programmers’ skilled hands.


Below is an image of an Access CRM that we built for internal use a decade ago.
At Excel Consultant we use Microsoft Access to build our client's CRM


What is CRM Software?

What is a CRM, according to Microsoft? To help you understand what makes VBA so versatile and effective in building CRM solutions, we first need to define what this software actually does. Investopedia explains: “special CRM software aggregates customer information in one place to give businesses easy access to data, such as contact data, purchase history, and any previous contact with customer service representatives.

This data helps employees interact with clients, anticipate customer needs, recognize customer updates and track performance goals when it comes to sales.” Basically, CRM software helps your team sort through the mountains of data you have on your customer base to find and use the information they really need.


5 Examples of which Microsoft software applications can be used to house your CRM application:

  1. Microsoft Access (Our first choice)
  2. Microsoft Excel (Our second preference)
  3. Microsoft SQL Server
  4. Microsoft Power BI
  5. Microsoft Dynamics


The Capabilities of Custom CRMs

♦ CRM software is a powerful tool that many of our business clients use to manage their operations. However, not all applications are created equal. Since you’ll be interacting with the program on a daily basis, an outstanding CRM will make your enterprise exponentially more efficient, while the wrong software could burden your business with added complications.


♦ You can, of course, purchase prepackaged CRMs off the shelf, but these will never meet your company’s total needs. They tend to be bloated with unnecessary functionality and lacking the features you actually need. Canned CRMs force you to work the way they were designed, which typically isn’t optimal for your business. Custom development is a certain way to get the right CRM for your company’s needs.


♦ This is where VBA’s ability to integrate and automate becomes absolutely invaluable. Microsoft created VBA to be an information highway between various Microsoft applications, and we maximize this potential.


Excel Consultant uses VBA macros to build our custom CRM solutions in Access, Microsoft’s database management system. Constructing these programs in Access allows you to leverage the power of all related Microsoft applications. With the help of VBA, we integrate Access with Excel, Outlook, PowerPoint, and QuickBooks, coding Access to automatically gather and analyze the data you need from all of your Microsoft applications.


Designing it just for your company in Access also makes your CRM flexible. You can expand on your custom Access CRM over time, making them more affordable. You can gradually add more sophisticated functions as you need them rather than settling for a weak CRM you’ll have to pay to replace or wiping out your entire budget on an expensive system that still probably won’t serve all of your particular needs.


Wikipedia says, “One important aspect of the Customer relationship management, processes implemented to manage a company’s interactions with customers and prospects CRM approach is the systems of CRM that compile data from a range of different communication channels, including a company’s website, telephone, email, live chat, marketing materials, and more recently, social media.[2] Through the CRM approach and the systems used to facilitate it, businesses learn more about their target audiences and how to best cater to their needs. However, adopting the CRM approach may also occasionally lead to favoritism within an audience of consumers, resulting in dissatisfaction among customers and defeating the purpose of CRM.”


Crafting Custom CRMs in Access with VBA

♦ Essentially, we use Microsoft Access as the “parent application” for your CRM, which means that the code is initiated in this program. We fully custom tailor your Access application to suit your users’ business needs. This program collects every piece of data you need about your clients from your other Microsoft programs and stores them so that you can instantly obtain this information.


♦ This kind of streamlining may seem simple, but it requires deft VBA coding to accomplish and offers brilliant benefits. For example, if you use QuickBooks to manage your firm’s day-to-day business activities and add about 250 new clients each week, you’d have to pay someone to type each new customer’s information into QuickBooks, then Outlook so you can communicate with them, then Excel for reporting and analysis, then PowerPoint to share important client information.


By the time all of the data is where you need it, you’d have spend hundreds of dollars of employee time and, adding insult to injury, you’d probably have a whole new batch of client information to enter, restarting the process. With a custom CRM in Access, you can enter client information just once, rather than four or five times, saving you notable time and money while ensuring that your reports remain up-to-date.


♦ Our custom code also distributes information back to the other Microsoft applications the user uses, so if information changes in Access, the code shares relevant data with Outlook, Excel, PowerPoint, and QuickBooks in turn. With our state-of-the-art VBA macros, Access becomes the central database for your company’s operations.

It then communicates with, cross-references, collects data from, and circulates information to your entire network of Microsoft programs. This ensures that every application, not just your CRM, runs on the most accurate business intelligence.


Here is an example of a CRM built in Microsoft Excel
Download a simple CRM system for Microsoft Excel®, by Jon WittwerDownload a simple CRM system for Microsoft Excel®, by Jon Wittwer


According to Jon Wittwer of Customer Relationship Management (CRM) systems and CRM software can help you increase sales by streamlining communications with your potential and current customers.



♦ In addition to coding a constantly updating database, the Excel Consultant team creates user-friendly Access forms that lower level employees can use to run more complex VBA macros behind the scenes. With these custom Access CRMs, all the user needs to do is type new information into the Access forms our team has designed, click a few check boxes or make a few drop-down selections, and click the “run” button to activate the macro.


This feature is particularly useful for managing your company’s ever-increasing list of customers and managing more complex cross-departmental projects. The VBA underlying this process then integrates the data between applications, providing users with the information they need while refreshing the data in the rest of the company’s Microsoft Office programs. This automates the workflow process, saving users hours, if not days, of time they can then spend on more meaningful profit-producing efforts for the enterprise.


♦ Ultimately, when coded and coordinated by our expert team, VBA makes it so that your team does not need to do significant segments of their work. Instead of spending hours searching through spreadsheets or copying data from one application to another, you can depend on VBA to do it for you.

With this advanced functionality, projects that use to take weeks could literally be finished in minutes, all with the few clicks of a mouse. Integrating your applications and simplifying your operations can offer huge savings in labor costs. This means that your company can pay for a custom Access CRM once and reap the financial rewards on an hourly basis.


Could Your Company Benefit from a Custom CRM?

♦ Are you ready to be rid of repetitive data entry? Do you want to learn more about VBA macros? Are you interested in a custom CRM designed by our dedicated team? Contact Excel Consultant today! We offer a wide array of Microsoft consulting, programming, training and Power BI services.