In This Chapter

Let Excel write the macro for you

Use the VBA Help files

Pilfer code from the Internet

Leverage user forums

Visit expert blogs

Mine YouTube for video training

Attend live and online training classes

Learn from Microsoft Office Dev Center

Dissect other Excel files in your organization

Ask your local Excel genius

No one can become a macro expert in one day. VBA is a journey of time and practice. The good news is that there are plenty of resources out there that can help you on your path. In this chapter, you’ll discover ten of the most useful places to turn to when you need an extra push in the right direction.

Letting Excel Write the Macro for You

One of the best places to get macro help is Macro Recorder in Excel. When you record a macro with Macro Recorder, Excel writes the underlying VBA for you. After recording, you can review the code; see what the recorder is doing, and then try to turn the code it creates into something more suited to your needs.

For example, let’s 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 Macro Recorder and record yourself refreshing all the pivot tables and clearing all the filters. When you stop recording, review the macro and make any changes you deem necessary.

Using the VBA Help Files

To a new Excel user, the Help system may seem like a clunky add-on that returns a perplexing list of topics that has nothing to do with the original topic being searched. However, if you learn how to use the Excel Help system effectively, it will often be the fastest and easiest way to get help on a topic.

You need to remember two basic tenants of the Excel Help system:

• Location matters when asking for help. Excel has two Help systems. One provides help with Excel features and the other provides help with VBA programming topics. Instead of doing a global search with your criteria, Excel throws your search criteria only against the Help system relevant to your current location, so the help you get is determined by the area of Excel in which you’re working. If you need help on a topic that involves macros and VBA programming, for example, you’ll need to be in VBA Editor when you perform your search.
• Online help is better than offline help. When you search for help on a topic, Excel checks to see whether 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 stored locally with Microsoft Office. One way to maximize the help you get in Excel is to use the online help. Online help is generally better than offline help because the content you find with online help is often more detailed and includes updated information, as well as links to other resources not available offline.

Pilfering Code from the Internet

The dirty secret about programming in the Internet age is that there is no longer any original code. All the macro syntax that anyone will ever need has been documented somewhere on the Internet. In many ways, programming has become less about the code one creates 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, fire up your favorite online search engine and describe the task you're trying to accomplish. 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 that someone on the Internet has tackled the same problem, and you'll find example code that will give you the nugget of information you need to jump-start ideas for building your own macro.

Leveraging User Forums

If you find yourself in a bind, you can post your question in a forum and get customized guidance. User forums are online communities that revolve around a particular topic. In these forums, you can post a question and experts will offer advice. 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, enter the words Excel forum in your favorite online search engine.

Here are a few tips for getting the most out of user forums:

• Read and follow the forum rules before you get started. These rules often include advice on posting question and community etiquette guidelines.
• Use a concise and accurate subject title for your question. Don’t create forum questions with vague titles such as Need advice or Please help.
• Keep the scope of your question narrow. Don’t ask questions like, "How do I build an invoicing macro in Excel?"
• Be patient. Remember that the folks answering your question 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. Take a moment to post a thank-you to the expert who helped you out.

Visiting Expert Blogs

Several dedicated Excel gurus 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 will not necessarily speak to your particular needs, they do 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 on the Internet today:

• ExcelGuru: Ken Puls is a Microsoft Excel MVP who shares knowledge on his blog (www.excelguru.ca/blog). In addition to his blog, Ken offers several learning resources for advancing your knowledge in Excel.
• Chandoo.org: Purna “Chandoo” Duggirala is a Microsoft Excel MVP out of India who burst on the scene in 2007. His innovative blog (http://chandoo.org/) offers many free templates and article that are aimed at “making you awesome in Excel.”
• Contextures: Debra Dalgleish is a Microsoft Excel MVP and the owner of a popular Excel site (www.contextures.com). With an alphabetized list of over 350 Excel topics, the site is sure to provide you with something of interest.
• DailyDose: Dick Kusleika is the owner of the longest-running Excel blog (www.dailydoseofexcel.com). He is the king of Excel VBA blogging, with over ten years’ worth of articles and examples.
• MrExcel: Bill Jelen is a 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 (www.mrexcel.com).

Mining YouTube for Video Training

Some of us learn better if we watch a task being done. If you find that you absorb video training better than online articles, consider mining YouTube. You might be surprised at how many free high-quality video tutorials you can find, run by amazing folks who have a passion for sharing knowledge.

Go to www.YouTube.com and search for the words Excel VBA.

Attending Live and Online 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 can provide a wealth of ideas and new tips. If you thrive in the energy of live training events, consider searching for Excel classes.

Here are a few site that provide excellent instructor-led Excel courses:

• Excel Hero Academy: http://academy.excelhero.com/excel-hero-academy-tuition
• Chandoo.org: http://chandoo.org/wp/vba-classes
• Exceljet: https://exceljet.net

Learning from Microsoft Office Dev Center

The Microsoft Office Dev Center site is dedicated to helping new developers get a quick start in programming Office products. You can get to the Excel portion of this site by going to https://msdn.microsoft.com/en-us/library/office/fp179694.aspx.

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, and step-by-step instructions.

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. Open Excel files that contain macros, and see how others in your organization use them. 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 Genius

Do you have an Excel genius in your company, department, organization, or community? Make friends with that person today. You'll have 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 out advice on how to tackle certain macro problems.