My typical use of dictionary

Dim dict As Object late binding
Set dict = CreateObject(“Scripting.Dictionary”)late binding
Change value at given keydict(Key) = newValue
dict(MyKeyVariable) = “MyNewItem”
can be used to add if the key doesn’t exist.
Storing number as textdict(MyNumber) = “‘” & MyNumber
Add new itemdict.Add Key, Value
dict.Add MyKeyVariable, “MyNewItem”
only to be used if key doesn’t exist
Check if key existsdict.Exists(Key)
If dict.Exists(MyKeyVariable) Then
Remove entry at Keydict.Remove KeyAll entries: dict.RemoveAll
Write all items to worksheet columnCells(1, 1).Resize(dict.Count) = Application.Transpose(dict.items)
Loop throughDim 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.

Continue reading NETWORKDAYS without 2nd & 4th Saturday

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.

Continue reading 8×8 display planner