Excel tips and tricks (v7 Feb 2023)

Contents

• Using a class for persistent public variables
• Measure the execution time of code
• Speed up execution of VBA code / macro
• Store the value of a forms controls (i.e. checkbox or textbox) between sessions
• Position of (pop-up) window on the screen and worksheet
• Using arrays to speed up read and write to worksheet cells
• Adding a menu to the ribbon
• Making your own custom functions
• The difference between HIDE and UNLOAD of a form
• Events triggered by the form controls
• Regular expressions with user defined functions
• Reading table filter settings

Using a class for persistent public variables

In my excel code I often make use of variables that I use throughout the sheets and modules. For instance I want to store the number of the last line of a table. I declare these public global variables in a module. For instance in a module I call "InitPublics" there is a line:

Public LastRowOfTable as Integer

The problem I faced with this is that these variables looses their value once the code is interrupted by a run time error. Especially for testing this is a pain. When you use a public constant such as:

Public LastRowOfTable as Integer = 10

Then the value will not be 'lost' during run time error but if the length of the table changes you cannot change the value of the constant during runtime.

A possible solution for this is to use a class property as a (public) variable. The advantage of a class property is that it has GET and LET methods that are activated during reading and writing of the property. You can use these methods to manipulate the value of the variable. Another great advantage is that you can create dynamic constants. Meaning that a variable can be read only for the code. Only within the class the variable gets a value. So the class does not prevent that variable values are lost during runtime errors but it can initialize the variables before they are actually used. Once you know how to do this, then working with these classes is quite simple. As a demonstration you can have the following code in your project in a class module named ClassGlobalVariables:

Private pDemoReadProperty As Integer
Private pDemoReadWriteProperty As Integer

' DemoReadProperty
Public Property Get DemoReadProperty() As Integer
	If pDemoReadProperty > 0 Then pDemoReadProperty = pDemoReadProperty - 1
	If pDemoReadProperty = 0 Then pDemoReadProperty = 10
	DemoReadProperty = pDemoReadProperty
End Property

' DemoReadWriteProperty
Public Property Get DemoReadWriteProperty() As Integer
	If pDemoReadWriteProperty = 0 Then pDemoReadWriteProperty = 100
	DemoReadWriteProperty = pDemoReadWriteProperty
End Property
Public Property Let DemoReadWriteProperty(val As Integer)
	pDemoReadWriteProperty = val
End Property

For demo purposes there are only 2 variables. One that is read only and one that is read write. The GET procedure is activated when the value of the variable is read in the code. The LET procedure is activated when the variable is set in the code. As you can see the read property only has a GET method. In the GET method of the read only property the value is set to 10 when it is not initialized yet. This sets the variable during first use in the code but also resets it if the value is lost during a run time error. As a demo I have added a line where the value of the read only variable is decremented by 1 every time the variable is read in the code.

Public GlobalVar As New ClassGlobalVariables

Sub DemoGlobalVars()

Dim i As Integer
Dim c As String

' read inital value of read only property
i = GlobalVar.DemoReadProperty

' read changed value read only property
i = GlobalVar.DemoReadProperty

' next line creates a run time error causing variables to loose their values
On Error Resume Next
i = i * c
On Error GoTo 0

' read again inital value of read only property
i = GlobalVar.DemoReadProperty

' read inital value of read and write property
i = GlobalVar.DemoReadWriteProperty

' next line will give a compiller error "Can't assign to read only property". Remove the comment to check this:
GlobalVar.DemoReadProperty = i - 1

' this sets the read write property
GlobalVar.DemoReadWriteProperty = i - 1

End Sub

When using the variables in your code you have to prefix the variable name with the name GlobalVar followed by a dot. Excel will give you a drop down of the defined properties during typing so that it is quicker to enter and less chances of typos. The first attempt to read the value of the read-only variable will result in the initial value of 10 for i. In the next attempt i will be 9 because it is decremented in the GET method. An attempt in the code to assign a value to a read only variable will result in a compiler error.

Download the excel file to try it yourself. Check out the class module 'ClassGlobalVariables' and the module 'Module1_GlobalVariables'.

Time the exection of code

To measure the execution speed of a piece of code is simple. You set a variable to the time value before you run the code and immediately after. The difference between the two is the time it took to execute the code. Simple but there is a problem, the problem is that the default and only VBA function to return a time value =Time() has a resolution / granularity of seconds. So measuring anything smaller than a second will not possible. Therefore Microsoft published code called MicroTimer to produce a time value with a micro second granularity.

Based on this code I made a so-called class named ClassTimer to define some methods to easily do this time measurement. The class enables you to use Timer.Start before the code under test and Timer.Finish after the code under test to print the execution time. In the class I convert the MicroTimer to milliseconds because for me that is enough. For fast code I execute it in a loop of 1000x-1000000x to produce readable results. Together with the statements to disable events (see next section about speeding up code) the VBA code for testing execution time becomes:

Dim timer As New ClassTimer
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.Cursor = xlWait ' to reduce cursor flicker and improve speed of code

timer.Start
For i = 1 To 1000
	' 
	"place code to test here"
	' 
Next i
timer.Finish

Application.Cursor = xlDefault
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

The result is written by the ClassTimer to the immediate window in the VBA editor using the command Debug.Print. It looks something like:

=== 10:16:17 ===
0,665         = Total from start

The first line with the time value is written by the timer.Start method. This method also set the internal timer to 0. The timer.Finish method prints the internal timer. The class also has some more methods:

timer.Inter: To display the running timer without resetting it to zero. This could be used as intermediate checkpoints for larger bits of code, where you also want to know to total time for all the code.
timer.Restart: To display the running timer value. This also resets the timer. This is basically the samea as timer.Inter followed by timer.Start

Download the excel file to try it yourself. Check out the class module 'ClassTimer' and the ExecTime sheet code.

Speed up execution of VBA code / macro

To speed up the execution of code that manipulates cells in worksheet and also to reduce screen flickering during code execution, I put the following lines before and after the code:

	Application.ScreenUpdating = False
	Application.EnableEvents = False
	Application.Calculation = xlCalculationManual
	Application.Cursor = xlWait
	Application.ScreenUpdating = True
	Application.DisplayStatusBar = True
	Application.StatusBar = "Message that code is executing..."

	< code to execute >

	Application.StatusBar = ""
	Application.Cursor = xlDefault
	Application.Calculation = xlCalculationAutomatic
	Application.EnableEvents = True
	Application.ScreenUpdating = True

Application.ScreenUpdating : Stops updating/refreshing the screen while the code is running. This is especially effective when the worksheet cells that are updated by the VGA code are visible for the user.
Application.EnableEvents : This prevents code is interrupted by events. Event could be something like changing a value of a cell. Remember that cells could be changed by the code and would trigger events like the Worksheet_Change() event.
Application.Calculation : To not trigger the calculation when cells are changed by the code. Remember that cells could be changed by the code also would trigger the calculation of the sheet.
Application.Cursor : This changes the cursor to a waiting circle. To reduce cursor flicker. Is also an indication to the user that code is executing and seems to speed up code execution.
Application.StatusBar : Displays a message in the left lower corner of the excel window. This does not actually improve the execution speed of the code but it can make it clear to the user that something is running. Current status is saved before message is displayed. Status bas is restored after code execution.

If code is stopped during execution e.g. by an error then the status of the events is not restored since these statement were not executed. This is especially anoying for the cursos which is still in the wrong state (circle) and the events are no longer executed. Therefore I leave out these lines until the code if fully debugged.

If code is called by other subroutines then it is better to store the current status and restore after code is executed:

	Dim currEnableEvents as Boolean
	Dim currScreenUpdating as Boolean
	Dim currCalculationMode as xlCalculation
	Dim oldStatusBar As Boolean

	currEnableEvents = Application.EnableEvents
	currScreenUpdating = Application.ScreenUpdating
	currCalculationMode = Application.Calculation

	Application.ScreenUpdating = False
	Application.EnableEvents = False
	Application.Calculation = xlCalculationManual
	Application.Cursor = xlWait
	oldStatusBar = Application.DisplayStatusBar
	Application.DisplayStatusBar = True
	Application.StatusBar = "Message that code is executing..."

	< code to execute >

	Application.StatusBar = ""
	Application.DisplayStatusBar = oldStatusBar
	Application.Cursor = xlDefault
	Application.Calculation = currCalculationMode
	Application.EnableEvents = currEnableEvents
	Application.ScreenUpdating = currScreenUpdating

Application.ScreenUpdating : Stops updating/refreshing the screen while the code is running.
Application.EnableEvents : This prevents code is interrupted by events. Event could be something like changing a value of a cell. Remember that cells could be changed by the code.
Application.Calculation : To not trigger the calculation when cells are changed by the code.
Application.Cursor : This changes the cursor to a waiting circle. To reduce cursor flicker. Is also an indication to the user that code is executing and speeds up code!!
Application.StatusBar : Displays a message in the left lower corner of the excel window. Current status is saved before message is displayed. Status bas is restored after code execution.

During code development I was interested in the parts of the code that contributred the most to the execution time of the code. Therefore I conducted some test on code snippets to measure the time needed. The absolute values of the measurements may vary from computer to computer depending on the exact version of Windows and maybe Excel and the hardware of the computer used. But the values can be compared to each other to determine which code is fast and which is slower. The measurements were done on Windows 11 and a processor 11th Gen Intel(R) Core(TM) i7-11700 @ 2.50GHz together with the built in graphics UHD 750. All measurements were done with the above mentioned method. Code snippets were executed 1x (Sec), 1000x (mSec) or even 1000000x (µSec).

The table below gives the execution times for the various ways of reading a value from a cell:

Some measurements for reading other properties of a cell. All these have more or less the same execution time:

The next table gives some values for writing to a cell:

And some random VBA code functions, methods and snippets that I used in my code:

Select Case i
	Case 1
	Case 2
	Case 3
	Case 4
	Case 5
End Select

If i = 1 Then
	ElseIf i = 2 Then
	ElseIf i = 3 Then
	ElseIf i = 4 Then
	ElseIf i = 5 Then
End If

If Not Intersect(Target, Range("A58")) Is Nothing Then
	' code to execute
End If

If (Target.Column = 1) And (Target.Row = 58) Then
	' code to execute
End If

Execution times of the lookup functions:

For r = 1 To 100
	If Cells(r, 2) = "Row100" Then Exit For
Next r

The following table shows the difference in reading a range cell by cell in a for next loop or using an array to read a range. As you can see, using an array is much faster. Note that the values are in [mSec] now:

For r = 1 To 100
	For c = 1 To 100
		shArr(r, c) = Cells(r, c)
	Next c
Next r

For r = 1 To 1000
	For c = 1 To 1000
		shArr(r, c) = Cells(r, c)
	Next c
Next r

The following table shows the difference in writing a range cell by cell in a for next loop or using an array to write to the range. As you can see, using an array is much faster. I could not find a significant difference in writing to a cells/range .Value, .Value2, .Formula or .Formula2 property. Also the type of the array seem to have no affect. With the shArr defined as integer, string, single, date etc the same result was obtained. I made the array the same size as the range being written to but in reality the shArr can be bigger than the range:

For r = 1 To 100
	For c = 1 To 100
		Cells(r, c) = shArr(r, c)
	Next c
Next r

For r = 1 To 1000
	For c = 1 To 1000
		 Cells(r, c) = shArr(r, c)
	Next c
Next r

Store the value of checkbox or textbox on a form during sessions

EXCEL has a built in mechanism to store and retrieve userform controls values. This is done through a property called "ControlSource". It is present on the properties of a control. See below for the CheckBox control:

This ControlSource property can be made to point to a cell. This will retain the value also when the workbook is stored. As an example I have linked one control box to cell B2 in Sheet "PopUp" and the other to B3:

In the above example the values are stored on the fly on the sheet. As soon as you change a value on the form it is stored in the sheet. No additional code is needed! When the workbook is saved the values are also automatically saved. When the userform is activated again it uses the stored values in the linked cells as the initial startup value. You can make the sheet hidden and give it a proper name, like 'Settings'.

Download the excel file to try it yourself. Check out the sheet "PopUp" and form 'Userform1' and module 'Module2_WindowFunctions'.

Position of (pop-up) window on the screen and worksheet

When you use a form in your code that pops-up you may want to position it more precisely. Excel offers a few options in the properties of the form during design:

• Manual : Userform will retain position between subsequent hides and shows. Initially userform will be positioned top-left of the screen. If the user changes position of the Userform by dragging and the window is 'closed' with the Userform.Hide method then on the next Userform.Show the Userform comes back on its previous position. If you close the window with the X this unloads the userform and the position will revert to top left again. Note that this can be outside the excel window if this is not maximized.
• CenterOwner : Userform appears center vertical and horizontal of the excel application window. If excel window is maximized this is the same as CenterScreen
• CenterScreen : Userform appears center vertical and horizontal of the screen. Note that this can be out of the excel window if the excel window is not maximized.
• WindowsDefault : Userform appears top-right of the screen. Note that this can be out of the excel window if the excel window is not maximized.

In mode 'Manual'when you want more control or dynamically change the position you can do this in the VBA code. There are a couple of Application, Userform and ActiveCell properties that are (can be) involved. This is shown in the picture below:

From the above picture you would expect that the minimum value for .top and .left are 0. But in fact, in a multi monitor set-up, these values can go below 0 and beyond the right side of the screen. This all depends on the position of the monitor in the windows configuration. The reference for the .top and .left values is normally monitor #1. This was a setup with a laptop and 2 connected monitors. Monitor #1 was the laptop screen:

But I also had a situation where not monitor #1 was the reference but monitor #2 appeared to be the reference. This occured after I closed the laptop screen. In the picture below both monitor #1 and #2 are external monitors. So windows renumbered the monitors when I closed the laptop:

With the excel application moved to monitor #2 you would get negative values for the .left and possibly also for the .top. With all this in mind I use the following code in a module to position a userform right of a selected cell (as shown in the above picture):

Note that this procedure uses ByRef for the parameters, normally this is ByVal. ByRef allows the procedure to modify the parameters. In this case set the window .left and .top properties. This procedure is called from the Userform.Initialize event:

Private Sub UserForm_Initialize()
	WindowPosRightOfSelection UserForm, ActiveCell
End Sub

Download the excel file for the code. Check out the module 'Module2_WindowFunctions' and the sheet 'PopUp'

Note: If you would execute this code directly from the userform then the 'w' should be replaced with 'Userform' or 'Me' and the 'c' should be replaced with 'ActiveCell. For instance var>w.Top becomes Me.Top and c.Left becomes ActiveCell.Left

The main part of the routine is:

	w.Top = Application.Top + c.Top * ZoomLevel + CommandBars("Ribbon").Height - (w.Height / 2)
	w.Left = Application.Left + (c.Left + c.Width) * ZoomLevel + (w.Width / 4)

This code will override the default startup position that was set in the form. The ActiveCell properties are affected by the excel window zoom level (in the bottom right corner). To calculate the position correctly you have to multiply the ActiveCell.Left and ActiveCell.Top values with the zoom level factor. I used the ribbon height property to account for a (un)collapsed ribbon.

