# 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) 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