Excel - Various User Defined Functions

With Excel VBA you can automate processes, but also create new functions. The demo file contains various User Defined Functions (UDF) that enhance Excel functionality (VBA code is found below), a date picker, functionality for path/file selection, and several Lambda functions.

Download demo file   (205kB - downloaded 32 times - Latest version: 2021-10-17-v23, including Lambda equivalents)

Though UDF work in older Excel versions as well, the demo file is best viewed in Office365 insider version/Excel 2021, because some examples use the newest standard functions, and because array formulas are easily handled in the latest Excel version.

Contents

Array UDF are marked with *. Some can act both as array and non-array, marked with **. Some can be used within standard functions such as SUM and thus convert to single cell output functions.


Next 5 UDFs are merely relevant for handling hidden columns. If your case is about hidden rows only, then use standard function SUBTOTAL.
Next UDF can be considered array manipulation functions.
Also UDF of pages bilinear interpolation ,  linear interpolation ,  as-the-crow-flies distance are included. Those pages contain more info.

The following are no UDF as such, but handy extra functionality. It requires a few lines of VBA code in your worksheet, to make it respond to a double click. VBA code is found below and in file.

File preview (top part only)

VBA code to be put in a module

In MS Excel, press Alt+F11 to open the Visual Basic Editor (Windows). Via top menu: Insert → Module. Copy the code below into the module.
Or export module mdlVariousUDF from the demo file and import it into your own file.



VBA code to be put in a worksheet

Copy the VBA code below into your worksheet to enable getFolder and getFile and trigger the DatePicker by double clicking on specific cells. It is handy to take a look at the demo file how to implement. It requires named ranges 'exportpath' and 'exportfile' to be defined in your worksheet, and cells to be formatted as date for the date picker.