How Excel VBA got me to London
In 2019 there is expected to be nearly 2 million people graduating with a Bachelors degree just in the US. Guess what? They are your competition. I’ve been there. That question of how do I differentiate myself from my competition- Hard work? Sure, but how about you couple that with working smart. I have a suggestion for you, even if you’ve been out of school for years.
Three words: Intentionally Embrace Technology (IET). It has been my experience that there are three types of people out there:
1)The reluctant user: “Technology isn’t my thing” or “I’m just not that technical.” In my opinion this attitude is disastrous- being proficient with excel is like being able to use a calculator, an absolute must.
2) The pragmatist: who use excel as much as necessary to get the job done. Some actually get quite proficient over time but, it isn’t intentional.
3) The master: They are always interested in improving their skills, so that they can be more productive, do a better job or it just plain excites them. Once you get to be known as the guy who uses technology to great effect, your value to the firm goes up drastically.
First I must admit, I am a developer but, I didn’t start out that way. I was an accountant by trade, more specifically I spent most of my career as an auditor. Auditing isn’t an easy profession; Your clients can be difficult and will often employ all manner of roadblocks to slow you down. They mistakenly see you as the “work police” and are afraid. I was okay at it, but with 500 auditors in a huge firm, spread all over the globe it’s pretty difficult to stand out. Truthfully all that paperwork wasn’t where my skills were strongest. Although I was always highly rated, that 1 “superstar rating” eluded me until my firm, Chase Manhattan merged with JP Morgan.
A merger can be a pretty unsettling thing to an employee – there’s the obvious “will I lose my job” but also “who will I work for”. I had been through a merger before, but this time, at least for the auditing department JP Morgan was in charge. I figured my days were numbered, but I was so wrong. Even after the paring down there were still too many of us, still they didn’t fire the better performers. My new boss came to me and said someone wrote this UDT (User Developed Tool) an excel macro. “You think you can figure it out” I think I said “I’ll sure try”.
There I was with time, a liberal training budget and a challenge. I stumbled upon a book by John Walkenback VBA Power Programming with Excel and VBA. It was an amazing book and got me on my way.
Visual Basic for Applications (VBA) in Excel, is a powerful and sophisticated built-in object Oriented programming language that allows you to:
1)Write code to automate mundane or repetitive tasks and improve efficiency
2) Create Custom Functions
3) Use a technology called “COM interface,” which allows Excel to communicate with other Microsoft and Non-Microsoft programs. This is extremely powerful feature.
4)Manage the communication with external databases including Big Data to extract data.
The division head apparently liked me and believed in training so I took all the courses that they would pay for. I mastered that UDT and built a powerful skill set that I employed to get real results. Fast forward a few years and I am sitting in a pub in London having fish and chips and a beer. The window in front of me is perfectly placed to get a perfect view of a well lit St. Paul’s cathedral. My hotel is a glass and chrome spectacle. I remember thinking this is pretty awesome. The firm brought me here because of that skill set. I could read and query databases. It wasn’t difficult and each year I got better. I learned something invaluable along the way, once you have access of the data all manner of secrets are revealed.
Success is a very relative thing, that is we all measure it in different ways. My goals were simple – build a pile so I’d never have to worry about most of life’s curve balls. Not be in the office until 9PM. Build a reputation as a invaluable contributor so I didn’t have to worry about the next merger, downsizing or Market Crash. (I still worried but all those things passed me by). Technology was my shield and helped me attain all my goals.
There is no doubt some of you are thinking well, I simply don’t have time to learn new technology. Did I mention I also managed to do 2 masters in the evenings? My point is there are people who come to work put their head down and are amazingly productive. (I would love to say I did all I could but the truth is I could have done more) The trick is to avoid distractions. JPMorgan was known for long hours and face time. I ignored all that and just focused and got the work done and more.
Here is my call to action- Learn VBA Programming
1. Buy both both books: Microsoft Excel Bible and the Excel 2010 Power Programming. [Pay attention to the version I’ve picked 2010 for this demo because you may not have the latest Excel Version]. You should be able to buy 2 recent versions for well under $50. The truth is the fundamentals do not change that much from version to version and new features are added. Now you might be tempted to buy excel VBA Programming for Dummies but John Walkenback’s book gives so much more.
2. Do a page by page review of the Excel Bible with a highlighter or a pencil. If you are wondering why should I buy that book when all of that information is available on the internet. The answer is simple the internet is an amazing source of information, but organization isn’t its strong suit. Having a chapter by chapter book for you to build a strong knowledge base is essential. Don’t just read, play with excel and make sure you can duplicate what you see in the text.
3. Once you’ve finished with your page by page review build yourself a personal Budget in Excel.
4. Do a page by page review of the power programming book and while doing it automate your budget. You can look at my Build yourself a Personal Budget Page to give you ideas. http://excelandvbacraftsman.com/excel-vba-budget-tool/
When you get through both books you should be well on your way to having a powerful extra skill set: the VBA programming language. VBA allows you control and extract information from other programs and it is called automation. I’ve used that functionality to great effect you can too. The jump from excel to VBA programming is very doable I’ve personally taught many people.
One thing, success in business requires you to continually learn new things. Let Excel, Access and VBA be your start- you won’t regret it.
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.”
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