I was testing the code in post #17 (uncomment the 3rd last line & comment the 4th last line) with these commands in the immediate window:

? NextReferenceNumber("ABCXYZ0999")
and 
? NextReferenceNumber("ABCXYZ00999")
- they both gave me the result of ABCXYZ1000