How to start building a macro arsenal

Face it, macros will make your life easier. You might not know how to write them yet but it shouldn’t deter you from using them. This post will make it easy for you to start collecting macros and keeping them handy for when the need strikes.

First, you will need a personal macro workbook. Macros are stored in workbooks and I’ll give you three types of workbooks:

  • The plain old workbook
  • The personal macro workbook
  • The add-in, which enables easy distribution of macros and functions. I won’t get into any more details on the add-ins here.

You should only store in the workbook macros that are either only relevant to that specific workbook, or are triggered by specific events on the workbook, or that need to accompany the workbook if you were to send it to somebody else.

All other macros should go to your personal macro workbook.

Do you have a personal macro workbook? If you don’t know the answer, it’s probably no, but I’ll show you how to set it up. The easiest way is to record a macro. A few options for this:

  • Use the shortcut Alt+t m r (for Tools \ Macro \ Record).
  • Click the Record Macro button on the Developer tab in the ribbon. It’s not installed by default, but you can right-click the ribbon, “Customize the Ribbon” and check the Developer box in the Main Tabs window to install it.
  • Right-click the status bar on the bottom left of your excel window (it should say Ready) and select Macro Recording, You now have a new status bar button to start (and stop) recording macros.

Once you’ve picked one of those options, you’ll see the following window:

Record Macro window

In the Store Macro In dropdown, select Personal Macro Workbook, then click OK. A small blue square (Stop Recording) will appear on the bottom left of your screen on the status bar. Click it (or use the same shortcut Alt+ t m r) to stop recording the macro.

You now have a personal macro workbook, stored somewhere in the depths of your user profile (something like C:\Users\UserName\AppData\Roaming\Microsoft\Excel\XLSTART depending on your version of windows). To see it, you’ll have to venture in the Visual Basic Editor, using the Alt+F11 shortcut, or press the Visual Basic button in the Developer tab of the Ribbon (see above). Expand the VBA Project (PERSONAL.XLSB), expand the Modules and you’ll see the first Module of your Personal Macro workbook, as shown in the screenshot below. Double-click to view the code, add any macros you want, and don’t forget to save.

Visual Basic Editor Screenshot

You can now close the Visual Basic Editor, your Personal Macro workbook should open whenever you open Excel and you can start filling it with wonderful macros!

Try it, you might like it.

Thomas

Advertisements

Reports of all stripes with conditional formatting

Hi all,

Imagine you want alternating rows (or columns) in a report to have alternating colors. I don’t know, maybe you’re missing the days of tractor-feed paper.

One option is to convert your data in a table (Ctrl+T, or Insert \ Table). Other advantages come with the table like the ability to reference ranges in formulas with names instead of addresses, but the table doesn’t work in all cases (but I definitely should write something about tables soon).

An easy option, if you can’t use tables is to use conditional formatting.:

  • Select your data
  • Home \ Conditional Formatting \ New Rule
  • Use a formula to determine which cells to format
  • Use the following formula =ISODD(ROW())
  • Set your format, validate, you’re all set (you can also use ISEVEN, and swap COLUMN() for ROW() if you want vertical rather than horizontal stripes).

Image

Try it, you might like it.

Thomas

Find the text cells masquerading as numbers.

From time to time, an import gone awry will leave you with values that look like numbers but are actually text. They won’t total, they won’t compute in formulas and won’t be picked up by a pivot table.

If you have a whole column of them, usually a Data \ Text to columns will take care of the issue, but sometimes that’s not your best tool.

A quick way to find out which cells are not really numbers (and a quick way to find lots of other types of cell) is to use the Goto…Special function. Select the range containing the culprits, press F5 and you have the Go to menu, press the Special button and you will get all the Special Cells option. Select the appropriate one, in this case, Constants \ Text and voilĂ , the text cells are selected. You can now manually address the issues.

Try it, you might like it.

Thomas

Quick Balance Sheet Account Matching

Hi all,

If your accounting package doesn’t let you apply g/l entries to other g/l entries, or if you just haven’t been doing it, reconciliation of balance sheet can often be an arduous task, with dozens, hundreds or thousands of line to match before you can find out your outstanding items.

The following formulas will help you make a first pass of reconciliation before you dig in further. The goal is to take care of all the one-for-one reversals. To find them, we check the amount column, and for each line, we check if the sum of amounts matching that line’s amount, either with a negative or positive sign, is equal to zero.

First, the two step method, using table references rather than range references. If the [ ] and [@..] confuse you, check out this tutorial on office.microsoft.com.

  • Create a column with the absolute value of the line’s amount, using the ABS() function: =ABS([@Amount])
  • Create a column with a formula summing the Amount column if the Absolute values match: =SUMIF([ABS],[@ABS],[Amount])=0

Second the one step method, using sumproduct:

  • =SUMPRODUCT((ABS([Amount])=ABS([@Amount]))*[Amount])=0

Both of these methods will return a list of TRUE / FALSE values: TRUE if the amount is matched, FALSE if it isn’t. You can filter out the TRUE value and focus your attention on the FALSE. Your work isn’t done yet, but it got started faster. Check out the example file (quick balance sheet matching) to see it in action.

Try it, you might like it,

Thomas

Quick True / False formula

Want a True / False result on your formula (which could be pretty helpful on a quick autofilter analysis)?
The long way?

=IF(MyFormula=0, TRUE, FALSE)

The quick way?

=MyFormula=0

You can use operators directly without an IF statement, and mix and match just like you would in the first part of an IF statement, e.g.
=AND(SUM(A1:A5)>5,SUM(B1:B5)<12)

Try it, you might like it.

Thomas

Put borders around separate values using Conditional formatting

Just a quick one to show you that conditional formatting is not only about coloring cells. Assuming you have a table with distinct values in column A, e.g. a customer code, a blend numbers, or anything else (see screenshot 1). You’d like to have borders between each category, just to make it neat. Just select the whole table, add a new rule for conditional formatting (see screenshot 2), use formula =$A2<>$A1 ($ are important), set a top border, validate. You’re done, see screenshot 3.

Screenshot 1, before:

Image

Screenshot 2, Conditional formatting with formula:

Image

Screenshot 3, after:

Image

Try it, you might like it.

Thomas

Automatic increment

By now, you probably know how to automatically increment series, enter the first two cells of the series, select them, place your pointer on the bottom right corner (until it turns into cross) and drag with a left click.

Now, for dates, you can do the same and more by just typing the first date (remember Ctrl+; will enter today’s date) and dragging with the right click. When you release, you can pick your increment between days, weekdays, month and year. You can also pick Series if you need more flexibility.

right click drag

Try it, you might like it.

Thomas