
Current Special! Complete
Excel Excel Training
Course for Excel 97  Excel 2003, only $145.00. $59.95 Instant
Buy/Download, 30 Day Money Back Guarantee
& Free Excel Help for LIFE!
 Custom Functions Introduction
 Custom Function Negatives
 Adding Descriptions to Custom Functions
 Two Column Lookup to Find N'th Occurrence
 Sum cells by color or count cells by color
 Sum Every Nth Row Or Cell
 Sum The Top/Bottom X Numbers
 Sum Excel Ranges Diagonally
 Sum With Up To 5 Conditions
 A Single Function That Will SUM or COUNT Cells By Their Fill Color
 Count or Sum Specified Number In Cell Housing Many Numbers
 Count Only One Occurrence Of Repeated Entries
 Count Whole Words
 Lookup any occurrence from any column and offset left or right
 Extract Numbers from Text Strings
 Find & Return The Last Used Cell On An Excel Worksheet Or Column VBA Function
 List/Return Difference Between 2 Cells Containing Comma Separated Strings
 Sort by Color
 Workbook Name in Cell
 VLOOKUP Across Multiple Sheet
 2 Criteria Vlookup
 3 Criteria Vlookup
 4 Criteria Vlookup
 5 Criteria Vlookup
 Extract Last Word
 The Ultimate Excel Lookup Function
 Non Repeating Random Numbers
 Get Cell Comment Text Into Cell
 Interior Cell Color by Index or Name
 Reverse Cell Content
 Get Highest Number Between Nominated Range
 Microsofts Convert a Numeric Value into English Words
 Microsofts Convert a Currency or Value into English Words
 Extract Hyperlink address from a Hyperlink
 Does a Cell House a Formula
 Calculation Status & Mode
 Extract File Name From Full Path
 Does an Excel Workbook Exists or Not?
 Extract nth Word From Text in Excel
 How Many Specified Days in a Month
 Determine nth Weekday of a Specified Date
 Calculate Siding Scale Tax
 Display AutoFilter Criteria
 Return Date of Last Chosen Day of Given Month
 Return Return Date of the First, or nth Day of Month
