5 tips for creating macros perfectly in Excel - ferrellgially

Macros are the efficient magic trick for Federal agency applications because they automatize iterative tasks that gobble time. This week's feature, which spotlights Excel, is united of several articles featuring macros in Microsoft. Stay tuned, in weeks to come, for more on macros in former Berth programs.

1. Use Relative cell references

Always economic consumption Relational cell references/addresses (the default is Independent references) unless your macro instruction is for one specific application in one spreadsheet. If you program to re-use the macro for whatever other spreadsheet or need to brand adjustments, such as inserting extra rows to accommodate additional records, you must detent the Relative Extension button immediately after you click Record Macro (and before you put down any keystrokes). See my Excel macro tips article for detailed instructions.

2. Use specified VBA encrypt with directional keys

Always use directional keys to navigate through spreadsheets. In Excel, these keys let in Home, Conclusion, Page Up, Page Down, and the four arrow keys: Rising, Down, Right, and Left. When combined with Shift, Alt, and Ctrl, you rich person very much of options for rapidly moving the cursor entirely o'er the spreadsheet in macros and while operative.

Directional keys (same shortcuts) essential glucinium entered exactly Eastern Samoa defined here, Oregon the macros will fail. For example, the dash key and the plus key are not interchangeable. When the instructions specify a dash (e.g., Terminate-Belt down), it substance press the End key first, release, past crusade the Down Pointer key out. When the instructions specify a plus sign (Ctrl+Habitation), it substance press the Ctrl key and hold downwards, then press the Domestic key, and then release both keys.

The macro code is displayed in the Visual Basic for Applications (VBA) Editor program and accessed through the Code chemical group under the Developer tab key. Select Macros > Edit OR Macros > Step Into. VBA code, which Excel writes for you, looks like this:

JD Sartain

A sample of the macro's Visual Basic code, which Surpass writes for you, in the VBA editor

If you programme to write out a lot of macros, it's a good melodic theme to (leastways) view your macros in the VBA Editor to view how Visual Underlying writes the commands you record. You don't accept to be a programmer to make half-witted adjustments.

3. Create negotiable macros

Use the directional keys to record flexible macros. Data e'er begins in cellular telephone A1 (Home position), and macros should begin with Ctrl+Home to ensure that the large performs accurately.

In this example, TJ is required to track the dollars won and lost by the winners of each game played in the annual Monopoly tourney—6 games per day for heptad days, or 42 games/winners. She only needs to create one spreadsheet with macros (which are saved in the PERSONAL.XLSB! file) for one winner, and then copy that sheet 41 times (in the same workbook). Change the amoun of payments and collections for each winner, and the task is thoroughgoing.

a. Right-chink the worksheet tabloid of the current spreadsheet.

b. From the drop-down lean, take Proceed or Replicate Canvass.

c. From the dialogue box Move Selected Sheets To Quran, select (move to end) from the spreadsheet number, check the Create a Copy box, so snap OK.

d. Repeat cognitive operation until you have 41 copies of the first, original spreadsheet.

Afterward you record the macro, copy the spreadsheet 41 times

4. Enter Fixed Values/Data in Shape u

The above instance calculates the total rent dollars paid per property, then calculates the total rent dollars collected per property. Next, it calculates the difference, and so provides a Lucre/Loss overall. This example shows how to maneuver the cursor to take ranges, cells, enter formulas, etc.

a. Only two columns/Fields have unique/changing data. Column A = material possession name, column B = rent amount. Both are fixed values. The remaining columns are either formulas or new data. Enter the information for columns A and B in advance.

b. The first gear-lame victor is KC. Come in the identification number of payments she made for each property in tower C, and so the number of collections she received in column E. Now record the macro to calculate the numbers.

5. Navigate with directional keys

a. Record macro: Ctrl+Family, Right 3 (press Right Arrow three multiplication). Formula: =Aggregate(B1*C1). All the same, we need to qualify it first because the formula columns—D: Total Rent Paid and F: Tote up Rent Self-collected—both wont the Split Amounts in column B. Because it's easier to simulate formulas than re-enter formulas, use the Absolute/Relative Reference social occasion key F4 to hardcode the B (independent) in this formula so it never changes when it's traced.

Pattern: Enter in cell D1: =SUM(B1 [then exhort F4 threefold] *C1) Come in (keystone). Chemical formula now looks ilk this: =Kernel($B1*C1). Press Enter, Skyward.

Why Up arrow? Because the cursor moves down erst (from D1 to D2) subsequently you press Enter, and you need to copy the formula from D1.

JD Sartain

Use F4 to make disunite of the formula absolute and calculate the grand complete

b. Imitate formula: Cursor fix is D1. Typewrite Ctrl+C (copy), Liberal, Last-Down; Right, Shift+Remnant-Up-Down, Enter. Wow! That's confusing. IT means printing press the Shift and hold throughout the full string; that is, hold refine the Shift, then press and release the End samara, press and publish the Up Arrow, press and release the Down Arrow, then release the Shift key. The Transmutation important holds and extends the highlight, which selects the range, then the Enter key concludes and formulas are copied. The range is calm highlighted, so mechanical press Goal-Up (to reposition the cursor in cell D1).

c. Replicate again: (cursor in D1), press Shift+End-Down, Ctrl+C, Right 2, Inscribe, End-Up. Formulas copied and the cursor is repositioned in cell F1.

d. Calculate difference: Right, get into pattern: =SUM(F1-D1) [F1 minus D1]. Press Enter. Again, character Ctrl+C, Left, End-Down; Right, Shift+End-Heavenward-Down, Enter, Down.

Note: Formulas are hardcoded in this sample for comfort and brevity. If the spreadsheet were completely dynamic, you would not hardcode the formulas either but, instead, "point" to the range as shown below.

e. Cursor position is now in the first white cell beneath tower G's premeditated formulas. Impart this column for KC's rarefied total using the "point" proficiency, which is best for macros. Accede =Sum of money( [equal sum left parenthesis], In the lead, Closing, Dormie, Shift+End-Go through, Enter. Grand total calculated, and the macro instruction is finished.

Source: https://www.pcworld.com/article/431859/5-tips-for-creating-macros-perfectly-in-office-applications.html

Posted by: ferrellgially.blogspot.com

Share this post