Excel macros are like mini-programs that perform repetitive tasks, saving you time and typing. For example, Excel takes less than a tenth of a second to compute a full, massive spreadsheet. It is the manual processes that slow you down. That's why you need macros to combine all these tasks in a single one-second transaction.
Excel Macros: Getting Started
We'll show you how to write your first macro. Once you realize how easy it is to automate tasks with macros, you'll never come back.
Here are some tips for preparing your data for macros:
- Always start your macro at its starting position (use the keyboard shortcut Ctrl + Pos to get there quickly).
- Use the directional buttons to navigate: Up, Down, Right, Left, End, Home, etc., and shortcuts to speed up the movement.
- Keep your macros small and focus on specific tasks. This is best for testing and editing (if necessary). You can always combine these mini-macros into a BIG macro when they are perfected.
- Macros require "relative" cell addresses ") Cell address (such as A1
- Fixed values and static information such as names, addresses, ID numbers, etc. are generally pre-entered and are not really part of your macro. Since this data seldom changes (and if so, you only need to add or remove a new record), it is almost impossible to include this function in a macro.
- Manage your data first: then add, edit or delete records Enter the updated values. Then you can run your macro.
Why It's Easier to Start with Mini-Macros
For this example, we have a shopkeeper who has expanded his territory from a single store to a dozen in 12 different cities. Now she is the managing director. She's been running her own books for years, which was not an easy task for a single store, and now she has twelve. She needs to collect and merge data from each store to monitor the health of her entire business
We've created a few mini-macros to perform the following tasks:
- Collect and combine the data from their 12 stores into one Workbook in a three-dimensional master table.
- Organize and sort the data.
- 19659006] Enter the formulas that compute the combined data.
Once the mini-macros have been recorded, tested and perfected, you can merge them into a large macro or leave them as mini-macros. Be sure to keep the mini-macros, because it's much easier and more efficient to manipulate and recombine the smaller macros than to look for a long, detailed macro to find bugs.
We have provided an example workbook for the above scenario so that you can follow with our guide. Of course, you can also create your own table.
In this Excel workbook you can practice creating and using macros. JD Sartain
Preparatory Work: The Master Spreadsheet
When you create your spreadsheets from scratch, you start with the master spreadsheet. Enter the date formula in A1 and the location in B1. See picture below.
Enter this date formula in cell A1: = Today () . Now the current date is always displayed in this cell. Be sure; However, your location (store name and number) is listed in B1.
. 2 Leave line 2 empty. Once the static data and the initial dynamic data are entered, line 2 is used for the totals. This may seem like a strange practice, but for macro spreadsheets this is the best way, as this line is stationary and always visible.
. 3 Next, enter the field names (and / or other field-specific information) in line 3 (for example, from A3 to J3, or how many fields your spreadsheet needs).
Tip: This allows you to text wrap the information in each cell when the data is long. For example, you can put all of your business contact information in a cell and wrap the rows. Press Alt + Enter to insert additional lines into the cells.
. 4 Next, enter the static data in column A. This is the record information in your spreadsheet that rarely changes. If your company uses product numbers or ID codes that are unique because there is only one code per product, enter the one in column A starting at line 4 (do not skip line 5). Other static data fields include the product description, the product price, the percentage of sales tax, and so on.
Do not skip rows and leave blank for column A. Each row must contain the data of the unique field – if not a product code, then another unique identifier. We do this for two reasons:
- Column A is the main navigation column. The macro moves and navigates through the spreadsheet, based on the starting position (A1) and column A. The macro fails if you ignore this rule because blank lines interfere with the actions of the directional keys.
- If you want to create multiple / relational tables later for pivot reports, you will need a unique key field to connect the related tables. For more information, see our Excel Pivot Table Tutorial.
. 5 Normally the product description is in column B, the quantity sold in column C, product price in column D, extended cost in E, discounts in F, sales tax in G and sums in H. The column totals are at the top of row 2, remember? Format the column widths based on the length of the field names and adjust the row height to 20 for all rows. Change the orientation from top to bottom in center, select the desired orientation (left, right, center) and format the worksheet "styles" to your liking.
. 6 Do not move anything after setting up the master database. If you need to add fields, use the command Insert Column . For example, if you want to add a second sales tax, position the cursor anywhere in the H (totals) column and click the tab: Start> Insert> Insert Insert Columns . The new column is displayed to become the new H column, and the sum column changes to I. This operation does not affect the macro.
. 7 The same procedure applies to rows. Normally, I'd like to remind you to insert rows within the active dbspace. For example, if the formula is = SUM (B3: B20) and you insert or use a line outside the formula range, such as B21, the data in the new record will not be included in the formula and therefore will not calculate.
8. Now we set up this formula area. Enter the following formulas in line 2 (this is a one-time task):
C2: = SUM (C4: C500)
E2: = SUM (E4: E500)
F2: = SUM (F4: F500)
G2: = SUM (G4: G500)
H2: H2: = SUM (H4: H500)
Next, enter the following formulas in these columns (also a one-time event):
E4: = SUM (C4 * D4) then copy from E4 to E5: E500
F4: = SUM (E4 * 10%) the current percentage for the discount in your store, then copy from F4 to F5: E500  G4: = SUM (E4-F4) * 6.25 where 6.25 is the sales tax in your region, then copy from G4 to G5: G500
H4: = SUM ( E4-F4 + G4) then copy from H4 to H5: E500
Now you have all spreadsheet formulas you only need to enter the amount (column C) for each sold computer (daily, weekly, or monthly). When prices change, enter the new prices in column D. The remainder of this database contains all formulas or static information.
. 9 As shown above, for macros spreadsheets, you can set the formula area to be many rows behind the last record. So you can just add new records at the end without worrying about adjusting the range. Because the macro sorts the database, the new records are moved to the correct position. The spreadsheet data in our example ends in line 210. The formula area extends to line 500 so that the next new record in line 211 can be safely added.
10th After you define the spreadsheet and set it up with the structure, static data, and correct formulas, make 12 copies on worksheets 2 through 13. Edit the tabs below to identify each memory. Change the name of the sheet1 tab to Master as this is your master database file.
. 11 Change the location information in line 1 to identify the storage information (which matches the storage on the tab) in all 12 spreadsheets. Next, send an electronic copy of each store's table to each store manager. For example, send the Boston Journal to Boston, the Dallas Journal to Dallas, and so on.
Their copies include the spreadsheet formulas that are applied to their individual worksheets (but not the formulas of the combined worksheets in the workbook).  12. The macro contains the formulas for the master. The master is the spreadsheet for the sum of all transactions. If you assemble all the data and run the master macros AND you also manage a single store, you must use one of the 12 sheets that you copied for your store. The master is only for the grand totals.
. 13 As branch offices e-mail their individual tables, it is safer to manually copy the individual worksheets from the workbooks of the 12 branches.
Now we can program a macro! Just click on the next page.