Defining the Category
You must execute some VBA code when the workbook (or add-in) that contains the function is opened.
For more information refer to the Function Categories page.
This example will accept a cell range and return an array of values plus 10
Public Function ReturnArray(ByVal oRange As Range) As Variant
Dim myarray As Variant
Dim irow As Integer
myarray = oRange.Value
For irowno = 1 To Ubound(myarray,1)
myArray(irowno,1) = myarray(irowno,1) + 10
ReturnArray = myarray
Excel > Cells & Ranges > VBA Code > Working with Arrays
Custom functions can also be used to significantly shorten your formulas. However custom functions are often much slower to calculate than the built-in functions.
Include screen shots for example
When your custom worksheet function is re-calculated it behaves just like an Excel worksheet function and is only re-calculated when any of its arguments are modified.
Include a log file example as well
Passing in Arrays
You can use ParamArray refer to MEDIANIFS
You can also use user defined worksheet functions in your regular VBA code.
Returning an Valid Error
It is possible to have your custom function return error values
In order to be able to return an error value from a custom function the datatype returned by the function must be Variant.
There will also be a slight performance loss by returning a Variant but returning a sensible error value might outweigh this.
Public Function BET_Error() As Variant
BET_Error = VBA.CVErr(xlCVError.xlErrValue)
If you try and return an error value from a function that does not return a Variant the function will return #VALUE.
VBA.CVErr(xlCVError.xlErrDiv0) 'Error Number 2007
VBA.CVErr(xlCVError.xlErrNA) 'Error Number 2042
# NAME ?
VBA.CVErr(xlCVError.xlErrName) 'Error Number 2029
VBA.CVErr(xlCVError.xlErrNull) 'Error Number 2000
VBA.CVErr(xlCVError.xlErrNum) 'Error Number 2036
VBA.CVErr(xlCVError.xlErrRef) 'Error Number 2023
VBA.CVErr(xlCVError.xlErrValue) 'Error Number 2015
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext