ASAP Utilities worksheet functions

You get several extra worksheet functions in Excel when you have ASAP Utilities installed. These are so called user defined functions (or UDF).

Added worksheet functions

=ASAPFullFileName()

Returns the full filename of your workbook. This is the name of the workbook including its path.
Example: D:\projects\active\balance.xls

=ASAPFileName()

Returns the name of your workbook.
Example: balance.xls

=ASAPFilePath()

Returns the path where your workbook is saved/located.
Example: D:\projects\active

=ASAPSheetName()

Returns the name of the sheet the formula is placed on.

=ASAPCountShades(range)

Returns the amount of colored cells in your range.
If you afterwards change the color in a cell, you have to press Control+Alt+F9 to have the formulas recalculated.

= ASAPCellColorIndex(range)

Returns the cell color index number of the first cell in your range.
If you afterwards change the color of the referenced cell, you have to press Control+Alt+F9 to have the formulas updated.
The color index number is the number that correspondences with a certain color in Excel. The color index is one of the 56 colors in Excel. The default color palette is:

=ASAPFontColorIndex(range)

Returns the font color index number of the first cell in your range.
If you afterwards change the fontcolor in a cell, you have to press Control+Alt+F9 to have the formulas updated.

=ASAPSumByFontColor(range, color number)

Returns the sum of all cells in your range that have the given font color index number. In this case the font color index is one of the 56 colors in Excel.

If you afterwards change the color in a cell, you have to press Control+Alt+F9 to have the formulas recalculated.

=ASAPSumByCellColor(range, color number)

Returns the sum of all cells in your range that have the given cell color index number. In this case the cell color index is one of the 56 colors in Excel. Useful combination with the function = ASAPCellColorIndex(range)
If you afterwards change the color in a cell, you have to press Control+Alt+F9 to have the formulas recalculated.

=ASAPCountByCellColor(Lookin_Range, Color_Index_Nr)

Counts the amount of cells in the selected range that have a certain cell color.
You can use this function in combination with the = ASAPCellColorIndex() function.

=ASAPCountByFontColor(Lookin_Range, Color_Index_Nr)

Counts the amount of cells in the selected range that have a certain font color. You can use this function in combination with the = ASAPFontColorIndex() function.

=ASAPGetComment(range)

Returns the content of the cell-comment of a given cell.
If you afterwards change the comment in a cell, you have to press Control+Alt+F9 to have the formulas updated.

=ASAPGetHyperlink(range, optional sNA)

This function returns the hyperlink from a cell. The hyperlink can be one of the following:

• existing file or web page
• place in your document
• e-mail address

You can optionally specify a string value for the sNA, this text will be displayed for cells without hyperlinks. If omitted cells the formula will give an empty result for cells without hyperlinks.
For example:

If you afterwards change the hyperlink in a cell, you have to press Control+Alt+F9 to have the formulas updated.

=ASAPSpellNumber()

This function spells out numbers.
The function has the following parameters: =ASAPSpellNumber(Number, language (EN/NL/DE), currency, optional strSingular, optional strPlural, optional strComma, optional strCentSingular, Optional strCentPlural)
Example:

=ASAPExtractNumbers(ref)

This function extracts the numbers from a given range or value.

=ASAPStripNumbers(ref)

This function strips the numbers from a given range or value.

=ASAPGetFontName(range)

This function returns the font name of a cell.

=ASAPGetFontSize(range)

This function returns the font size of a cell.

=ASAPGetNumberformat(range)

This function returns the number format of a cell.

=ASAPIsFormula(range)

This function returns true if the cell contains a formula or an array formula. Otherwise it returns false.

=ASAPCountChar(SearchText, SearchValue)

This function returns the number of times the "SearchValue" occurs in the "SearchText". This way you can for example count the number of commas in a cell. This function is case sensitive.

=ASAPExtractFilename(FullFilename, optional separator)

Extracts the file name from a full path & filename
Example
Cell B23 contains: D:\User Guide\screenshots\Sort random (shuffle).xls
=ASAPExtractFilename(B23) will return "Sort random(shuffle).xls"

=ASAPExtractFolderName(FullFilename, optional separator)

Extracts the folder name from a full path & filename
Example
Cell B23 contains: D:\User Guide\screenshots\Sort random (shuffle).xls
=ASAPExtractFolderName(B23) will return "D:\User Guide\screenshots"

=ASAPGetFormula(FormulaCell)

Returns the formula in a cell in local Excel language. Example: =GEMIDDELDE("A1:B12")
Returns empty if the cell does not contain a formula

=ASAPGetFormulaInt(FormulaCell)

Returns the formula in a cell in international notation. Example: =AVERAGE("A1:B12")
Returns empty if the cell does not contain a formula

=ASAPMergeCells(inputrange, delimiter, skipemptycells)

An easy alterative for the Excel =CONCATENATE() fuction. However the benefit of this ASAP Utilities function:

• you can specify a range, for example A1:G1
• you only have to specify a delimiter once
• by default empty cells will be ignored

=ASAPIsBold(range)

Returns true if the cell is bold, false if it isn't

Where to find these functions?

Once you have installed ASAP Utilities in Excel and see the ASAP Utilities menu you can use these worksheet functions.
There are two ways to use the functions:

1. You can use them by typing in their name in a cell.
2. You can select them from Excel's function box:
   In the menu choose: Insert > Function... (or use the shortcut key Shift+F3)
   
   In the dropdown select the category "User Defined":
   
   Now you can pick one of the ASAP Utilities worksheet functions. If there are more functions in the list you can distinguish the ones from ASAP Utilities because their names start with ASAP.
   

Remarks on distributing

If you use these worksheet functions in your worksheet there a few considerations to make if other people are going to work on the same workbook:

1. Everybody that works with the file using the ASAP Utilities worksheet functions should have ASAP Utilities installed as default add-in.
2. If somebody is going to work with you file but he/she doesn't have ASAP Utilities, you should convert the formula-results to their values (Copy » Paste Special » Values) or in ASAP Utilities:
   ASAP Utilities » Formulas » Convert formulas to their values

If you see the result of a function displayed as "#NAME?" this means that ASAP Utilities is not available. (In case you do have ASAP Utilities installed it can also mean there is a spelling-error in the formula name).

Remarks on calculation

Since Excel only recalculates a workbook if the value in a cell changes (most of the times), some of the new ASAP Utilities worksheet formulas don't get triggered to update. In that case you have to press Control+Alt+F9 to have the formulas updated.