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 chan

    eric chan Guest

    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
    #1
    1. Advertisements

  2. eric chan

    pk Guest

    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
    #2
    1. Advertisements

  3. eric chan

    Tom Ogilvy Guest

    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
    #3
  4. eric chan

    keepitcool Guest

    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
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. wliong

    How to Eliminate the first 2 digit out of 10 digit number

    wliong, Nov 25, 2005, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    173
    wliong
    Nov 25, 2005
  2. new.microsoft.com

    Do anyone know to validating rule for checking the Hong Kong Identity Card no

    new.microsoft.com, Nov 29, 2005, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    428
    NickHK
    Nov 29, 2005
  3. Guest

    How do I shorten a 17 digit field to a 6 digit field?

    Guest, Jul 27, 2006, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    207
    Don Guillett
    Jul 27, 2006
  4. Arnold
    Replies:
    5
    Views:
    204
    Ron Rosenfeld
    Aug 2, 2007
  5. John Menken

    Two digit dates and two digit days

    John Menken, Jan 13, 2012, in forum: Microsoft Excel Programming
    Replies:
    3
    Views:
    375
    isabelle
    Jan 17, 2012
Loading...

Share This Page