• Hiding a userform with Userform.Hide keeps it in the background. It means that values on the form will be saved during the session.
• Clicking on the X of the userform 'unloads' it. This means that values on the form are not saved. The form is re-initialized again (event: UserForm_Initialize() ) when it is shown.
• When the userform is made visible this triggers the activate event UserForm_Activate()
• The ActiveCell.Top and ActiveCell.Left properties are the distances respectively from Row 1 and Column A. So not from the left / top of the excel window grid! This means that when the excel window is scrolled and row 1 and/or column A is not visible you get a too high value for the ActiveCell.Top and ActiveCell.Left properties
• It is good practice to check if userform is not positioned off screen or off excel window. If the userform is positioned off screen you cannot close it and this may block code execution.
• The Application.UsableHeight and Application.UsableWidth properties give roughly a size that is a little bit bigger than the cell grid.

Using arrays to speed up read and write to worksheet cells

Reading from and writing to worksheet cells takes up relatively much of the processing time of code. A faster way to do this is to use an array to store, modify and restore the cells. Increase in speed can easily be a factor 100x. Steps to follow if to use an array

• Define an array without dimension eg ArrRange() as Variant. USe type variant to handle all types of values (strings, integers, dates etc) in a cell
• Define the range / area you want to work on.
• Re-define the array using the range column and row values.
• Read the sheet values into the array.
• <code that manipulates the array values>"
• Write the array back to the worksheet

In excel basic this could be something like:

Sub DemoWriteCells()
Dim ArrRange() As Variant
Dim StartRange As String
Dim EndRange As String
Dim rDim As Integer
Dim cDim As Integer
Dim r As Integer
Dim c As Integer
Dim sTime As Date
Dim fTime As Date

' define the range to work with
TopLeftCell = "B5"
BottomRightCell = "BZ100"

' First directly write to cells on the worksheet
sTime = Now()
For r = Range(TopLeftCell).Row To Range(BottomRightCell).Row
	For c = Range(TopLeftCell).Column To Range(BottomRightCell).Column
		Worksheets("PopUp").Cells(r, c) = "w"
	Next c
Next r
fTime = Now() - sTime
MsgBox "Execution time direct write to cells = " + CStr(fTime), vbInformation

' Use array to read and write cells on worksheet
rDim = Range(BottomRightCell).Row - Range(TopLeftCell).Row
cDim = Range(BottomRightCell).Column - Range(TopLeftCell).Column
ReDim ArrRange(rDim, cDim)
ArrRange = Range(TopLeftCell + ":" + BottomRightCell).Value

sTime = Now()
For r = 1 To Range(BottomRightCell).Row - Range(TopLeftCell).Row + 1
	For c = 1 To Range(BottomRightCell).Column - Range(TopLeftCell).Column + 1
		ArrRange(r, c) = "A"
	Next c
Next r
Range(TopLeftCell + ":" + BottomRightCell).Value = ArrRange
fTime = Now() - sTime
MsgBox "Execution time using array = " + CStr(fTime), vbInformation

End Sub

Note, the .value property of the cell is used in the above example to read and write back the data. Excel stores values and formulas of a cell separately from each other. The .formula property holds the formula and the .value property will hold the result. Writing to a cell using the .value property looses the formula if there was one. If you are working with an area that (also) holds formulas then consider using .formula to read and write the data.

Download the excel file to try it yourself. Check out the sheet "Array" and the module 'Module2_Arrays'.

Adding a menu to the ribbon.

To make your excel code more user friendly and professional you can add command buttons to the built in excel ribbon.

• You can define yourself where the new menu should be positioned in the ribbon.
• You can give the menu and the buttons a short name. This name is visible on the ribbon.
• You can define an icon from the standard set. For a list of names search for 'msoImages' on the web.
• Or you add a new icon to CustomUI and use that.
• You can add tooltip text to your buttons. This will appear if the user hovers over the button with the mouse.
• You can enable or disable a button. A disabled button will be grayed out.

The following steps are needed to add a menu to the ribbon:

• If not already done, download and install the CustomUI utility.
• From CustomUI open an xlsm file and add xml code for the ribbon.
• Save the xml code to the excel
• Open the xlsm in Excel. You will now see the added menu
• Add code to a module in the xlsm to handle the events triggered by the buttons.

Here is an example of xml code for a menu in the ribbon:

	<ribbon>
		<tabs>
			<tab id="customTab" label="RADAR" insertAfterMso="TabHome"
				<group id="customGroup1" label="Tools"
					<button id="customButtonIMP"
						label="Import workload"
						size="large"
						screentip="Imports the capacity and planned workload from the VIRES export file."
						supertip="Refreshes the data in sheet WorkloadImport."
						onAction="cMenuImportWorkload"
						enabled = "true"
						imageMso="RecordsAddFromOutlook" />
					<button id="customButtonOPL"
						label="OPL list"
						size="large"
						screentip="Update the prospect/sales projects list from an OPL file on the server"
						supertip="Reads the OPL file and updates the project data."
						onAction="cMenuUpdateOPLRadar"
						enabled = "false"
						imageMso="CacheListData" />
					<button id="customButtonSORT"
						label="Sort OPL"
						size="large"
						screentip="Sorts the OPL list."
						supertip="1) Area[A-Z] 2) Scoring Chance[Planned,High,Medium] 3) Start Eng Req[Old-New]"
						onAction="cMenuSortOPLList"
						enabled = "true"
						imageMso="SortCustomExcel" />
					<button id="customButtonHLP"
						label="Manual"
						size="large"
						screentip="Open the user manual of the Radar excel file."
						supertip="Opens the pdf version of the user manual stored in smarteam under document number A_DOC384967."
						onAction="cMenuHelp"
						imageMso="FunctionsLogicalInsertGallery" />
				</group>
			</tab>
		</tabs>
	</ribbon>
