Record Macros in a Personal Macro Workbook

When recording macros in Excel you have the option to either save the macro in the current workbook, a new workbook, or the 'Personal Macro Workbook'. The Personal Macro Workbook is stored at this location:

C:\Users\Your name\AppData\Roaming\Microsoft\Excel\XLSTART . It can be used as a 'storehouse' for macros that can accessed from any workbook.

On the Developer tab when you click on 'Record Macros' you get the option to choose the macro's name and then select where you want to save it.

You then perform a series of actions and click on Stop Recording on the Developer tab. Before you can begin recording if you have selected 'Personal Macro Workbook' you may get this error message:

In order to get around this and successfully save your new macro go to File . . . Options . . Addins. In the Manage menu at the bottom select 'Disable Items' and click 'Go'. You should see the option to enable the disabled Personal Macro Workbook.

Note that you are warned it may be necessary to restart Excel in order for the changes to take effect. After doing so, you should then be able to successfully record a macro in the Personal Macro Workbook. This workbook file will be displayed in Project Explorer in Visual Basic.

Serial Numbers

Excel uses a serial number system to represent dates and times. January 1, 1900 is arbitrarily assigned the number 1. Time is expressed with decimal points - noon, the midway point during the day, is assigned 0.5. So the date 12/31/16 5:00 PM . . .

. . . would be represented as 42735.7083333333 - 5 PM being a little bit more than 70% way through the day, and December 31, 2016 being 42,735 days since the beginning of the 20th century.

If you already have a date entered in a cell the YEAR, MONTH, and DAY functions can be used to extract one of these values.

The WEEKDAY function will indicate the day of the week as numeric value. If you reference a date followed by a 1, Sunday will be counted as the first day; followed by a 2, Monday will be counted as the first day. A 3 given as the 'return type' will count Monday as the first day but start at 0.

INDEX and MATCH

Here's a refresher course on how the INDEX and MATCH formulas work in Excel.

If you insert the MATCH formula at Formulas . . . Lookup & Reference

. . . you'll be given the option to:

1. lookup a value

2. in a selected array

3. according to a match type -

0 for an extact match

1 for the largest value that is less than or equal to the lookup value.

-1 for the smallest value that is greater than or equal to the lookup value.

When the match type is 1 the array must be in ascending order.

When the match type is 0 the array must be in descending order.

In this example we see that the formula:

=MATCH(Q2,L:L,1)

. . . returns the row number of the first entry in column L that is equal to or greater than the value Q2. Note that the HR column is sorted in ascending order. If it is sorted in the opposite order the value will not change, and will be incorrect.

With the HR column sorted in descending order we can use the formula:

=MATCH(Q3,L:L,-1)

. . . to find the row on which the lowest number of home runs equal to or greater than the value in Q3 appears. So this data (which only goes through the 1988 MLB season) lists 17 players who have hit 50 or more home runs in a season.

Wildcard characters (* for multiple characters; and ? for single characters) can be used with the MATCH formula - but only when the lookup value is 0. See for example, =MATCH("fox*",A:A,0)

MATCH formula are usually nested inside INDEX formulas. See the Tip of the NIght for August 17, 2015. Knowing the row number where a value appears is not as helpful as knowing which value appears on that row. So if we edit the first formula to be:

=INDEX(A:A,MATCH(Q2,L:L,1))

. . . we get the name of the player who hit the highest number of home runs less than or equal to 60.

When entering an INDEX formula from the 'Lookup & Reference' menu note that you'll have the option to structure the formula in one of two ways:

You can list an array, then a row number and column number

. . . to get this formula: =INDEX(A:L,65532,12). Here it is used to simply pinpoint a particular spot in an array.

The second option (for reference, row_num, column_num, area_num) will give the same result if the area_num is left blank. So this entry:

. . . generates the same formula: =INDEX(A:N,65532,12) and result, 58.

The area_num gives the option of searching through in multiple arrays and then specifying which one a value should be returned from. So in this example:

The formula =INDEX((H1:H3, J1:J3), 3, 1, 2) is returning the third row and first column of the second array.

We can also enter values directly in an INDEX formula. For example:

=INDEX({"Boston","Philadelphia","Detroit";"Red Sox","Phillies","Tigers";"Williams","Klein","Greenberg"},0,3)

If you select multiple cells and then enter the formula in the formula bar, it will be generate entries from each of the arrays. This is an array formula so you need to press CTRL + SHIFT + ENTER.