ExcelBanter - Canadian SIN

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Canadian SIN (https://www.excelbanter.com/excel-programming/299428-canadian-sin.html)

Good morning everyone,

On a Useform I have a textbox where the user must enter a
Social Insurance Number (Canadian).

I would like to verify if the number entered is valid.
Someone gave me the following code:

Function NumberValidation(CodeNumber)

Dim E, i, SUBT, TOT, LAST

If (IsNumeric(CodeNumber)) and (Len(CodeNumber) 8) and
(Len(CodeNumber) < 12) Then
If (Len(CodeNumber) = 11) Then
CodeNumber = Del(CodeNumber, 4, 1)
CodeNumber = Del(CodeNumber, 7, 1)
End If
TOT = 0
For i = 1 To 8
E = mid(CodeNumber,i,1)
If ((i Mod 2) = 0) Then
SUBT = (E * 2)
Else
SUBT = E
End If
If (SUBT 9) Then
SUBT = Mid(SUBT, 1, 1)*1 + Mid(SUBT, 2, 1)
End If
TOT = TOT + SUBT
Next
If (TOT 9) Then
LAST = (10 - ((Mid(TOT, 2, 1))))
Else
LAST = (10 - ((Mid(TOT, 1, 1))))
End If
If (LAST 9) Then LAST = (Mid(LAST, 2, 1))
If Mid(CodeNumber, 9, 1) = Trim(LAST) Then
NumberValidation = True
Else
NumberValidation = False
End If
Else
NumberValidation = False
End If
End Function

However, Excel doesn't seem to like it. It returns an
error message on

CodeNumber = Del(CodeNumber, 4, 1)

Anyone would have an idea why? Or maybe a better
solution ?

Thanks for your help...

Dennis

Hi Dennis,

Dennis wrote:
On a Useform I have a textbox where the user must enter a
Social Insurance Number (Canadian).

I would like to verify if the number entered is valid.
Someone gave me the following code:

<code snipped

However, Excel doesn't seem to like it. It returns an
error message on

CodeNumber = Del(CodeNumber, 4, 1)

Not sure what that code is attempting to do. There is no Del function in
VBA, so an error will occur. Maybe there was a custom function named Del
that was supposed to included with the code?

Here's some code that I came up with that might do what you're looking for:

Public Function gbVerifySIN(rsSIN As String) _
As Boolean
Dim sNums As String
Dim nChar As Integer
Dim nNum As Integer
Dim sNum As String
Dim nTot As Integer

If rsSIN Like "###-###-###" Then
sNums = Replace$(rsSIN, "-", "")
For nChar = 1 To 8
If nChar Mod 2 = 0 Then
sNum = CStr(CInt(Mid$(sNums, _
nChar, 1)) * 2)
If Len(sNum) = 1 Then
nTot = nTot + CInt(sNum)
Else
nTot = nTot + CInt(Left$(sNum, 1)) + _
CInt(Mid$(sNum, 2))
End If
Else
sNum = Mid$(sNums, nChar, 1)
nTot = nTot + CInt(sNum)
End If
Next nChar
gbVerifySIN = ((10 - nTot Mod 10) = _
CInt(Right$(sNums, 1)))
End If
End Function

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Good afternoon Jake,

Thanks a lot for your reply. I will give it a try and come
back to you....

Have a nice day...

Dennis

-----Original Message-----
Hi Dennis,

Dennis wrote:
On a Useform I have a textbox where the user must enter
a
Social Insurance Number (Canadian).

I would like to verify if the number entered is valid.
Someone gave me the following code:

<code snipped

However, Excel doesn't seem to like it. It returns an
error message on

CodeNumber = Del(CodeNumber, 4, 1)

Not sure what that code is attempting to do. There is no
Del function in
VBA, so an error will occur. Maybe there was a custom
function named Del
that was supposed to included with the code?

Here's some code that I came up with that might do what
you're looking for:

Public Function gbVerifySIN(rsSIN As String) _
As Boolean
Dim sNums As String
Dim nChar As Integer
Dim nNum As Integer
Dim sNum As String
Dim nTot As Integer

If rsSIN Like "###-###-###" Then
sNums = Replace$(rsSIN, "-", "")
For nChar = 1 To 8
If nChar Mod 2 = 0 Then
sNum = CStr(CInt(Mid$(sNums, _
nChar, 1)) * 2)
If Len(sNum) = 1 Then
nTot = nTot + CInt(sNum)
Else
nTot = nTot + CInt(Left$(sNum,
1)) + _
CInt(Mid$(sNum, 2))
End If
Else
sNum = Mid$(sNums, nChar, 1)
nTot = nTot + CInt(sNum)
End If
Next nChar
gbVerifySIN = ((10 - nTot Mod 10) = _
CInt(Right$(sNums, 1)))
End If
End Function

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address
unmonitored]

