Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

Ozgrid, Experts in Microsoft Excel Spreadsheets

Custom Excel Formulas - User Defined Functions/Formulas

 

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!

Excel Custom Formulas/Functions. How about A Free Handy Excel Functions Add-in? Or Excel VBA?

User Defined Functions Introduction

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 Run-time error. You just end up with one of the error values (eg; #VALUE!) in the cell housing the UDF. This can make de-bugging 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 Color-Count 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 top-to-bottom and vice versa, left-to-right 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 Add-ins 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 [email protected] 31 days after purchase date.


Instant Download and Money Back Guarantee on Most Software

Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

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 & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates