Advanced


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.


Returning Arrays

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
   Next irowno
   ReturnArray = myarray
End Function

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)
End Function

If you try and return an error value from a function that does not return a Variant the function will return #VALUE.


# DIV/0!

VBA.CVErr(xlCVError.xlErrDiv0)  'Error Number 2007  

# N/A

VBA.CVErr(xlCVError.xlErrNA)  'Error Number 2042  

# NAME ?

VBA.CVErr(xlCVError.xlErrName)  'Error Number 2029  

# NULL!

VBA.CVErr(xlCVError.xlErrNull)  'Error Number 2000  

# NUM

VBA.CVErr(xlCVError.xlErrNum)  'Error Number 2036  

# REF!

VBA.CVErr(xlCVError.xlErrRef)  'Error Number 2023  

# VALUE!

VBA.CVErr(xlCVError.xlErrValue)  'Error Number 2015  


© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext