Integration of the functions
for Excel 2007 to 2019
Download the Excel file with the VBA code of the functions
SORT, SORTBY, FILTER, XLOOKUP, XMATCH, UNIQUE, SEQUENCE, RANDARRAY
and all examples shown on these web pages:
▸Download Excel file
(Version 3.7 - Last update: 2023-01-06 - XLookup and XMatch now fit for use in array formulas ▸more on this)
More information on the ▸Sort page.
Integration of the functions
Sort, Sortby, Filter, Unique, Sequence, Randarray, XLookup and XMatch
After performing the procedure described here, these functions are available for the one Excel file in which they were integrated. If they are to be automatically available for all Excel files on your own computer, you can install them in Excel as an Add-in (see ▸next section).
1. Integrate into a new file
This is the simplest case. In the downloaded Excel file (download see above) you delete all worksheets except for one empty worksheet. - Done.
This empty file then only contains the VBA code of the functions. It can thus be used as a template for all new Excel files to be created.
2. Integrate into an existing file
In this case you have to copy the module with the VBA code of the functions into your own file and save the file as a .xlsm Excel file.
To do this, follow these steps:
- Open the file in which the functions are to be integrated and the downloaded Excel file at the same time.
- Switch to the VBA editor with Alt-F11.
- Drag the module named modul_hbsort (red arrow) with the mouse to the name of your own file (green arrow).
If the modul name is not visible, you must first click on the plus symbol to the left of the word "modules". - Your own file must be saved with the extension .xlsm.
So that you don't have to carry out these three steps for each individual Excel file, you can also set up the functions as an Add-in. Then you can access these functions in all Excel files on your own computer without having to copy the VBA modules.
However, if you want to share an Excel file that uses one of these functions, you have to copy and deliver the module modul_hbsort.
Installing the functions as an Add-in
If the functions are set up as an Add-in in Excel, they are automatically available to all Excel files on your own computer, even if the Excel files have the extension .xlsx.
Disadvantage:
Excel files that were created with the add-in activated and that contain the new functions only run on your own computer. They cannot therefore be passed on to others without further ado. If you often want to transfer Excel files to other computers, it is easier not to set up the new functions as an add-in, but to integrate the VBA code of the new functions into each Excel file as described under points 1 and 2.
For setting up the UDFs (user defined functions) as an Add-in, follow these steps (Excel 2019):
- Make a copy of the hbExcel_SORT_function.xlsm file (download see top of page) and delete all worksheets except one. All content is deleted on this last worksheet. The file therefore only contains the VBA code and an empty worksheet.
- When used as an add-in, one line of VBA code must be disabled, otherwise the runtime error 91 occurs:
Switch to the VBA editor with ALT-F11.
Double-click "DieseArbeitsmappe" at the top left (1).
At the top right delete the line of VBA code with the text "ActiveWorkbook.Saved = True" or deactivate it with a single quote (2).
Use ALT-F11 to return to the Excel worksheet. - Select File ➔ Save As, click Browse, change the type to 'Excel Add-in ( *.xlam)', select 'hbExcel_SORT_function' as file name (or something else) and click on Save. The file is automatically saved in a special Add-in folder.
- The next step is to activate the Add-in.
You open any Excel file or just Excel without a file.
Click on File ➔ Options ➔ Add-ins and you see this window:
In the column 'Location' you look for your selected file name (1) and note the corresponding name of the Add-in in the column 'Name' - here 'Beispieldatei Datenschnitt' (2).
Then click on 'Go...' (3). - You will now see this window:
In the list of available Add-ins, look for the name of your Add-in - here 'Beispiel Datenschnitt' - and set a tick for this Add-in.
Click OK.
From now on the functions Sort_, Sortby, Sortby2, Filter_, Unique, Sequence, Randarray, XLookup, XLookup2 and XMatch can be used in every Excel file on your own computer.
Sharing knowledge is the future of mankind