.

You can get this information from CCRA.

They have a formula that you can put in your form to check
the valadity of a SIN.

Cheers
-----Original Message-----
Good morning everyone,

On a Useform I have a textbox where the user must enter a
Social Insurance Number (Canadian).

I would like to verify if the number entered is valid.
Someone gave me the following code:

Function NumberValidation(CodeNumber)

Dim E, i, SUBT, TOT, LAST

If (IsNumeric(CodeNumber)) and (Len(CodeNumber) 8)
and
(Len(CodeNumber) < 12) Then
If (Len(CodeNumber) = 11) Then
CodeNumber = Del(CodeNumber, 4, 1)
CodeNumber = Del(CodeNumber, 7, 1)
End If
TOT = 0
For i = 1 To 8
E = mid(CodeNumber,i,1)
If ((i Mod 2) = 0) Then
SUBT = (E * 2)
Else
SUBT = E
End If
If (SUBT 9) Then
SUBT = Mid(SUBT, 1, 1)*1 + Mid(SUBT, 2, 1)
End If
TOT = TOT + SUBT
Next
If (TOT 9) Then
LAST = (10 - ((Mid(TOT, 2, 1))))
Else
LAST = (10 - ((Mid(TOT, 1, 1))))
End If
If (LAST 9) Then LAST = (Mid(LAST, 2, 1))
If Mid(CodeNumber, 9, 1) = Trim(LAST) Then
NumberValidation = True
Else
NumberValidation = False
End If
Else
NumberValidation = False
End If
End Function

However, Excel doesn't seem to like it. It returns an
error message on

CodeNumber = Del(CodeNumber, 4, 1)

Anyone would have an idea why? Or maybe a better
solution ?

Thanks for your help...

Dennis
.

I went to a CCRA Office and got the "Payroll Deductions
Formulas for Computer Programs".

It starts on page 23 but the formula is on page 24.

Example

The employee provides SIN 193-456-787

Basic number(first eight digits) check digit

193 456 78 7

Make a number from each alternate position to the left

beginning at the second digit 9 4 6 8
Add the number to itself 9 4 6 8
Sum 18 8 12 16

Cross-add the digits in the sum

(1 + 8 + 8 + 1 + 2 + 1 + 6) = 27

Add each alternate digit beginning at the first digit

(1 + 3 + 5 + 7) = 16

27
16
Total 43

If the total is a multiple of 10, the check digit should
be 0; otherwise, subtract the total calculation (43) from
the next highest number ending with 0 (50) in this case.

The check digit is (50 - 43) = 7

This is how it is writen in the "Payroll Deductions
Formulas for Computer Programs"" from CCRA.

GOOD LUCK

René
-----Original Message-----
Good morning everyone,

On a Useform I have a textbox where the user must enter a
Social Insurance Number (Canadian).

I would like to verify if the number entered is valid.
Someone gave me the following code:

Function NumberValidation(CodeNumber)

Dim E, i, SUBT, TOT, LAST

If (IsNumeric(CodeNumber)) and (Len(CodeNumber) 8)
and
(Len(CodeNumber) < 12) Then
If (Len(CodeNumber) = 11) Then
CodeNumber = Del(CodeNumber, 4, 1)
CodeNumber = Del(CodeNumber, 7, 1)
End If
TOT = 0
For i = 1 To 8
E = mid(CodeNumber,i,1)
If ((i Mod 2) = 0) Then
SUBT = (E * 2)
Else
SUBT = E
End If
If (SUBT 9) Then
SUBT = Mid(SUBT, 1, 1)*1 + Mid(SUBT, 2, 1)
End If
TOT = TOT + SUBT
Next
If (TOT 9) Then
LAST = (10 - ((Mid(TOT, 2, 1))))
Else
LAST = (10 - ((Mid(TOT, 1, 1))))
End If
If (LAST 9) Then LAST = (Mid(LAST, 2, 1))
If Mid(CodeNumber, 9, 1) = Trim(LAST) Then
NumberValidation = True
Else
NumberValidation = False
End If
Else
NumberValidation = False
End If
End Function

However, Excel doesn't seem to like it. It returns an
error message on

CodeNumber = Del(CodeNumber, 4, 1)

Anyone would have an idea why? Or maybe a better
solution ?

Thanks for your help...

Dennis
.