Macros 2

Download

HTML can be useful for transferring content to other online publishing platforms, typically by copy/pasting to the new platform.

<p id="p_ipNu" name="p_ipNu">This chapter will work more closely with Visual Basic (sometimes called Visual Basic for Applications or VB). First, we'll record another macro similar to the previous chapter that creates a basic table utilizing various formatting features.</p>
<div class="callout default" data-callout-custom-class="default">
<h3 id="h3_GfaW" name="h3_GfaW">Practice Spreadsheet</h3>
<p id="p_hJzy" name="p_hJzy">Use the workbook you created in the <a href="https://books.byui.edu/bus_115_business_app/macro_1" target="_blank" rel="noopener" data-shortened-url="https://books.byui.edu/-BTY" data-unshortened-url="https://books.byui.edu/bus_115_business_app/macro_1">Macros 1</a> chapter. </p>
</div>
<ol id="ol_hchu" name="ol_hchu">
<li id="li_Fztj" aria-level="1" name="li_Fztj">Press the <strong>Record Macro</strong> button from the <strong>Developer</strong> toolbar.</li>
<ol style="list-style-type: lower-alpha;" id="ol_BiIE" name="ol_BiIE">
<li id="li_Hdkj" aria-level="2" name="li_Hdkj">For the purpose of this demonstration, the macro settings can remain as their default.</li>
</ol>
<li id="li_gLnz" aria-level="1" name="li_gLnz">Type the letters <strong>A–F</strong> into separate cells down the left side of the table's column.</li>
<li id="li_mcZJ" aria-level="1" name="li_mcZJ">Type the numbers <strong>1–5</strong> into separate cells across the table's top row.</li>
<li id="li_bxFz" aria-level="1" name="li_bxFz">Format the table's cells with borders.</li>
<li id="li_aSnm" aria-level="1" name="li_aSnm">Group columns <strong>I</strong> and <strong>J</strong>.</li>
<ol style="list-style-type: lower-alpha;" id="ol_nejV" name="ol_nejV">
<li id="li_caqv" aria-level="2" name="li_caqv">Select the I and J columns.</li>
<li id="li_kKXM" aria-level="2" name="li_kKXM">Go to the <strong>Data</strong> toolbar and press the <strong>Group</strong> button.</li>
</ol>
<li id="li_mkID" aria-level="1" name="li_mkID">Type a title for the table in the top left cell.</li>
<ol style="list-style-type: lower-alpha;" id="ol_yFdF" name="ol_yFdF">
<li id="li_iWpR" aria-level="2" name="li_iWpR">For the purpose of this demonstration, the title can simply be "Table title."</li>
<li id="li_ZILK" aria-level="2" name="li_ZILK">The title needs to be in the row above the table's row of numbers.</li>
</ol>
<li id="li_NWng" aria-level="1" name="li_NWng">Format the table title's row with <strong>Heading 1</strong>.</li>
<li id="li_BYiC" aria-level="1" name="li_BYiC">Format the number and letter labels in <strong>bold</strong>. (See <strong>Figure 29.1</strong>)</li>
<li id="li_cCBp" aria-level="1" name="li_cCBp">Press the <strong>Stop Recording</strong> button from the <strong>Developer</strong> toolbar.</li>
</ol>
<figure class="col-12 text-center"><img src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_29.1.png" class="img-fluid" alt="BUS115_image_29.1.png" id="img_dAKy" name="img_dAKy">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 29.1</em></span></figcaption>
</figure>
<div class="clearfix"> </div>
<p id="p_DouI" name="p_DouI">Additionally, we will record a macro to remove the table.</p>
<ol id="ol_ffsH" name="ol_ffsH">
<li id="li_uaRI" aria-level="1" name="li_uaRI">Press the <strong>Record Macro</strong> button from the <strong>Developer</strong> toolbar.</li>
<li id="li_aQxm" aria-level="1" name="li_aQxm">Ungroup columns <strong>I</strong> and <strong>J</strong>.</li>
<ol style="list-style-type: lower-alpha;" id="ol_oMYw" name="ol_oMYw">
<li id="li_BihI" aria-level="2" name="li_BihI">Select columns <strong>I</strong> and <strong>J</strong>.</li>
<li id="li_XZcP" aria-level="2" name="li_XZcP">Go to the <strong>Data</strong> toolbar and press the <strong>Ungroup</strong> button.</li>
</ol>
<li id="li_jvfE" aria-level="1" name="li_jvfE">Select all of the table’s cells and press the <strong>Delete</strong> key.<br><em>Note: </em>You will probably notice some of the formatting remains. The delete key removes cell values, but it does not delete formatting. Continue with the next steps to clear formats. (See <strong>Figure 29.2</strong>)<br>
<figure class="col-12 text-center"><img src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_29.2.png" class="img-fluid" alt="BUS115_image_29.2.png" id="img_WMLr" name="img_WMLr">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 29.2</em></span></figcaption>
</figure>
<div class="clearfix"> </div>
</li>
<li id="li_yHnm" aria-level="1" name="li_yHnm">Go to the <strong>Home</strong> toolbar.</li>
<li id="li_ituQ" aria-level="1" name="li_ituQ">Press the <strong>Clear</strong> button and select <strong>Clear Formats</strong>. (See <strong>Figure 29.3</strong>)</li>
<li id="li_Byar" aria-level="1" name="li_Byar">Press the <strong>Stop Recording</strong> button from the <strong>Developer</strong> toolbar.</li>
</ol>
<figure class="col-12 text-center"><img src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_29.3.png" class="img-fluid" alt="BUS115_image_29.3.png" id="img_xYBN" name="img_xYBN">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 29.3</em></span></figcaption>
</figure>
<div class="clearfix"> </div>
<h2 id="h2_Szbd" name="h2_Szbd">Visual Basic</h2>
<p id="p_nyxr" name="p_nyxr">Next, we'll look at the code composing the macros. Press the <strong>Visual Basic</strong> button from the Developer toolbar to open the code in Visual Basic. Here, we can review, edit, and create the code used in Excel macros. (See <strong>Figure 29.4</strong>)</p>
<figure class="col-12 text-center"><img src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_29.4.jpg" class="img-fluid" alt="BUS115_image_29.4.jpg" id="img_tpVo" name="img_tpVo">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 29.4</em></span></figcaption>
</figure>
<div class="clearfix"> </div>
<h2 id="h2_CInD" name="h2_CInD">Organizing Code</h2>
<p id="p_VuZm" name="p_VuZm">Organizing code by inserting notes is an important practice for you and others to make sense of the code. Notes are written beginning with a single apostrophe ( <strong>'</strong> ) which the program recognizes as separate from the macro code. If text is written without an apostrophe, the text will be colored red to indicate an error because it is not actual code. (See <strong>Figure 29.5</strong>)</p>
<figure class="col-12 text-center"><img src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_29.5.png" class="img-fluid" alt="BUS115_image_29.5.png" id="img_EIZs" name="img_EIZs">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 29.5</em></span></figcaption>
</figure>
<div class="clearfix"> </div>
<h2 id="h2_qCGz" name="h2_qCGz">Editing Code</h2>
<p id="p_LiBF" name="p_LiBF">Recording macros can be helpful for completing repetitive tasks. However, the recorded macro will likely contain a lot of extra code as a result of selecting cells prior to performing a specific action. In <strong>Figure 29.6 </strong>below, we can see each action begins with identifying rows, columns, or a range of cells and performing the action on the selected cell(s) (for example, <strong>Range("E3:L12").Select</strong>). As in<strong> Figure 29.7</strong>, the code can be simplified by removing the selection and performing the next action on the designated cell(s) directly (for example, <strong>Range("E3:L12").ClearContents</strong>).</p>
<p id="p_Rree" name="p_Rree">We can take it a step further by simplifying the clear contents and clear formats lines. Instead of performing the actions to clear content and format separately, the <strong>ClearContents</strong> action can be changed to <strong>Clear</strong>, and the code will clear everything and the clear formats line can be deleted.</p>
<figure class="col-12 text-center"><img src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_29.6.png" class="img-fluid" alt="BUS115_image_29.6.png" id="img_MJNK" name="img_MJNK">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 29.6</em></span></figcaption>
</figure>
<div class="clearfix"> </div>
<figure class="col-12 text-center"><img src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_29.7.png" class="img-fluid" alt="BUS115_image_29.7.png" id="img_IbzB" name="img_IbzB">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 29.7</em></span></figcaption>
</figure>
<div class="clearfix"> </div>
<h2 id="h2_SMRa" name="h2_SMRa">Debugging Code</h2>
<p id="p_Eifd" name="p_Eifd">There will be situations wherein misspellings, changes, or other continuity problems between the macro code and the spreadsheet cause a macro to fail. In the event of an error, Visual Basic will produce an error message and prompt the user to <strong>End</strong> or <strong>Debug</strong> the macro. (See <strong>Figure 29.8</strong>)</p>
<figure class="col-12 text-center"><img src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_29.8.png" class="img-fluid" alt="BUS115_image_29.8.png" id="img_FdMv" name="img_FdMv">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 29.8</em></span></figcaption>
</figure>
<div class="clearfix"> </div>
<p id="p_UiWy" name="p_UiWy">Pressing <strong>Debug</strong> will direct the user to the line of code that failed. The error will be highlighted yellow with an arrow indicating the line of code. In the following example, the macro attempted to select a range labeled as "abc." However, the range didn't exist. It may have been a typing error, or the user forgot to update the desired cell(s) with the indicated label. The code or the spreadsheet can be modified to correct the error. (See <strong>Figure 29.9</strong>)</p>
<figure class="col-12 text-center"><img src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_29.9.png" class="img-fluid" alt="BUS115_image_29.9.png" id="img_YIkx" name="img_YIkx">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 29.9</em></span></figcaption>
</figure>
<div class="clearfix"> </div>
<div class="callout default" data-callout-custom-class="default">
<h3 id="h3_dfNx" name="h3_dfNx">Supplemental Resource</h3>
<p id="p_yqwE" name="p_yqwE"><iframe id="kaltura_player_1636661561" src="https://cdnapisec.kaltura.com/p/1157612/sp/115761200/embedIframeJs/uiconf_id/33020032/partner_id/1157612?iframeembed=true&playerId=kaltura_player_1636661561&entry_id=1_2zhrpa9p" width="480" height="270" frameborder="0" allowfullscreen="allowfullscreen"></iframe></p>
</div>