</customUI>

And here is the event handling code for the above ribbon:

' button [Import]
' Callback for customButtonRes onAction
'
Sub cMenuImportWorkload(control As Variant)
    ' insert code here to execute
    MsgBox "Menu button 'import workload' pressed", vbInformation
End Sub
'
' button [Update OPL]
' Callback for customButtonOPL onAction
'
Sub cMenuUpdateOPLRadar(control As Variant)
    ' insert code here to execute
    MsgBox "Menu button 'update opl radar' pressed", vbInformation
End Sub
'
' button [Sort OPL]
' Callback for customButtonSORT onAction
'
Sub cMenuSortOPLList(control As Variant)
    ' insert code here to execute
    MsgBox "Menu button 'sort OPL list' pressed", vbInformation
End Sub
'
' button [Manual]
' Callback for customButtonHLP onAction
'
Sub cMenuHelp(control As Variant)
    On Error Resume Next
    ActiveWorkbook.FollowHyperlink Address:="http://members.home.nl/rjut/EXCEL/CustomUI sample of ribbon.jpg"
    On Error GoTo 0
End Sub

Download the excel file for the code. Check out module4_CustomMenu and the excel ribbon.

Making your own custom functions.

EXCEL has many useful built in functions that you can use in your spreadsheet. Sometimes you miss a function that is not available. I needed for instance a function to split a separated string into different individual elements, each in their own cell. Splitting a string such as "one, two, three". Three words separated by a comma. If you want each element in a different cell then of course you can do this with the standard built in 'text to columns function':

... but this is static and I wanted a dynamic split. Of course you can also use the standard excel functions but this is cumbersome. First you need to find the location of the commas, then you extract each element into a different cell. Excel VBA offers a very simple built in function called split() for this. You can make a so-called User Defined Function (UDF) that can be used in the excel spreadsheet and that uses the VBA split function to do the work.

There are a couple of important things to remember with UDF's:

• An UDF must reside in a module.
• An UDF must be a function that returns a single result.
• An UDF function can reference any other cell in the workbook but cannot manipulate other cells in the spreadsheet or workbook. Excel will not allow this.
• An UDF is by default a so-called 'non volatile' function. This means that EXCEL will only (re)calculate the result if it directly depends on another cell in the workbook. For instance =MyUDF(A3) depends on cell A3 so is always recalculated if cell A3 changes. To make sure cells with your UDF are recalculated properly you can either force the function to be volatile or use another cell reference as a parameter of the function. There is actually a third method and that is to manually force recalculation of the complete workbook by pressing Ctrl-Alt-F9. If you force the function to be volatile then excel will always recalculate, even if the parameter values of the function did not change. If you have many (thousands) of cells with a volatile function then EXCEL will always recalculate all these thousand cells if you change something to the worksheet. You may start noticing a delay after changing a cell. If you use a cell ref as a parameter then EXCEL will only recalculate the UDF when the cell referenced changes.

If you want to know more about UDF's in excel check out this excellent website. See below for an example of a UDF function:

Function MyUDF3(SepList As String, SepChar As String, ElPos As Integer)
Dim SepArr() As String
Dim i As Integer
	' split the string into a zero based array
	SepArr = Split(SepList, SepChar)
	' return the requested element from the array
	MyUDF3 = SepArr(ElPos - 1)
End Function

This function returns the ElPos'th element from a list string SepList were elements are separated by SepChar. It could be used in an excel sheet cell as =MyUDF3(C9,";",2) which will return "b" into the cell when cell C9 holds "a;b;c;d;e". When used like this EXCEL will (re)calculate the result if cell C9 is changed

If your UDF does not reference other cells and you want to force the function to be volatile then:

Function MyUDF() As Double
	' Good practice to call this on the first line.
	Application.Volatile
	MyUDF = Now()
End Function

This function simply returns the time. Of course this is already a built in EXCEL function

Download the excel file to try it yourself. Check out the module Module2_UserDefineFunction and sheet 'MyUDF'

There are a couple of UDF's in this module:

