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

Assign unique random number using Excel

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

HKID_check_digit

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)

Outbound References

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

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *