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 Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

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 *