• MyUDF1 adds the vaule of 2 cells and can be set to volatile or non-volatile with a parameter. It demonstrates that it is always recalculated, volatile of non-volatile.
• MyUDF2 simply returns the time and can also be set to volatile or non-volatile with a parameter. It demonstrates that it is only recalculated when it is volatile, parameter is TRUE.
• MyUDF3 is the list separator function. It references another cell for the SepList parameter. It demonstrates that it is recalculated when the referenced SepList cell changes. It is more elegant when you call this function in the sheet with =MyUDF3($C9;",";COLUMN(D9)-COLUMN($C9)). This way the parameters change to the next index when you copy the cell to other cells.
• MyUDF4 is another list separator function. It uses an indirect reference to another cell for the SepList parameter. It uses this indirect ref to determine the number of cells between the calling cell and the cell with the list. Therefore it does not need the ElPos parameter. But since the cell reference is indirect it is not recalculated automatically ... unless you change the way you call it. In the excel sheet I used =MyUDF4("C"&ROW($C14);";") to call the UDF. By using $C14 as a reference the cell is re-calculated when the list in cell C14 changes. If you copy the formula to other rows then it is also correctly changed by excell. Or of course you can add the line Application.Volatile to your code to make sure it is always recalculated. Then you could call the function simply by =MyUDF4("C13";",").
• MyUDF5 is the same as MYUDF4 but forced volatile.
• MyUDF6 is by far the most efficient and user friendly version. This only works with the new excel array formulas and spill functionality available from 2019/2020 in Excel Office 365 v16. This function has only one line of code!. It simply directly returns the result of the split() function. This is an array. In previous excel version this would result in a single value entered in the column where the formula is entered. In the latest versions excel fills in all returned array values into adjacent cells. This is called spilling. For this to work the cells next to the formula have to be empty. If not a SPILL error is shown in the formula cell. Note that the formula is entered only in one cell. Note also that if you change the number of items in the list the spill range will automatically be sized to the number of elements, cool! If you select the cell with the formula the spill range will be marked with a blue border:
  
  If you select another cell of the spill range the formula will be shown in gray.
  
  If the entire spill range is not empty then you get the SPILL error in the formula cell:
  
  

For splitting a list I prefer to use =MyUDF6(C21;",") because I have the latest EXCEL version. For older versions of EXCEL I would use the =MyUDF4("C"&ROW($C14);";"). It is only recalculated when needed and can be easily copied to other columns and rows.

One very nice possibility is that you can define your own help text for a user defined function. This help text appears in the excel function selector. Also you can put your UDF's in their own category. To do this you have to run the following code for each function you want. You have to run this code only once. Therefore I placed it in the ThisWorkbook module in the Worksheet_Open() event:

Application.MacroOptions _
	Macro:="MyUDF3", _
	Description:="Returns the n-th element from an one dimensional array", _
	Category:="User Defined", _
	ArgumentDescriptions:=Array("String with text reference to a cell with the array list with the separated elements.", "The separation character used in the list.")

The result is shown in the function input pop-up form:

In the above example the MyUDFx user function definitions are added to the (excel predefined) category 'User Defined'. You can also create your own new category. As an example:

Download the excel file to try it yourself. Check out the module Module2_UserDefineFunction and Sheet MyUDF

The difference between HIDE and UNLOAD of a form.

An excel form is made visible with the command UserFormX.Show in the vba code. It can be made invisible with the command UserFormX.Hide or with the command Unload UserFormX. They both seem to do the same thing, but there is a difference. I like to think of it this way: With Hide the form is simply made invisible but is still active in the background. With Unload the form is removed from memory completely. The difference can (does not have to be) be visible when you re-open the form using the command UserFormX.Show again. When the form was closed with UserFormX.Hide the form is opened at the same position it was when it was closed and the values of the controls are kept. When the form was closed with Unload UserFormX the it is re-initialized again. All properties are set as defined during design time. The event 'UserFormX_Initialize' is triggered when the form is opened for the first time or after an unload. The event 'UserFormX_Activate' is triggered when a form is opened after hide and also after unload. When the 'UserFormX_Initialize' event is triggered the form is being built but not yet shown to the user. When the event 'UserFormX_Activate' is triggered then the form has just been opened and is shown to the user. This is why the window position manipulation code in the above example is inside the UserFormX_Initialize event. In the picture below I have tried to illustrate this graphically:

The difference is normally not so relevant. You can place 'the code to execute' when form is started in the 'UserFormX_Activate' event. I ran into some situations where this difference became important:

• When you want to manipulate the position of the form programmatically.
  You want this code to execute before the form is shown i.e. in the UserFormX_Initialize event. This also means that the form must be closed with Unload otherwise the UserFormX_Initialize event is not triggered after UserformX.Show
• When you use a value of a form control in your VBA code.
  When a form is UNLOADed and you check (eg Result = CheckBox1.Value) or change (e.g. CheckBox1 = False) the value of a CheckBox control in your code, then this triggers also the UserForm_Initialize event of the form where the controls is on. In this case you want to close the form with hide.

Download the excel file to try it yourself. Check out sheet 'OpenForm' and UserForm3.

Events triggered by the form controls.

The controls on a userform can trigger a myriad of events. The events can be triggered by the user or by other VBA code. All controls seem to have events that can be triggered but these are not the same for all controls. A commonly used event is the Click() event which is generated when the user clicks on the button with the mouse or presses the ENTER key when the command button has the focus (dotted line visible on the button). Below is a summary of some commonly used events

More than one event can be (and usually is) triggered by a single action. Below are some examples of what events are triggered by what action on a control:

There are 2 properties that influence the events that can be triggered by a controls. These are "Enabled" and "Locked". Both of these can be set to True or False.

The Enabled property set to False greys out the control on the UserForm. In this state it does not generate events anymore when clicked. It also cannot get the focus. So it seems completely dead.
With the locked property set to True the controls is shown as normal on the form. The user cannot change/modify it. It can still receive focus. It will still generate some events.

To demonstrate all this I have made a UserForm2 that lists the events that the user can trigger on various controls.

