Excel 2019 Power Programming with VBA - Michael Alexander

Скачать книгу

you need a macro that refreshes all the pivot tables in your workbook and clears all of 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. Once you've stopped recording, you can review the macro and make any changes that you deem necessary.

      Use the Help system

      To a new Excel user, the Help system may seem like a clunky mechanism that returns a perplexing list of topics that has nothing to do with the topic you're searching. The truth is, however, once 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 just need to remember two basic tenets of the Excel Help system: location matters when asking for help, and you need to be connected to the Internet to use Excel's Help system.

      Location matters when asking for help

      In Excel, there are actually 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 is 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'll need to be in the Visual Basic Editor while performing your search. This will ensure that your keyword search is performed on the correct Help system.

      You need to be connected to the Internet

      When you search for help on a topic, Excel checks to see if you're connected to the Internet. If you are, Excel takes you to the MSDN website where you can search for the topic on which you need help. If you aren't connected to the Internet, Excel gives you a message telling you that you need to be online to use Help.

      Use the Object Browser

      The Object Browser is a handy tool that lists every property and method available for every object. When you are in the VBE, you can bring up Object Browser in any of the following three ways:

       Press F2.

       Choose View ➪ Object Browser.

       Click the Object Browser button on the Standard toolbar.

 Screenshot of the Object Browser that is a great reference source.

      The drop-down list in the upper-left corner of Object Browser includes a list of all object libraries to which you have access:

       Excel itself

       MSForms (if user forms are utilized in your workbook)

       Office (objects common to all Microsoft Office applications)

       Stdole (OLE automation objects)

       VBA

       The current project (the project that's selected in Project Explorer) and any workbooks referenced by that project

      Your selection in this upper-left drop-down list determines what is displayed in the Classes window, and your selection in the Classes window determines what is visible in the Members Of panel.

      After you select a library, you can search for a particular text string to get a list of properties and methods that contain the text. You do so by entering the text in the second drop-down list and then clicking the binoculars (Search) icon.

      1 Select the library of interest.If you're not sure which object library is appropriate, you can select <All Libraries>.

      2 Enter the object you're seeking in the drop-down list below the library list.

      3 Click the binoculars icon to begin the text search.

      Object Browser may seem complex at first, but its usefulness to you will increase over time.

      Pilfer code from the Internet

      All the macro syntax you will ever need has likely 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 are stuck trying to create a macro for a particular task, fire up your favorite online search engine and simply describe the task you are trying to accomplish. For the best results, enter Excel VBA before your description.

      For example, if you are trying to write a macro that deletes all the blank rows in a worksheet, search for Excel VBA delete blank rows in a worksheet. You can bet two months' salary that someone out there on the Internet has tackled the same problem. Nine times out of 10, you will find some example code that will give you the nugget of information you need to jump-start some ideas for building your own macro.

      Leverage user forums

      If you find yourself in a bind, you can 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 advice 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.

      There are many forums 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:

       Always read and follow the forum rules before you get started. These rules often include advice on posting questions and community etiquette guidelines.

       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 that 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 either to 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.

      Visit expert blogs

      There are a few dedicated Excel gurus who share their knowledge through blogs. These blogs are often treasure troves of tips and tricks, offering nuggets that can help build up your skills. Best of all, they are free!

      Although

Скачать книгу