|
New! Excel User Group Now Open @
www.excelusergroup.org,
Check It Out!
UDFs = User Defined Functions
User defined functions can be used in exactly the
same way as any worksheet function in Excel and can
equally be used in code.
UDFs cannot change any object, they can only
return a value. If you need to manipulate an
object you will need to look at a 'Sub' procedure.
To give an example use for a UDF. You may
always write worksheet functions to add VAT @ 17.5%.
This creates two problems.
- You have to repetitively add the 17.5%,
(=SUM(A1:A100)*1.175)
- The Chancellor can never be convinced to keep it
@ 17.5%! Imagine how many formulas need changing!
To overcome this you could write a short UDF and save
it for use by all workbooks in your
personal.xls workbook.
This function in an example workbook can be
declare it like so.
Function AddVAT(dInputVal As
Double) As Double
'Code goes in here
End Function
You will notice that most UDFs have 'Parameters' in
this case 'dInputVal'. This is the 'value' passed to the
function. When we use this on a worksheet, it will be
the value of a cell, e.g. =AddVAT(A1). You also notice,
in the same way as a worksheet function we use the
function name preceded by a '=' sign. (If this is held
in another workbook then you will need to precede the
function name with the workbook name and the workbook
must be open. Remember: personal.xls is always open when
Excel is, but hidden, so it's a great place for UDFs).
You may also notice that the value we are expecting
into the UDF is a 'Double' data type (See Data Type
Summary in Excel's help for an explanation) and the
value we are outputting from the function is also a
'Double' data type. (As Double).
The function name is used after all calculations
within the function to return the value to the
worksheet, (in this case), or code. The line 'Application.Volatile
True' ensures this function is updated whenever the
worksheet re-calculates. The final function would look
like this.
Function AddVAT(dInputVal As
Double) As Double
Application.Volatile True
AddVAT = dInputVal * 1.175
End Function
It is not possible, without using XLLs and C++ code
currently to add 'descriptions' to UDFs or have
parameters appear in Excel's function wizard or tooltips.
(See below).
All UDFs will appear in the 'User Defined' section of
the 'Define Function' dialog, (below), invoked from 'Insert
Function...' or when pressing 'Fx' in the formula
bar when the active cell does not already have a formula
in it.
The above is a very simple example, but UDFs can
become very complicated, have optional parameters and
return the same error values as Excel's own functions, a
very powerful addition to Excel's armoury.
|