Download the excel file to try it yourself. Check out sheet 'Events' and UserForm2.

Regular expressions with user defined functions.

The built in search/replace function in excel is very useful and flexible. It can find text in cells or formulas and also certain formatting of a cell. The user interface allows for some special characters to look for specific text. I believe the options are:

• * to find any combination of characters. For instance 'the*re' would find a cell containing 'there' but also 'they were'.
• ? to find any single character. For instance 'the?e' would find a cell containing 'there' but also 'theme' but not 'they were'.
• ~ To escape a * or ?. For instance you would use ~* to search for a single *
• ALT+nnnn You can type in a 4 digit char code by holding the ALT key while typing the 4 numbers on the num keypad.

Editors such as Notepad++ have a more powerful find and replace. Notepad++ uses a text find/replace syntax that is known as 'regular expressions'. This is a rather cryptic yet powerful syntax that enables more complex search and replace actions. The syntax with regular expressions is not fully standardized. Every implementation seems to differ a little from the other. However, many frequently used options are common.

Microsoft Office has a regular expression engine already built in that can be made available for the excel user through User Defined functions. I have made a couple of user defined functions so that you can get familiar with the (microsoft office) syntax of the regular expressions.

The regular expression engine is part of a library that is not by default included in the excel workbook. You have to include it yourself. This can be done in the Visual Basic editor through the tools menu:

Select the 'Microsoft VBSript regular expressions 5.5' library. Make sure not to include the old 1.0 version.

In the workbook I have added a sheet 'Regex' and a module 'Module2_RegularExpressions'. To keep the user defined functions as compact as possible I have created 4 user defined functions to cover 4 different scenarios (Test, Find, Count and Replace). All 4 functions are almost identical. Below is the one for RegexTest.

Module1_RegularExpression:

Const ParGlobal As Boolean = False     ' FALSE=Single match, TRUE=Find all matches
Const ParIgnoreCase As Boolean = False ' FALSE=case sensitive, TRUE= a equals A
Const ParMultiLine As Boolean = False  ' FALSE=Anchor $ is end of line, TRUE=anchor $ is end of file. For text string use in this excel this should be FALSE.
Dim RegexObj As New VBScript_RegExp_55.RegExp ' Creates a static RegExp object used by the Regex routines in this module

Public Function RegexTest(Pattern As Range, SearchText As Range) As Boolean
	With RegexObj
		.Global = ParGlobal
		.Pattern = Pattern
		.IgnoreCase = ParIgnoreCase
		.MultiLine = ParMultiLine
	End With
	RegexTest = RegexObj.test(SearchText)
End Function

The first four lines define module wide constants that are used in each of the 4 user defined regex functions.
The fith line defines an regular expression object that is used in the module. This object is created onces. This improves execution time. With a 'Dim RegexObj' in every function it took about 3 seconds to process 2000 cells. With the module level 'RegexObj' it takes about 1 second. The with statement set the parameters and the search pattern. Finally a method 'test' is invoked with the SearchText
Note: For some reason the RegexReplace did not function with the module level object. This function creates its own static object. If a reader can tell me why this is happening, please drop me a mail!.

The sheet is built up as follows:

• In column A there is a list of 2000+ text entries. This is the SearchText
• Test: In column B the user defined function used is RegexTest(Pattern, SearchText). The Pattern is entered in the header of the column. The SearchText is directly left of the formula in column A. The result of the formula is shown in the cell in column B. FALSE when the Pattern is not found in SearchText, TRUE if it is found.
• Count: In column C is RegexCount(Pattern, SearchText). The Pattern is entered in the header of the column. The SearchText is directly left of the formula in column A. The result of the formula is shown in the cell in column C. 0 if not found, 1 if found once, 2 if found twice etc...
• Find: In column D is RegexFind(Pattern, SearchText). The Pattern is entered in the header of the column. The SearchText is directly left of the formula in column A. The result of the formula is shown in the cell in column D. #N/A if not found, The found text if found once or #VALUE if found more than once.
• Replace: In column E is RegexReplace(Pattern, SearchText, ReplaceText). The Pattern is entered in the header of column E. The SearchText is directly left of the formula in column A. The ReplaceText is in the header of column F. The result of the formula is shown in the cell in column E. The original SearchText> if not found, The replaced text if found.
• In column H is a quick reference to the most used regular expression syntax of Microsoft office. The full list is available on the Microsoft website.

As an example I have made a regular expression pattern to replace a song file name with another file name. The search/replace patterns replace "The Artist" with "Artist, The".

The search string used for this is: 'Singles\\(The)\s(.+)\s-\s'
This looks rather cryptic but if you brake it down into smaller pieces and check with the quick reference it all makes sense.

• 'Singles' text simply searches/matches the text 'Singles'. Keep in mind that the search is case sensitive.
• '\\' refers to the backslash character following the text Singles.
• '(The)' captures the word 'The' and stores in group 1. Group 1 because it is the first time a group is defined.
• '\s' represents a space character following 'The'
• '(.+)' captures the text up to ' - ' and stores in group 2
• '\s-\s' is the dash separator between artist and title including the spaces

A typical search result for input text "Singles\The Rolling Stones - Emotional Rescue.Mp3" would be "Singles\The Rolling Stones - " where 'The' is captures in group 1 and 'Rolling Stones' in group 2.

