Data Entry UserForm linked to table with Classes

Decided to make a UserForm and link it to a table with a Class object. The code behind the navigation buttons is expressed very simply as the code is obscured by encapsualion.

Private Sub btnMoveFirst_Click()
    tbl.MoveFirst
End Sub

Private Sub btnMoveLast_Click()
    tbl.MoveLast
End Sub

Private Sub btnMoveNext_Click()
    tbl.MoveNext
End Sub

Private Sub btnMovePrevious_Click()
    tbl.MovePrevious
End Sub

Download

The Scripting Dictionary

Just recently discovered the scripting dictionary which comes as part of the Windows family of operating systems, so unfortunately this can't be levered by VBA on Macs.

Dictionaries are a common enough feature of various languages, but something that VBA lacks natively. The closest VBA has is collections, but these are read-only. You can add or remove items from a collection, but you can't change an item's value. You could use an array, but the index is always a number in an array. In a dictionary, the key (index) can be anything you like, as long as it's unique. The value can be anything too (even objects).

This is great for looping through lists and finding unique values, assigning them to a key and adding the value in a related column. You can even set the item equal to a range that you can do something with later.

This script finds unique values in Column A, uses Union to create a multiple range object encompassing each of the unique values, then colours each of the ranges in a different colour.

Sub foo()
    Dim c As Range, sKey As String, i As Long    
    With CreateObject("Scripting.Dictionary")
        For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
            sKey = c.Value
            If Not .exists(sKey) Then
                Set .Item(sKey) = c.Resize(1, 2)
            Else
                Set .Item(sKey) = Union(.Item(sKey), c.Resize(1, 2))
            End If
        Next
        For i = 0 To .Count - 1
            Debug.Print "Values :" & .Keys()(i) & ", with Range(" & _
            .Items()(i).Address & ") has " & .Items()(i).Cells.Count & " cells"
            .Items()(i).Interior.ColorIndex = 34 + i
        Next i
    End With
End Sub

Date Picker with classes

Almost there! One or two quirky bugs to sort out but looking good at the moment.

Please feel free to download it and try it out! If you have any comments or suggestions, please let me know. I'm still learning and would appreciate any advice you may have.

The idea behind this project was to create a date picker that lived on the form with the other controls that didn't need an add in. I know DTPicker does the same thing, but was shocked to find that Office 2016 at home was missing the reference to it. After a bit of Googling it turns out that DTPicker is becoming increasingly depreciated. It was also an excuse to finally dip my toe in the waters of writing class objects. My first effort, so be kind!

Download: DatePicker