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
Assign unique random number using Excel
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 this article, I will make use of some previous custom Functions I previously 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 have made assumption that HKID first letter can be anything from A to Z.
Public Function wRandomHKID(Optional numType = 1)
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
If numType = 1 Then
wRandomHKID = IDnoDigit & newCheckdigit
ElseIf numType = 2 Then
wRandomHKID = IDnoDigit & "(" & newCheckdigit & ")"
End If
End Function
Function Syntax – Access Excel Generate random HKID (Hong Kong ID card number)
The Function accepts an optional parameter.
=wRandomHKID(numType)
| Argument | Explanation |
| numType | An optional argument, you may choose to display whether the last digit has brackets. By default the value is 1 numType =1 : no bracket numType = 2: add brackets to last digit |
Example
| Formula | Value |
| =wRandomHKID() | O3348661 |
| =wRandomHKID(1) | E3278240 |
| =wRandomHKID(2) | O132710(1) |