# How to write vba to check the last digit of hong kong ID card number

Discussion in 'Microsoft Excel Programming' started by eric chan, Oct 15, 2003.

1. ### eric chanGuest

I am an new learner to write execel vba.

Now, I come across a question about how to write vba so as to check
the last digit of hong kong id card number, says B583418(5), how do
you know the last number is 5, and how do you write this excel vba to
expedite you working.

If anyone know how to write it, please let me have the solution.

Thanks a lot !!
14/10/03

eric chan, Oct 15, 2003

2. ### pkGuest

Write the following as one line:

xLastValue = Mid(ActiveCell.FormulaR1C1,
Len(ActiveCell.FormulaR1C1) - 1, 1)

This should do it for you, but it assumes that all card
numbers are formatted the same way. That is, that the
number you want is always the next to the last character
in the string.

Hope this helps...

>-----Original Message-----
>I am an new learner to write execel vba.
>
>Now, I come across a question about how to write vba so

as to check
>the last digit of hong kong id card number, says B583418

(5), how do
>you know the last number is 5, and how do you write this

excel vba to
>expedite you working.
>
>If anyone know how to write it, please let me have the

solution.
>
>Thanks a lot !!
>14/10/03
>.
>

pk, Oct 15, 2003

3. ### Tom OgilvyGuest

sStr = "B583418(5)"
? Mid(sStr,len(sStr)-1,1)
5

--
Regards,
Tom Ogilvy

eric chan <> wrote in message
news:...
> I am an new learner to write execel vba.
>
> Now, I come across a question about how to write vba so as to check
> the last digit of hong kong id card number, says B583418(5), how do
> you know the last number is 5, and how do you write this excel vba to
> expedite you working.
>
> If anyone know how to write it, please let me have the solution.
>
> Thanks a lot !!
> 14/10/03

Tom Ogilvy, Oct 15, 2003
4. ### keepitcoolGuest

hastily put together.. without errorchecking
dont have any ID's to check here.
but it's according to the site's theory..

Sub IDTest()
Dim s\$
s = InputBox("Enter ID")
MsgBox "ValidatedID: " & HongKongID(s)
End Sub

Function HongKongID(sID As String)
Dim s\$, i%, n(1 To 7), r
'Theory: http://home.hkstar.com/~maukwan/fun/digit.htm

s = UCase(Left(sID, 7))
For i = 1 To 7
n(i) = Mid(s, i, 1)
Next
n(1) = 1 + (Asc(n(1)) - 65) Mod 11
For i = 1 To 7
r = r + Val(n(i)) * (9 - i)
Next
r = 11 - r Mod 11

HongKongID = s & " (" & r & ")"

End Function

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

(eric chan) wrote:

> I am an new learner to write execel vba.
>
> Now, I come across a question about how to write vba so as to check
> the last digit of hong kong id card number, says B583418(5), how do
> you know the last number is 5, and how do you write this excel vba to
> expedite you working.
>
> If anyone know how to write it, please let me have the solution.
>
> Thanks a lot !!
> 14/10/03
>

keepitcool, Oct 15, 2003