Each year we get to work on hundreds of Excel projects with our Clients. Some of the most interesting projects involve automating Excel tasks using VBA macros. We had some fun looking back at the most interesting and creative Excel automations from this past year. Here is our Top 10 List:
#10 - Google Earth Plotting
Excel was used to automate the placement of hundreds of Google Earth "pins" on demand based on a selected list of addresses. first used Google Maps to determine the latitude and longitude of an address. VBA then pinned the coordinates and key data to Google Earth.
#9 - Dropbox Consolidation
How do you summarize and analyze Excel files stored in hundreds of Dropbox folders? Excel VBA is one efficient way to get the job done. VBA was used to open each Excel file found in each folder and record key information on a summary report.
#8 - Portfolio Randomizer
The task was to allocate a page full of investments in an equitable manner to 5 partners based on 8 performance criteria. Not an easy job for a human, but a clever bit of VBA code was able to determine the most equitable combination in no time at all.
#7 - Fuzzy Match
If you've worked with the Excel VLOOKUP or MATCH functions, you know that exact matches of text data allow you to bring various data sets together. However, what if the match is not exact? This macro was written to find the closest match in a list using character by character matching and match scoring.
#6 - QuickBooks Conversion
Where do you turn if you need to convert accounting systems for dozens of companies from QuickBooks to another accounting system? We worked with our client to develop an automated process using Excel VBA. Each QuickBooks transaction was processed based on a set of conversion rules.
#5 - Dual Serial Feeds (USB and Bluetooth)
Excel can be used to read serial port inputs. This is often used for barcode scanners as one example. In this case, we developed a set of macros that processed feeds from both a USB port and a Bluetooth port simultaneously.
#4 - From Raw Survey Data to PowerPoint
This time saving macro imports customer survey results and automatically converts the data into a 40 slide PowerPoint presentation rich in charts and analysis. VBA can be used to automate tasks throughout the entire suite of Microsoft Office products.
#3 - High Volume Stock Analysis
Excel is often used to maintain stock portfolios and analyze metrics related to each stock. In this case, dozens of complex computations needed to be performed on thousands of stocks. We turned to VBA to automate this process. Stock data is retrieved directly from the internet for analysis.
#2 - Production Schedule with MRP
Many of our projects each year involve the analysis of manufacturing operations. In one case, we worked with our client to develop a highly automated workbook which is used to schedule production. Based on this production schedule, the multi-level BOM is used to run MRP calculations for the plant.
#1 - Excel with SQL Server Database on Azure Cloud
When a client's Excel data grew to include over 500 Excel files at about 10MB each, it was time to move to a database. We built a SQL Server database on Microsoft Azure Cloud to store the data. Excel is still the application "front end" and we use VBA to read and write directly to the database.
Contact Us Today
About the author: Brian Busscher is the Founder and CEO of Thesis Technologies which pursues a mission to “help you get your systems right.” If you would like to learn more about how the right business systems can help drive your business forward, please contact the Thesis Team at (888) 705-7253.
Related Blog Posts
How Does Microsoft Excel Help Analyze Statistical Data?
How Using Microsoft Excel Can Improve Your Business
This blog post has been updated.