Work in progress: some to-be-implemented events (e.g. multiple rows/cols added at once) currently will trigger Debug.Assert
s rather than gracefully failing. As a result this code is not safe for use in the wild. My recommendation is to replace the Debug.Assert False
lines in the [Default/NoCache]Categoriser.cls
with events.RaiseDataValueChanged target
to raise a generic event, if you want to use this code right now. Many events are implemented (see https://github.com/Greedquest/ListObject-WithEvents/issues/4).
Get events when your Excel Tables (ListObjects) expand to hold new data, values change, rows are added and deleted and more...
This repo follows Test Driven Development. As such all of its features are self documenting:
Clone Repo or download as Zip (see https://github.com/Greedquest/ListObject-WithEvents/issues/2 for details on Zip download). Then add all the files to your project by clicking and dragging onto the project explorer. Ignore .doccls
files and _tests
files unless testing (see below).
Private WithEvents fooTableEvents As TableWatcher
Sub StartListening()
Set fooTableEvents = TableWatcher.Create(Sheet1.ListObjects("foo"))
End Sub
Private Sub fooTableEvents_RowAppended(ByVal where As ListRow)
Debug.Print "New Row added to table Foo -"; where.DataBodyRange.Address
End Sub
The constructor optionally takes a categoriser - an object which converts Worksheet_Change
events into TableWatcher
events.
TableWatcher.Create(ByVal srcTable As ListObject, Optional ByVal eventsCategoriser As IWorksheetChangeCategoriser)
2 categorisers are provided to get you started:
Application.EventsEnabled = False
and a modification is made to the table then the categoriser gets out of sync and may miscategorise events.Application.EventsEnabled
is toggled on/off. (Of course, no events will be triggered when Application.EventsEnabled = False
by either categoriser)If you wish to run the RD unit tests, you must create a new workbook with 2 sheets. Set the Name
and CodeName
properties of the 2 sheets to TestSheet
and DemoSheet
respectively. In RD right click in the project explorer and choose Sync->Replace Contents from Files
and select the entire src directory. This will import all the code including the document modules. Finally create a new excel table on DemoSheet
it must have at least 3 rows and 3 columns for the tests to run and set its name to DemoTable
. The Unit tests should then all run successfully.