PC World - USA (2020-03)

122 PCWorld MARCH 2020

HERE’S HOW HOW TO CREATE EXCEL MACROS

• Use the directional keys to navigate:
  Up, Down, Right, Left, End, Home, etc., and
  shortcut keys to expedite movement.


• Keep your macros small and focused
  on specific tasks. This is best for testing and
  editing (if needed). You can always combine
  these mini-macros into one BIG macro later
  once they’re perfected.


• Macros require “relative” cell
  addresses, which means you “point” to
  the cells rather than hardcode the actual
  (or “absolute”) cell address (such as A1, B19,
  C20, etc.) in the macro. Spreadsheets are
  dynamic, which means they constantly
  change, which means the cell addresses
  change.


• Fixed values and static information
  such as names, addresses, ID numbers, etc.
  are generally entered in advance and not
  really part of your macro. Because this data
  rarely changes (and if it does, it’s just to add
  or remove a new record), it’s almost
  impossible to include this function in a
  macro.


• Manage your data first: Add, edit, or
  delete records, then enter the updated
  values. Then you can execute your macro.

WHY STARTING WITH MINI-

MACROS IS EASIER

For this example, we have a store owner

who has expanded her territory from a single

store to a dozen in 12 different major cities.

Now the CEO, she’s been managing her

own books for years, which wasn’t an easy

task for a single store, and now she has 12.

She has to collect data from each store and

merge it to monitor the health of her entire

company.

We created a few mini-macros to perform

the following tasks:

1. Collect and combine the data from her
   12 stores into one workbook in a Master
   three-dimensional spreadsheet.


2. Organize and sort the data.


3. Enter the formulas that calculate the
   combined data.
   Once the mini-macros are recorded,
   tested, and perfected, we can merge them
   into one big macro or leave them as mini-
   macros. Either way, keep the mini-macros,
   because it’s much easier and more efficient
   to edit the smaller macros and re-combine
   them, than try to step through a long,
   detailed macro to find errors.
   We’ve provided a sample workbook for
   the above scenario so you can follow along
   with our how-to. Feel free to create your own
   spreadsheet too, of course (go.pcworld.
   com/wkbk).

PREP WORK: THE MASTER

SPREADSHEET

If you’re building your spreadsheets from the

ground up, start with the Master

spreadsheet. Enter the date formula in A1

and the store location in B1. See screen shot

opposite.