Add-ins
An add-in is a workbook that has been saved as an add-in (.xla format – select ‘Microsoft Excel Add-In’ for type under Save As). This workbook will now be hidden. Then add it via Tools>Add-ins>Browse (or select it from the list if you saved it under Users/[USER]/Application Data/Microsoft/AddIns).
Note that if you have the original workbook (e.g. personal.xls) as a hidden workbook, there will now be two projects in the VB Editor with the same name (though different file names).
PERSONAL.xls
To have PERSONAL.xls macros available, make sure the file is in the XLStart folder – Program Files (x86)/Microsoft Office/OfficeXX/XLStart. Then open it and hide it, and save changes. It should now open as a hidden file each time you start Excel.
Macros will now be usable as ‘personal.xls!macroname(…)’. To make the functions in personal.xls global (eliminating the need for the ‘personal.xls!’ prefix) you have to add it as a reference. Go to the VB editor and first rename the project to e.g. ‘projPersonal’ (unsaved projects can’t be referenced) by right-clicking and changing the project properties. Then go to Tools>References and find the project by name and check it. Alternatively, use Add-ins instead.
Functions
ISNUMBER(value) – The ISNUMBER() function can be used to check if a function returns a valid number or not. It can be used to check for #VALUE! errors. For instance, =FIND(“z”,”word”) returns #VALUE!. However, =IF(ISNUMBER(FIND(“z”,”word”)),FIND(“z”,”word”),0) returns 0, which is what you probably want. Note that there is also the similar ISERROR() function, however, that will also ignore #DIV/0!, #REF!, #N/A!, #NAME? and other errors that you probably don’t want to ignore.
DATEDIF(Date1, Date2, Interval) function – The datedif() function is undocumented, but available in Excel.
- Date1 must be earlier than Date2 else a #NUM! error will occur.
- The Interval can be “d” for days, “m” for months, and “y” for years.
- Example: =DATEDIF(“5/31/05″,”2/2/06″,”d”) will give a value of 247.
Lookup and Reference Functions
VLOOKUP(lookup_value, table, column_index, FALSE) – This function is good for data tables where there are multiples copies of the same columns associated with different data, and they are ordered different. Make sure to include the FALSE parameter, otherwise inexact matches will be counted.
MATCH(lookup_value, array, match_type) – Typically used with index(), to feed the row_num argument. Note that match_type should always be 0 if you want an exact match!
INDEX(array, row_num, column_num) – This function is good for table lookups where you have the index row number already, probably from another oclumn. Best used with match() feeding the row_num value.
Matrix Calculations
Excel can do matrix calculations (Excel calls them arrays). To add/subtract an m*n matrix with an n*p matrix, select a range of size m*p, then type the ‘=’ sign, and the formula should be selecting the m*n matrix’s range +/- the n*p matrix’s range. Then hit ‘CTRL+SHIFT+ENTER’, and the m*p range will be filled with the result values.
To do matrix multiplication, the formula should use the MMULT() function instead of +/-.
Miscellaneous
The & operator can be used to pass cell references as text, as well as for concatenation. For instance, the COUNTIF(Range, Criteria) function: if you do: =COUNTIF(A1:A5, “>B1”), it will compare with the text B1 rather than the cell value of B1. The correct way to do this is: =COUNTIF(A1:A5, “>=”&B1). A concatenation example that doesn’t make use of the CONCATENATE() function: =”apples”&C2&”oranges” will return: “apples[value of cell C2]oranges”
To disable automatic calculation, go to Ribbon>Excel Options>Formulas>Calculation options (in Word 2003, Tools>Options>Calculation>Calculation), and set to Manual. Then cell contents will not be formulaically recalculated until F9 is hit.
Excel automatically parses lots of strings as dates, such as turning “3/4” and “3-4” into “4-Mar”. To counter this, enter a space or apostrophe (‘) before the string. Note that this means the space or apostrophe is now part of that cell.
You may experience a situation where the forward slash key (/) seems to get stuck and keeps brining up the file menu. To disable it, go to Ribbon>Excel Options>Advanced>Lotus Compatibility and uncheck ‘Transition Navigation Key’ (in Word 2003, go to Tools>Options>Transitions>Microsoft Excel menu or Help key, and delete the ‘/’).