The replace string used for this is: 'Singles\\$2, $1 - '
This breaks down to:

• 'Singles' literal text 'Singles'
• '\\' to insert the \ character
• '$2' inserts group 2
• ', ' inserts a comma and a space
• '$1' inserts the first group
• ' - ' finally the artist and title separation is added again.

The replacement result is "Singles\Rolling Stones. The - "
The function returns a string where the match result in the SearchText is replaced. So "Singles\The Rolling Stones - " is replaced with "Singles\Rolling Stones. The - "
This is something that cannot be achieved with the built in find/replace of excel.

Download the excel file to try it yourself. Check out sheet 'Regex' and Module1_RegularExpression.

Reading user applied filter settings

In VBA you can programmatically read and manipulate the filters of a table. Let use this table as an example:

You can read the filter settings for example with the following code or something similar:

	Dim cr1 As Variant
	Dim cr2 As Variant
	Dim fon As Boolean
	Dim FilterText As Filter

	Set FilterText = ActiveSheet.AutoFilter.Filters(5)
	fon = FilterText.On
	If fon Then
		ope = FilterText.Operator
		cr1 = FilterText.Criteria1
		cr2 = FilterText.Criteria1
	End If

The 5 means the 5th column of the filter table. The fon is only true if column 5 is being filtered. If another column is filtered but 5 is not then it will be false. The cr1 and cr2 are defined as variant because these values are not always strings as you will see below. The first thing you need to do if the filter setting is actually active. If it is not active an attempt to read the Operator and Criteria1 and Criteria2 will result in a runtime error.

I found out the hard way that this approach does not work for multi level selection filter that is used for dates:

Even when filtering is active on a date column, an attempt to read Criteria1 and Criteria2 will result in a runtime error. Do not know if this is intended or a bug in EXCEL. You can happily read the fon and Operator. Strange thing is that you can set the multilevel filter criteria programmatically but even then you cannot read them back! You can use for instance the following to set a filter on a multi level date column:

	' filter date column
	' criteria2 array(depth, "m/d/yyyy hh:mm:ss")
	' depth sets filter depth, 0=year, 1=month, 2=day
	ActiveSheet.Range("A1:E8").AutoFilter Field:=1, Operator:=xlFilterValues, Criteria2:=Array(2, "5/3/1990")

Note that the date format has to be in American style mm/dd/yyyy. For multi level filtering Criteria1 is not used. Criteria2 is an array consisting of 1 or more pairs of number followed by a date. The number indicates the level/branch on which the filtering is applied. A 0 for years, a 1 for months and a 2 for days. With a 0 and a given date all dates in that year will be checked and visible after filter is applied.

However, the filter settings are stored in the EXCEL workbook. This can be proven by closing the workbook and opening again. On re-opening the filter will show the setting before it was saved. The settings are stored in the sheetX.xml file inside the ZIPped Excel xlsm file. X being the sheet number. In this workbook it is sheet7. In the xml it is stored as:
<autoFilter ref="A1:E8" xr:uid="{8CFF16D5-699F-4856-ADBB-D7A55000C510}"><filterColumn colId="0"><filters><dateGroupItem year="2020" month="5" dateTimeGrouping="month"/><dateGroupItem year="1990" month="5" day="3" dateTimeGrouping="day"/></filters></filterColumn></autoFilter>

Excel filter object attributes, .operator, .criteria1, criteria2.

User selection	Table result	Operator	Criteria1	Criteria2	Remark
1 value selected		0	"=Delta"	'undefined'	There is no enumeration defined for 0. This works only for a single level selection list as shown, NOT for multilevel date selection!
2 values selected		2 = xlOr	"=-102"	"=21"	This works only for a single level selection list as shown, NOT for multilevel date selection!
3 values selected		7 = xlFilterValues	{"=00:00:01", "=07:00", "=23:59:59"}	'undefined'	Criteria1 is 1d array with 3 values. This works only for a single level selection list as shown, NOT for multilevel date selection!
Between start and end values		1 = xlAnd	"=1‑1‑2020" ">=43831"	"=1‑1‑2021" "<="44197"	Custom filter between 1-1-2020 AND 1-1-2021. Criteria1 and Criteria2 hold the date in human readable format or as the equivalent number. Not sure why it sometimes displays one or the other.
Date filter eg 'Year To Date'		11 = xlDynamic	16	'undefined'	Criteria1 holds specific date filter, see microsoft.com" for a full list of all possible enumerations.
Multilevel selection. ActiveWindow.AutoFilterDateGrouping = TRUE		7 = xlFilterValues	'undefined'	'undefined'	Both Criteria1 and Criteria2 have no value. An attempt to read Criteria2 in VBA code will actually result in an error. No way to retrieve the filter settings through Criteria2. Even if this filtering was not set by the user but was set using VBA code then you would still not be able to read the settings back through Criteria2.
Single level date selection. ActiveWindow.AutoFilterDateGrouping = FALSE		2 = xlOr	"=03‑05‑90"	"=03‑05‑20"	Same as other type filtering with 2 values. Compare above filtering on 2 numbers. Criteria1 and Criteria2 date format is same as selected for the date column in the table.