EXCEL tips & tricks

Excel tips and tricks (v6 Aug 2020)

Contents

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'.

Speed up execution of VBA code / macro

To speed up the execution of code and to reduce screen flickering, I put the following lines before and after the code:

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

	< code to execute >

	Application.StatusBar = False
	Application.DisplayStatusBar = oldStatusBar
	Application.Cursor = xlDefault
	Application.Calculation = xlCalculationAutomatic
	Application.EnableEvents = True
	Application.ScreenUpdating = True

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.

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:

Excel userform control property ControlSource

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:

Excel userform controls value storage demo

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:

Excel form property StartUp Position

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:

Excel properties window positions

For instance 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.

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

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.

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

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':

The excel 'text to column function in the ribbon

... 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:

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:

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:

User defined function pop-up window

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:

User defined function category window

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:

Illustration of hide and un-hide in combination with Initialize and Activate events

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:

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

Examples of control events triggered by actions
Event: Parameters: Explanation:
ContolX_Change() - This event is triggered when the user has changed the value of the control or the value is changed programmatically in VBA code.
ContolX_Click() - This event is triggered when the user has clicked on the control or the control is assigned a value in VBA code.
ContolX_MouseDown()
ContolX_MouseUp()
Button
X
Y
This event is triggered when the user has clicked on the control.
The X and Y in points give a relative point where the user clicked. TopLeft is 0,0. X is to the right and Y is down.
Button is the mouse key that was pressed. Left=1, Right=2, DblClick=4
ContolX_KeyDown()
ContolX_KeyUp()
KeyCode This event is triggered when the user has pressed or released a key.
The KeyCode holds the value of the key pressed/released.
Click here for a list of KeyCodes
ContolX_KeyPress() KeyAscii This event is triggered when the user has pressed a key.
The KeyAscii holds the character of the key or key pressed.
The difference between a KeyCode and a KeyAscii is that a KeyCode represents 1 key on the keyboard. A KeyAscii is an actual visible character that can be read. For instance shift key has KeyCode=16 dec, Letter A has KeyCode=65 dec. Typing Shift+A will result in 2 key codes, one for the shift key and one for the A key but only 1 ASCII code 65
Click here for a ASCII character table.

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:

Examples of control events triggered by actions
Controls: Action: Events triggered in order: Explanation
CommandButton Mouse click on button when control has focus
  1. CommandButtonX_MouseDown()
  2. CommandButtonX_MouseUp()
  3. CommandButtonX_Click
Using Click() to detect button pressed is most commonly used.
CommandButton ENTER key pressed when control has focus
  1. CommandButtonX_KeyDown()
  2. CommandButtonX_Click
  3. CommandButtonX_KeyUp()
Using Click() to detect button pressed is most commonly used.
Label Mouse click on the label
  1. LabelX_Click
Image Mouse click on the image
  1. ImageX_MouseDown()
  2. ImageX_MouseUp()
The X & Y values can be used to do a certain action depending where in the image the user has clicked
CheckBox Mouse click on the checkbox
  1. CheckBoxX_MouseDown()
  2. CheckBoxX_MouseUp()
  3. CheckBoxX_Change
  4. CheckBoxX_Click
  5. CheckBoxX_AfterUpdate
CheckBox Change value programmatically e.g. CheckBox1.Value = True
  1. CheckBoxX_Change
  2. CheckBoxX_Click
OptionButton Mouse click on a unchecked option button X that is part of a group.
  1. OptionButtonN_Exit()
  2. OptionButtonX_Enter()
  3. OptionButtonX_MouseDown()
  4. OptionButtonX_MouseUp()
  5. OptionButtonN_Change
  6. OptionButtonX_Change
  7. OptionButtonX_Click
  8. OptionButtonX_AfterUpdate
The first EXIT() event is of another option button in the same group
After the mouse click you get 2 change events, one for the previous button and one for the current button.
TextBox Clicking in a text box and editing the contents followed by pressing ENTER key.
  1. TextBoxX_MouseDown()
  2. TextBoxX_MouseUp()
  3. TextBoxX_KeyDown
  4. TextBoxX_KeyPress
  5. TextBoxX_Change
  6. TextBoxX_KeyUp
  7. TextBoxX_KeyDown
  8. TextBoxX_Exit
The MouseDown/Up events appear after clicking in the text box.
For every key pressed you get a KeyDown/Up event
For every printable/visible character you get a KeyAscii event.
For every modification to the contents you get a change event.
The last KeyDown is for the ENTER key.
General When control X looses focus due to pressing TAB key or clicking another control.
  1. ControlX_Exit()
General When control X receives focus due to pressing TAB key or clicking on control X.
  1. ControlX_Enter()

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.

Controls properties Enabled and Locked

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.

UserForm2 screenshot that lists events triggered by user actions on the 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:

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:

Visual basic tools menu to include a library to the workbook

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

Visual basic library selection drop down menu

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:

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.

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:

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:

Filter table

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:
Multi level date filter
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
Single value selected with multi level filter
Show single value in table 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
Two value selected with single level filter
Two values listed in the table 2 = xlOr "=-102" "=21" This works only for a single level selection list as shown, NOT for multilevel date selection!
3 values selected
Three values selected with single level filter
Three value listed in the table 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
Start and end value selected with filter
Customer between start and end value 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'
Single value selected with filter
Customer selection 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
Two values selected with multi level filter
Customer multi selection 2 values 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
Two values selected with single level filter
Customer multi selection 2 values 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.