Letting Excel write code for you
One of the best places to get macro help is the macro recorder in Excel. When you record a macro with the macro recorder, Excel writes the underlying VBA for you. After recording, you can review the code, see what the recorder did, and then try to turn the code it creates into something more suited to your needs.For example, say you need a macro that refreshes all the pivot tables in your workbook and clears all the filters in each pivot table. Writing this macro from a blank canvas would be a daunting task. Instead, you can start the macro recorder and record yourself refreshing all the pivot tables and clearing all the filters. After you stop recording, you can review the macro and make any changes you deem necessary.
To a new Excel user, the Help system may seem like a clunky add-in that returns a perplexing list of topics that has nothing to do with the original topic being searched. The truth is, however, after you learn how to use the Excel Help system effectively, it’s often the fastest and easiest way to get extra help on a topic.
You need to remember two basic tenets of the Excel Help system:
- Location matters when asking for help. Excel actually has two Help systems: one providing help on Excel features and another providing help on VBA programming topics. Instead of doing a global search with your criteria, Excel throws your search criteria only against the Help system that’s relevant to your current location. This essentially means that the help you get is determined by the area of Excel in which you’re working. So, if you need help on a topic that involves macros and VBA programming, you need to be in the VBE while performing your search. This ensures that your keyword search is performed on the correct Help system.
- Online help is better than offline help. When you search for help on a topic, Excel checks to see if you’re connected to the Internet. If you are, Excel returns help results based on online content from Microsoft’s website. If you aren’t, Excel uses the Help files that are locally stored with Microsoft Office. Online help is generally better because the content is often more detailed and includes updated information, as well as links to other resources not available offline.
Pilfering code for Excel VBA from the internet
The dirty secret about programming in the Internet-age is that there is no original code anymore. All the macro syntax that will ever be needed has been documented somewhere on the Internet. In many ways, programming has become less about the code you can create from scratch, and more about how to take existing code and apply it creatively to a particular scenario.If you’re stuck trying to create a macro for a particular task, describe the task you’re trying to accomplish via your favorite search engine. For the best results, enter “Excel VBA” before your description.
For example, if you’re trying to write a macro that deletes all blank rows in a worksheet, search for, “Excel VBA delete blank rows in a worksheet.” You can bet two months’ salary that someone has tackled the same problem and posted example code that gives you the nugget of information you need to jump start ideas for building your own macro.
Leveraging Excel VBA user forums
If you find yourself in a bind, post your question in a forum to get customized guidance based on your scenario.User forums are online communities that revolve around a particular topic. In these forums, you can post questions and have experts offer advise on how to solve particular problems. The folks answering the questions are typically volunteers who have a passion for helping the community solve real-world challenges.
Many forums are dedicated to all things Excel. To find an Excel Forum, type “Excel Forum” in your favorite search engine.
Here are a few tips for getting the most out of user forums:
- Always read and follow the forum rules before you get started. These rules often include advice on posting question and community etiquette guidelines.
- Check whether your question has already been asked and answered. Why not save time by leveraging the forum’s archive of questions? Take a moment to search the forum to see whether your question has been asked before.
- Use concise and accurate subject titles for your questions. Don’t create forum questions with abstract titles like: Need advice, or Please Help.
- Keep the scope of your questions as narrow as possible. Don’t ask questions like: How do I build an invoicing macro in Excel.
- Be patient. Remember the folks answering your questions are volunteers who typically have day jobs. Give the community some time to answer your question.
- Check back often. After posting your question, you may receive requests for more details about your scenario. Do everyone a favor and return to your posting to either review the answers or respond to follow-up questions.
- Thank the expert who answered your question. If you receive an answer that helps you, take a moment to post a thank you to the expert who helped you out.
Visiting Excel VBA expert blogs
There are a few dedicated Excel Gurus that share their knowledge through blogs. These blogs are often treasure troves of tips and tricks, offering nuggets that can help build your skills. Best of all, they’re free!Although these blogs won’t necessarily speak to your particular needs, they offer articles that advance your knowledge of Excel and can even provide general guidance on how to apply Excel in practical business situations.
Here is a list of a few of the best Excel blogs today:
- ExcelGuru: Ken Puls is a Microsoft Excel MVP who shares knowledge at his blog. In addition to his blog, Ken offers several learning resources for advancing your knowledge in Excel.
- org: Purna “Chandoo” Duggirala is a Microsoft Excel MVP out of India, who burst on the scene in 2007. His innovative blog offers many free templates and article aimed at “making you awesome in Excel.”
- Contextures: Debra Dalgleish is a Microsft Excel MVP and the owner of a popular Excel site. With an alphabetized list of over 350 Excel topics, you’re sure to find something that will interest you.
- DailyDose: Dick Kusleika is the owner of the longest running Excel blog. He is the king of Excel VBA blogging with over ten years’ worth of articles and examples.
- MrExcel: Bill Jelen is the larger-than-life ambassador of Excel. This long-time Excel MVP offers over a thousand free videos and a huge library of training resources on his site.
Mining YouTube for Excel VBA video training
If you find that you absorb video training better than articles, consider mining YouTube. Dozens of channels are run by amazing folks who have a passion for sharing knowledge. You’ll be surprised at how many free high-quality video tutorials you can find.Attending live and online Excel VBA training classes
Live and online training events are an awesome way to absorb Excel knowledge from a diverse group of people. Not only is the instructor feeding you techniques, but the lively discussions during the class are a wealth of ideas and new tips you may never even thought of. If you thrive in the energy of live training events, then consider searching for Excel classes.Here are a few sites that provide excellent instructor-led Excel courses:
Learning from the Microsoft Office Dev Center for help with Excel VBA
The Microsoft Office Dev Center is dedicated to helping new developers get a quick start in programming Office products.Although the site can be a bit difficult to navigate, it’s worth a visit to see all the free resources, including sample code, tools, step-by-step instructions, and much more.
Dissecting other Excel files in your organization
Like finding gold in your backyard, the existing files in your organization are often a treasure trove for learning. Consider opening Excel files that contain macros, and have a look under the covers. See how others in your organization use macros. Try to go through the macros line-by-line and see if you can spot new techniques.You could find a few new tricks you never thought of. You may even stumble upon entire chunks of useful code you can copy and implement in your own workbooks.
Asking your local Excel Guru
Do you have an Excel genius in your company, department, organization, or community? Make friends with that person today. What you have there is your own personal Excel forum.Most Excel experts love sharing their knowledge. So don’t be afraid to approach your local Excel Guru to ask questions or seek advice on how to tackle certain macro problems.