I still occasionally use Excel VBA although I now try to use the Excel formula language and LAMBDAs if possible. However, sometimes it is easier and cleaner to implement something in VBA.
Something I find painful in Excel is the absence of set logic. In SQL we have operators such as UNION, EXCEPT, and INTERSECT and they are super useful. I have seen implementations of these in Excel using combinations of FILTER, MATCH and so on in LAMBDAs. They implementations might be clever but they are not clear or intuitive to me.
As a challenge, I decided to try to implement them in VBA using dictionaries. The implementations are not yet complete but I am making progress. Once I have finished, I will post an extensive blog on the subject.
I have uploaded two _.bas module files and two .cls class files.
- clsSet.cls: This contains the set logic code and is the key file.
- clsTestSet: Testing code for the main class file. Trying to implement a poor man's unit testing in VBA 🤪.*
- modTest_clsSets: Creates the class instance for testing and calls the test routines.
- SetUDFs.bas: User-defined functions that use the main class and that can be called in Excel.
* Before you ask, yes, I have tried RubberDuck but it is not working for me. Might be because I do my VBA work on a locked down work laptop 🤷♂️
I have also uploaded the macro-enabled Excel file and it contains all the code, name Sets.xlsm.
- Not yet finished!
⚠️ - The code is not yet fully implemented or documented.
- I will write a longer explanation of how this works in a blog once I have finished the coding.