| Action | implementation | comment |
| Declare | Dim dict As Object | late binding |
| Create | Set dict = CreateObject(“Scripting.Dictionary”) | late binding |
| Change value at given key | dict(Key) = newValue dict(MyKeyVariable) = “MyNewItem” | can be used to add if the key doesn’t exist. |
| Storing number as text | dict(MyNumber) = “‘” & MyNumber | |
| Add new item | dict.Add Key, Value dict.Add MyKeyVariable, “MyNewItem” | only to be used if key doesn’t exist |
| Check if key exists | dict.Exists(Key) If dict.Exists(MyKeyVariable) Then | |
| Remove entry at Key | dict.Remove Key | All entries: dict.RemoveAll |
| Write all items to worksheet column | Cells(1, 1).Resize(dict.Count) = Application.Transpose(dict.items) | |
| Loop through | Dim i As Long For i = 0 To dict.Count – 1 Debug.Print dict.Keys()(i), dict.Items()(i) Next i | works with both early and late binding |
NETWORKDAYS without 2nd & 4th Saturday
Sometimes, for some employees, not all Saturdays are free 🙁
The presented below UDF to calculate NETWORKDAYS including Holidays list, but counting as working day every 2nd and 4th Saturday of the month was inspired by the request from the forum (unanswered for quite some time).
The applied algorithm is pretty strightforward – for each day in examined period check if it is Sunday, Holiday or Saturday (but not 2nd or 4th). If not add 1 to count and examine next day.
8×8 display planner
Simple file to facilitate planning of 8×8 LED matrix on MAXIM MAX7219 controlled 8×8 LED display module.
There each 8×8 range simiulate LED matrix. Cells with value 1 are “switched on” – red, and cells with 0 are switched off (background color as applied by the user for whole range).
Switching on/off cells can be done manually, or if you have macros enabled, just by selecting a cell or a range in rows 28:35. Also (as once a cell is selected, it can be selected again only after some other cell or other object was selected), similar action occurs after double click. Two simple event handler procedures (located in a worksheet code) are responsible for that. See the Arduino setup + code and watch it working on YouTube.