Although Excel has over 300 built in functions, there are those times
when you cannot achieve the result you want with a standard Function or you must
get your result by creating a deeply nested formula using a combination of standard
functions. It is at these times that a Custom Function or User Defined Function
(UDF) can be very handy.
There are however a few drawbacks when writing a Function in the VBE as opposed
to a Macro or Procedure. As long as we are aware of the rules, we should not encounter
any serious problems. Before we look at some examples, let's get the negatives out
of the way first. This way we are thinking along the same lines! I will refer to
Custom Functions as UDF's (User Defined Functions)
Creating a UDF requires the use of VBA  there is no way around it.
This means a user cannot Record a UDF, you have to create the UDF yourself. Nevertheless,
this is not to say that you cannot copy and paste bits of a recorded macro into
your UDF. UDF's do not have the same flexibility as a standard Procedure.
A UDF cannot alter the structure of a Worksheet, such as changing the Worksheet
name, turning off gridlines, protecting the Worksheet etc.
They cannot change a physical characteristic of a cell, including the one
that houses the UDF, so we cannot use a UDF to change the font colour, background
colour etc of any cell. They cannot be used to try and change any part of another
cell in any way at all. This means a UDF cannot place a value into any other cell
except the cell housing the UDF. A UDF cannot use many of Excels built in features
such as AutoFilters, AdvancedFilters, Find, Replace to name but a few!
We can use a UDF to Call (Run) another standard Procedure, but if we do the standard
Procedure will then be under the same restrictions as the UDF itself. To make matters
even worse, when you use a line of code in a UDF that cannot be executed you may
not receive a Runtime error. You just end up with one of the error values (eg;
#VALUE!) in the cell housing the UDF. This can make debugging UDF's very difficult
and leave one scratching their head!
A UDF should update automatically if the data content of any cell it references
changes, this does not include format changes etc. To have any UDF update
as a Volatile Function (eg NOW TODAY etc) you would need to use: Application.Volatile
as the first line. However, be aware that too many Volatile function can slow down
Excel. Another option to force recalculation is to use: Edit>Replace and
Replace = With =
So basically a UDF is very much as the name suggests  a "User Defined Function",
with the emphasis on Function. They should only be used to perform a calculation
of some sort and not take the place of a Procedure.
Another thing to keep in mind is that a UDF will more often than not be less efficient
than a deeply nested group of standard Functions. This is because Excel's built
in Functions are written in the extremely fast language, C++. VBA, unfortunately,
is a very slow programming language.
While all this negativity may leave you thinking "what is the use of them then?",
they can and do come in very handy as long as we are aware of the restrictions imposed
upon them. When used in the correct context, and you become comfortable with them,
you can build your own library of Functions that are not available to other Excel
users.
If you are still with me, let's look at some examples:
Adding Descriptions to Custom Functions: Little known manual steps that show you how to add a description to your UDF's, so that other users will know what they do! It also shows how to add your UDF to a nominated category and even how to add your very own category! TOP^
Add UDF to Custom Category via VBA
Two Column Lookup to Find N'th Occurence: This UDF will look in the first column in a Table or Range for the N'th occurence of a specified value, then look in a nominated column for another specified value on the same row. It will then return the result from the same row in a specified column.
Sum by ColorCount by Color: Will sum a range of cells based on their fill color. There is also one which will count cells based on their fill color.
Sum Every Nth This Custom Function will sum every nth row or cell in the specified range
Sum The X Smallest/Largest Numbers In Row or Column Sums the top/bottom N numbers in 1 column/row range.
Sum Excel Ranges Diagonally Ever wanted to sum some cells in Excel, but with a twist. Sum cells diagonally toptobottom and vice versa, lefttoright and vice versa.
Sum Excel Range Meeting Up To 5 Conditions This Custom Function for pre Excel 2007. If you have Excel 2007, use SUMIFS. It allows you to nominate up to 5 conditions/criteria to be met in corresponding columns.
A Single Function That Will SUM or COUNT Cells By Their Fill Color: Use this one function to specify if the range of colored cells should be summed or counted.
Count Words: Will count words in single cell, or range of cells.
Count or Sum Specified Number In Cell Housing Many Numbers
Extract Numbers from Text Strings: Will extract the numeric portion from a Text String. TOP^
List/Return Difference Between 2 Cells Containing Comma Separated Strings
Sort by Color: Will allow you to nominate a range of colored cells to be sorted by the color order chosen. In other words, Sort by color!
Workbook Name in Cell: These two UDF's will place the name of a Workbook into a cell, or the Workbooks File path and name. It also shows the Excel CELL function returning the Workbooks name, file path and active sheet name. Neither of the two examples for the UDF's take any arguments.
VLOOKUP Across Multiple Sheet: This UDF was written by myself to take the place of VLOOKUP when you need to look across ALL the Worksheets in the active Workbook.
Extract Last Word: This one will extract the last word from a string of text.
Non Repeating Random Numbers: A very handy little function which will produce x unique random numbers between any 2 numbers you specify
Get Cell Comment Text Into Cell: Very simple Function that can be used to extract the text from Excel's cell comments. TOP^
Interior Cell Color by Index or Name: Very handy function that will return the referenced cells interior fill color as either and index number or it's text name.
Reverse Cell Content: Function that will reverse the content of a cell.
Get Highest Number
Between Nominated Range: A Custom Function that will return the highest
number in a range, that is between two specified numbers.
Microsofts Convert a Numeric Value
into English Words : A very popular function from Microsoft themselves,
that spells out numbers. TOP^
Microsofts Convert a Currency
or Value into English Words : A very popular function from Microsoft themselves,
that spells out currency values.
Get Hyperlink Address
: Need to extract the underlying address from an email or Web address? This is the
one!.
Does Cell Have a Formula
: This one is very handy when used with Conditional Formatting as you can map your
spreadsheet.
2 Functions To Determine
Excel Calculation Status & Mode
Extract File Name
from Full Path Name : Function that is used to extract the file name from
a full path name. TOP^
Does an Excel Workbook
Exists or Not?: Function that is used to determine if an Excel Workbook
exists or not.
Extract nth Word From Text in Excel: With the aid of Excel VBA we can write a custom formula/function, or user defined function to extract out the nth word from a text string.
How Many Specified Days in a Month: Number of Specified Days in a Specified Month
Determine nth Weekday of a Specified Date : If you need to have formula return different results on the 1st Friday (any weekday) than if it would on the 2nd, 3rd or 4th Friday you can use the Custom Function/Formula here. For example, you may need to determine how many Saturdays have passed so far in a specified month. TOP^
Calculate Sliding Scale
Tax : Custom Excel Function (Excel Macros) that can be used to calculate
tax based on a sliding scale, or bracket tax.
Display AutoFilter Criteria
: Custom function that displays AutoFilter criteria being
used
Return Date of Last Chosen Day of Given Month : Custom function that displays
the date of the last chosen day in any given month. TOP^
Return
Date of the First, or nth Day of Month : The Custom Function here can be
used to return the date of the nth specified day of any given month
Count One Instance
of Each Entry : The Custom Function here can be used to count repeated items
once. TOP^
Lookup any occurrence from any column and offset left or right : Lookup the nth occurrence in any column and return the corresponding cell to the left or right of the match. TOP^
The Ultimate Excel Lookup Function This Custom Function will look in any column, unless specified, for the nth occurrence of the specified value and return the corresponding value x rows above or below the found value to the left or right.
Special! Free Choice of Complete Excel Training Course OR Excel Addins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages  Trading In Excel  Construction Estimators  Finance Templates & Addins Bundle  CodeVBA  SmartVBA  PrintVBA  Excel Data Manipulation & Analysis  Convert MS Office Applications To......  Analyzer Excel  Downloader Excel
 MSSQL Migration
Toolkit 
Monte Carlo Addin 
Excel
Costing Templates