S.I.N. is the Social Insurance Number in Canada.
Here is a formula in VBA to validate the S.I.N.
Private Sub cmdValidation_Click()
Dim strSIN As String
Dim str1 As String
Dim str2 As String
Dim str3 As String
Dim str4 As String
Dim str5 As String
Dim str6 As String
Dim str7 As String
Dim str8 As String
Dim str9 As String
Dim Int1 As Integer
Dim Int2 As Integer
Dim Int3 As Integer
Dim Int4 As Integer
Dim Int5 As Integer
Dim Int6 As Integer
Dim Int7 As Integer
Dim Int8 As Integer
Dim Int9 As Integer
Dim FirstDigit As Integer
Dim SecondDigit As Integer
txtTEST.SetFocus 'set focus on textbox
strSIN = txtTEST.Text
str1 = Mid(strSIN, 1, 1)
str2 = Mid(strSIN, 2, 1)
str3 = Mid(strSIN, 3, 1)
str4 = Mid(strSIN, 4, 1)
str5 = Mid(strSIN, 5, 1)
str6 = Mid(strSIN, 6, 1)
str7 = Mid(strSIN, 7, 1)
str8 = Mid(strSIN, 8, 1)
str9 = Mid(strSIN, 9, 1)
MsgBox _
str1 & " " & _
str2 & " " & _
str3 & "-" & _
str4 & " " & _
str5 & " " & _
str6 & "-" & _
str7 & " " & _
str8 & " " & _
str9
Int1 = 1 * CInt(str1)
Int2 = 2 * CInt(str2)
Int3 = 1 * CInt(str3)
Int4 = 2 * CInt(str4)
Int5 = 1 * CInt(str5)
Int6 = 2 * CInt(str6)
Int7 = 1 * CInt(str7)
Int8 = 2 * CInt(str8)
Int9 = 1 * CInt(str9)
If Int1 > 9 Then
FirstDigit = 1
SecondDigit = Int1 - 10
Int1 = FirstDigit + SecondDigit
End If
If Int2 > 9 Then
FirstDigit = 1
SecondDigit = Int2 - 10
Int2 = FirstDigit + SecondDigit
End If
If Int3 > 9 Then
FirstDigit = 1
SecondDigit = Int3 - 10
Int3 = FirstDigit + SecondDigit
End If
If Int4 > 9 Then
FirstDigit = 1
SecondDigit = Int4 - 10
Int4 = FirstDigit + SecondDigit
End If
If Int5 > 9 Then
FirstDigit = 1
SecondDigit = Int5 - 10
Int5 = FirstDigit + SecondDigit
End If
If Int6 > 9 Then
FirstDigit = 1
SecondDigit = Int6 - 10
Int6 = FirstDigit + SecondDigit
End If
If Int7 > 9 Then
FirstDigit = 1
SecondDigit = Int7 - 10
Int7 = FirstDigit + SecondDigit
End If
If Int8 > 9 Then
FirstDigit = 1
SecondDigit = Int8 - 10
Int8 = FirstDigit + SecondDigit
End If
If Int9 > 9 Then
FirstDigit = 1
SecondDigit = Int9 - 10
Int9 = FirstDigit + SecondDigit
End If
MsgBox _
Int1 & " " & _
Int2 & " " & _
Int3 & "-" & _
Int4 & " " & _
Int5 & " " & _
Int6 & "-" & _
Int7 & " " & _
Int8 & " " & _
Int9
Dim intSum As Integer
intSum = Int1 + Int2 + Int3 + Int4 + Int5 + Int6 + Int7 + Int8 + Int9
txtTEST2.SetFocus
txtTEST2.Text = intSum
End Sub
