Chapter 11
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.
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.
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:
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.
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:
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:
www.excelguru.ca/blog
). In addition to his blog, Ken offers several learning resources for advancing your knowledge in Excel.http://chandoo.org/
) offers many free templates and article that are aimed at “making you awesome in Excel.”www.contextures.com
). With an alphabetized list of over 350 Excel topics, the site is sure to provide you with something of interest.www.dailydoseofexcel.com
). He is the king of Excel VBA blogging, with over ten years’ worth of articles and examples.www.mrexcel.com
).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.
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:
http://academy.excelhero.com/excel-hero-academy-tuition
http://chandoo.org/wp/vba-classes
https://exceljet.net
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.
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.
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.
13.59.26.221