How I taught myself Excel VBA in 1 month and how you can, too.
Just one month ago, I decided to take the plunge and start to learn the VBA Excel programming language to automate Excel worksheets and processes. I have learned a great deal since that starting point and am happy to share my experiences. I am very happy to have been able to reduce the process times and complexity for many of my and my team’s tasks from hours to just mere minutes.
- Use Google to find answers.
-Most times when I encounter an unfamiliar process to solve, I perform a web search of my dilemma. There are a ton of resources of similar issues and solutions.
-Stackoverflow.com has an excellent community that responds to all types of VBA questions. Previous questions that have been answered are viewable and can save a ton of time and frustration.
2. Don’t reinvent the wheel.
-As you build Macro projects, you will encounter similar issues each time. Be sure to save snippets of code in OneNote. You can easily leverage past learnings and make future projects easier.
3.Learning Resources — I have used a variety of formal and informal programs to learn VBA. The two most useful so far were the Udemy course and the Power Programming book. The biggest takeaway from these resources is just the immense power of Excel to improve manual Excel workbook tasks. I use these as references every now and then for ideas and to brush up on more advanced techniques. The New Horizons program was excellent for explaining how to build a professional Macro workbook.
-Excel 2010 Power Programming with VBA ~ $50
-Udemy.com Ultimate Excel VBA course ~ $15
-New Horizons Excel 2010 Programming with VBA ~ $700
4. Use the Macro recorder.
-The Macro recorder is a great tool to use to understand how everything in Excel is run by code. By understanding the code, you can build innovative solutions.
5.Experiment and keep trying.
-My first project was clumsy but functional. Every project that I have done since then has improved in efficiency and complexity. Don’t be afraid to try and make mistakes. Use the resources available to understand your errors and learn from them.
6. “A Journey of a Thousand Miles Begins with a Single Step”
-Break down each project into a series of tasks and then combine each successful step as you continue. Some projects require process mapping and then programming to understand what steps are necessary. Begin at the smallest level and test your code to be sure that it works. Move onto the next task and soon, it will all flow together.
7.Use one master Macro to run all of the sub-Macros in a project.
-Each step should be it’s own Macro. When you have finished, every sub-Macro should route to a master Macro with the ‘Call’ function. This can be linked to a single button in a worksheet that removes all manual processes that were previously performed.
8. Think creatively.
-Most problems have several different answers and solutions. Brainstorm and research solutions. If you get stuck, think of how else to approach a roadblock. Do not waste time pressed against a brick wall.
9. Understand what can be programmed with VBA.
-If a process entails steps that follow set rules that can always be applied, then it can be programmed.
-If something requires analytical skills that can’t be documented, then don’t try and program the process. Use Excel to make the manual steps leading up to that part easier.
10. Sometimes the answer isn’t programming.
-Use diagramming to understand why tasks take time to complete.
-Many times, I use the ‘Index/Match’, ‘Countif’, ‘Sumif’, Pivots, and ‘Remove Duplicates’ formulas to speed along lengthy processes.
Happy programming!