Access Excel Generate random HKID Hong Kong ID card number

This Access Excel VBA tutorial demonstrates how to generate random HKID / Hong Kong ID card number.

You may also want to read:

Access Excel Function HKID Check Digit (last digit)

Excel VBA Rnd Function to generate random number

Excel generate random letter A to Z using RANDBETWEEN

Access Excel Generate random HKID  (Hong Kong ID card number)

The purpose of this article is to generate a random HKID (Hong Kong ID card number) that has valid Check Digit (last digit in brackets) for use in system testing.

In another article, I will make use of some previous custom Function I wrote, that include how to check last digit of HKID and how to generate random number / letter.

VBA Code – Access Excel Generate random HKID  (Hong Kong ID card number)

The below Function generates single letter random HKID, such as A123456(7). (There are double letter HKID)

I can only guarantee to generate a random HKID of valid Check Digit, but I can’t find a source of credibility that lists the rules for the first letter and there seem to be exceptional cases.

Without such information, I can only assume HKID first letter can be anything between A to Z.

Public Function wRandomHKID()
    Randomize
    randomLetter = Chr(Int((90 - 65 + 1) * Rnd + 65))
    randomNumber1 = Int((9 - 0 + 1) * Rnd + 0)
    randomNumber2 = Int((9 - 0 + 1) * Rnd + 0)
    randomNumber3 = Int((9 - 0 + 1) * Rnd + 0)
    randomNumber4 = Int((9 - 0 + 1) * Rnd + 0)
    randomNumber5 = Int((9 - 0 + 1) * Rnd + 0)
    randomNumber6 = Int((9 - 0 + 1) * Rnd + 0)
    IDnoDigit = randomLetter & randomNumber1 & randomNumber2 & randomNumber3 & randomNumber4 & randomNumber5 & randomNumber6
    checkSum = (Asc(randomLetter) - 64) * 8 + randomNumber1 * 7 + randomNumber2 * 6 + randomNumber3 * 5 + randomNumber4 * 4 + randomNumber5 * 3 + randomNumber6 * 2
    checkDigit = 11 - checkSum Mod 11
   If checkDigit = 10 Then
        newCheckdigit = "A"
    ElseIf checkDigit = 11 Then
        newCheckdigit = "0"
    Else: newCheckdigit = checkDigit
    End If
    wRandomHKID = IDnoDigit & newCheckdigit
End Function

Function Syntax – Access Excel Generate random HKID  (Hong Kong ID card number)

The Function is simple to use, no argument is required. Just type the following to generate a random HKID.

=wRandomHKID()

Outbound References

http://www.techonthenet.com/excel/formulas/asc.php

Access Excel Generate random HKID Hong Kong ID card number