Catching a duplicate frame number the moment it's scanned.
Ride Me Cycles logs every incoming bike by its frame number as stock arrives. A duplicate scanned in by mistake used to go unnoticed until a stock count came up one bike short. A lookup that's O(1) per scan catches it immediately instead.
BlackTor Group LtdDartmoor, UK
A note on this page
A reference, not a client write-up.
Illustrative, per the note on the main VBA page: not real client work. It sits alongside the Warranty Claims and Resilient Export pages as a deeper dive on one routine that comes up often enough to be worth its own write-up.
The issue
Checking against the whole log, every single scan, gets slower all year.
The most direct way to check a scanned frame number against everything already logged is to loop down the log column and compare each cell. That's correct, and it's fine on the first day of a stock year when the log is nearly empty. It gets slower as the year goes on: by the time the log holds a few thousand rows, checking one scan means comparing against a few thousand cells, and the whole day's intake means doing that thousands of times over.
The fix isn't a faster loop, it's not looping at all for the check itself: a Scripting.Dictionary looks a key up in roughly constant time no matter how many keys it already holds, so the check stays just as fast on the last scan of the year as the first.
Dictionary lookup
IsDuplicateFrameNumber: an O(1) check instead of a re-scan per scan
Option Explicit
' IsDuplicateFrameNumber
' ----------------------
' Checks a newly scanned frame number against every frame number already
' logged this stock year, using a Scripting.Dictionary built once per
' session rather than re-scanning the log range for every single entry.
' A worksheet loop re-read for every scan is O(n) per check and O(n^2)
' over a full day's intake; the dictionary lookup is O(1) per check
' regardless of how many rows the log already holds.
'
' dictExistingFrames is populated once, on first use, by
' LoadExistingFrameNumbers below.
Private dictExistingFrames As Object ' Scripting.Dictionary, late-bound
Public Sub LoadExistingFrameNumbers()
Dim wksLog As Worksheet
Dim varFrameNumbers As Variant
Dim lngLastRow As Long
Dim lngRowIndex As Long
Set dictExistingFrames = CreateObject("Scripting.Dictionary")
dictExistingFrames.CompareMode = vbTextCompare ' frame numbers are case-insensitive
Set wksLog = ThisWorkbook.Worksheets("Frame Log")
lngLastRow = wksLog.Cells(wksLog.Rows.Count, "A").End(xlUp).Row
If lngLastRow < 2 Then Exit Sub ' header row only, nothing logged yet
' Read the whole frame-number column into an array in one call, not
' cell by cell: a single Range.Value read is orders of magnitude
' faster than looping cell references -- the same principle as the
' "read a range into an array once" routine on the main VBA page.
varFrameNumbers = wksLog.Range("A2:A" & lngLastRow).Value
For lngRowIndex = 1 To UBound(varFrameNumbers, 1)
If Len(varFrameNumbers(lngRowIndex, 1)) > 0 Then
dictExistingFrames(CStr(varFrameNumbers(lngRowIndex, 1))) = True
End If
Next lngRowIndex
End Sub
Public Function IsDuplicateFrameNumber(ByVal strFrameNumber As String) As Boolean
If dictExistingFrames Is Nothing Then LoadExistingFrameNumbers
IsDuplicateFrameNumber = dictExistingFrames.Exists(strFrameNumber)
End Function
Public Sub LogScannedFrame(ByVal strFrameNumber As String)
Dim wksLog As Worksheet
Dim lngNextRow As Long
If IsDuplicateFrameNumber(strFrameNumber) Then
MsgBox "Frame " & strFrameNumber & " is already on the log.", _
vbExclamation, "Duplicate frame number"
Exit Sub
End If
Set wksLog = ThisWorkbook.Worksheets("Frame Log")
lngNextRow = wksLog.Cells(wksLog.Rows.Count, "A").End(xlUp).Row + 1
wksLog.Cells(lngNextRow, "A").Value = strFrameNumber
wksLog.Cells(lngNextRow, "B").Value = Now
dictExistingFrames(strFrameNumber) = True ' keep the dictionary in step with the sheet
End Sub
LoadExistingFrameNumbers pays the cost of reading the log exactly once, as a single array read rather than cell by cell, and builds the dictionary from that array in memory. Every check after that is a dictionary lookup, not a worksheet read.
LogScannedFrame keeps the dictionary in step with the sheet by adding the new frame number to both in the same call: without that last line, the dictionary would quietly fall out of date after the very first new entry, and every check after it would be working from a stale picture of the log.
vbTextCompare on the dictionary matters here specifically because frame numbers get typed and scanned inconsistently in upper and lower case; without it, "RM2024001" and "rm2024001" would be treated as two different keys and the check would miss the duplicate entirely.
Result
The same check speed on the last scan of the year as the first.
A duplicate is caught and flagged the moment it's scanned, at a consistent speed regardless of how many thousand frame numbers are already on the log.
