Excel, Macros, VBA - Fully Customizable Tools
Microsoft Excel is one of the most used software applications in the world. If you are reading this, you likely have experience using Excel. Your experience may be using it to track a few personal finance items, or you may have built an entire career working in Excel daily to manage the accounting or supply chain of a prominent business. If for no other reason, this makes building software and automation tools utilizing the highly flexible capabilities of Excel a great option.
Reasons to use Excel for your solution
- User-friendly - Even highly complex customized Excel tools have a familiarity for nearly all users.
- Cost-Effective - The primary spreadsheet interface gives a great starting point for the data handling needs of most business processes. This benefit can significantly speed up the time it takes to build a solution than creating a user-interface from scratch.
- Schedule Effective - In addition to being faster to develop, the user-friendly nature will reduce the typical friction in training and transitioning to new processes for users. Deployment can often be as easy as sharing an Excel file.
- Capability – Excel Macros are written in VBA (Visual Basic for Applications). VBA is a general-purpose programming language that can automate Excel and most of your entire PC due to its' seamless interactions with Windows (and other Microsoft products). In contrast, many low-code platforms such as Power Automate, Power Apps, etc., may hit a low ceiling on their capabilities. Even other general-purpose programming languages (python, java, etc.) may require additional effort to work well with Windows and your PC.
- Due to its' qualities described above, it can be magnitudes quicker to create, test, and deploy a solution. A more hidden benefit of this is that you can quickly trial a process change, and if you don't like it, it is easier to let go and try something else. The other standard software options of commissioning a built-from-scratch application or purchasing an expensive new subscription can make the business over-invested in a solution. This situation means you may have used up significant time and resources before you realize the solution is not for you, and it can be hard to let go of the sunken effort.
When you may not want to use Excel for your solution
- Especially complex data analysis may be better suited for specialized tools such as Python or R. An extreme example would be Machine Learning. In some other cases, Excel may have the ability to perform the task but be much less effective or efficient. Power BI or similar software may be better suited for complex data dashboards with vast data (ex. more than 1 million rows).
- If you need a web app or browser-based solution. Applications that run through your web browser may be better for particular goals. Macros and VBA cannot run through a browser yet, although Excel Online is constantly increasing in capability and can be a great solution in some cases.
- If you cannot tolerate any deviation from a strict process. The flexibility of Excel means that users will always have a certain degree of freedom. There are ways to reduce risk, such as protecting aspects of the file, restricting access, etc. However, it will always have much more user flexibility than a built-from-scratch application.
- If you or your business does not currently use or have Excel
Project Examples
Note: Projects shown are personal hobby projects for demonstration. I will not post any specific projects from customers or employers without express permission.
Jump to Specific Project...
Inventory Management App
Automated Document Generation Quote Example
Business Action Log and Tracker
Simple Time Clock Log
Inventory Management App
| Brief Description: | Manage Inventory of Ice Cream Shop (or other small business) |
| Platform: | Excel, VBA, SQL (MS Access DB and/or Azure Cloud SQL) |
| Compexity: | High |
| Key Concepts: | Master Data, Transaction Data, Business Process Controls, Data Analysis, Ease-of-use Design, Automation, Barcode Creation |
| Comments: | - Production version runs on Azure Cloud SQL Database. Simple trigger will connect to MS Access database for development. - Works with barcode scanner for instant item lookup and transaction. - Active inventory calculated on the fly based on transaction ledger. |
Figure: Home Page - Basic Navigation
Figure: Smart Item Lookup, Transaction to Database
| Additional Feature Ideas: | TBD |
Automated Document Creation Quote Example
| Brief Description: | Using inputs from an Excel file to create automated MS Word documents. Final output can be a Word document, PDF, email with documents attached, etc. |
| Platform: | Excel, VBA, Word |
| Compexity: | Medium |
| Key Concepts: | Automated Document Creation |
| Comments: | The figure shows a simple quote example. This automation method can be used to create nearly any document that can be created manually. |
Figure: Excel Inputs to Generate MS Word Document
Figure: Excel Inputs to Generate PDF Document
Figure: Tracking Log for Use with Document Creation
| Additional Feature Ideas: | Generate automated email message with the document(s) created as attachments. |
Business Action Log and Tracker
| Brief Description: | Allow teams to easily create, assign, track and manage actions for business operations or specific project |
| Platform: | Excel, VBA, SQL |
| Compexity: | Medium |
| Key Concepts: | External SQL database, Auto-email generation, event triggers, simultaneous multi-user |
| Comments: | Similar to other ticketing or request tracking systems available but without all the things you don't want or need. Simple, responsive, and extremely easy to deploy. |
Figure: Create new actions for an assignee, auto-email
Figure: Manage actions, re-open actions
| Additional Feature Ideas: | Integration with more specific projects or tasks (less generic) |
Simple Time Clock Log
| Brief Description: | Quick button press to start and stop timer to log time for applicable work. Stop button auto-jumps to field to typing description of work. |
| Platform: | Excel and VBA |
| Compexity: | Very simple |
| Key Concepts: | Button triggers, Basic process validation, data tracking |
Data Collection from Multiple Files and Reports
| Brief Description: | TBD |
| Platform: | Excel, VBA |
| Compexity: | Medium |
| Key Concepts: | TBD |
| Comments: | TBD |
| Additional Feature Ideas: |