Posted on by raymond Posted in Excel/VBATagged , , ,
I love my work as a Microsoft Office VBA Freelance Developer
A new client called a few days ago asking if I could help with automating a mailing routine.  It actually involved creating a series of reports in a pdf format and then having a button to use outlook to mail them. Over the years  I’ve done this type of task many times before so I was confident I could help. It turns out this would be a case study of Excel vs Access.  (Perhaps the Access Excel Hybrid.).
After our initial discussion I had the workbook in my possession.  It turned out to be an impressive piece of work: volumous, sophisticated, used the latest excel functions and was highly functional.  He said before you use it “turn calculation to manual, it takes 20 seconds to calculate”.  I did as suggested But I missed a step! I failed to uncheck the recalc box before  saving, which wasted a half hour of my time.  I’ll know better next time.
I cleaned up and annotated the existing code and wrote new routines as necessary.  During the coding I became aware of the many dependencies,  every cell seemed to be a formula that depended on other cells with formulas. There are literally hundreds of cells with these multiple dependencies.  It worked but, the some of the more advanced formulas seemed a bit buggy when it came to calculation.

I sent the first piece over to the client – I didn’t have the ‘gig’ at this point, but after a few minutes discussion and review of the work I did we had a deal.  The client was a bright guy, clearly with an aptitude for VBA and Technology.  I asked why he didn’t put it in access?  The answer was predictable:

  1. a number of people use this spreadsheet and they dont know access
  2. management is fussy with what it looks like

Excel vs Access
This all left me thinking about two things: when is it time to switch to MS Access?  When you analyze this particular workbook for what I’ll call its Root Function, the answer is it slices and dices data, which sounds like what a relational database does best.

I have a good deal of experience using Excel as the front end with an access  database as the “data crunching” engine.  Any required format can easily be achieved.  The reality is its working and as long as its working no one wants to invest the time and money to improve it.

Heres my thoughts on things to consider if its time to switch to access.
1) Root function of the worksheet is data sorting and tabulation
2) The data is volumous, thousands of rows
3) you need to turn calculation to manual
4) The workbook has lots of instructions and “Dont change this”
5) A  preponderance of vlookups, xLookups and nested formulas (complexity)

There is a phenomena out there called “spreadsheet creep”.  It started with a small simple spreadsheet and grew into this unwieldy monster.  It may have been modified by a few different people.  It works well you think, but its the elephant in the room no one dares mention.

Do you have an elephant? Can I help you make it right before the unthinkable happens?  The solution could be an Excel Front End (GUI) and an Access Database Backend doing its data crunching magic.

contact me Here

If you enjoyed my blog and would like to comment or share a similar experience please send your comments  Here.
If you would like to say hello and Buy me a cup of coffee please follow the link.
.
Raymond Mills, M.B.A., M.S.  has spent over 20 years of his career as Accountant, Investment Bank and Credit Card Technical Auditor/ Data Analyst.  His specialty was using Excel to get Big Databases including Teradata, Oracle,  Squel Server and Sybase to give up their secrets. Ray has said “I love nothing better than using VBA to unleash the power of Microsoft Office.” You can contact Ray @ 484 574-3190 or by emailing him Here

If you have a challenge with Excel, Access or Word and would like to speak with Ray,   You can get his contact details by clicking here: Contact Me