From b680c7f41f9d1518d5ce373d7301c03e4dc6a574 Mon Sep 17 00:00:00 2001
From: Mohammad S Anwar <mohammad.anwar@yahoo.com>
Date: Wed, 20 Oct 2021 08:12:09 +0100
Subject: - Added guest contributions by Eric Cheung.

---
 .../eric-cheung/excel-vba/Challenge_135.xlsm       | Bin 0 -> 37000 bytes
 challenge-135/eric-cheung/excel-vba/ch-1.bas       |  43 +++++++++
 challenge-135/eric-cheung/excel-vba/ch-2.bas       | 100 +++++++++++++++++++++
 3 files changed, 143 insertions(+)
 create mode 100755 challenge-135/eric-cheung/excel-vba/Challenge_135.xlsm
 create mode 100755 challenge-135/eric-cheung/excel-vba/ch-1.bas
 create mode 100755 challenge-135/eric-cheung/excel-vba/ch-2.bas

diff --git a/challenge-135/eric-cheung/excel-vba/Challenge_135.xlsm b/challenge-135/eric-cheung/excel-vba/Challenge_135.xlsm
new file mode 100755
index 0000000000..93bd67b8f2
Binary files /dev/null and b/challenge-135/eric-cheung/excel-vba/Challenge_135.xlsm differ
diff --git a/challenge-135/eric-cheung/excel-vba/ch-1.bas b/challenge-135/eric-cheung/excel-vba/ch-1.bas
new file mode 100755
index 0000000000..d9b68d9b89
--- /dev/null
+++ b/challenge-135/eric-cheung/excel-vba/ch-1.bas
@@ -0,0 +1,43 @@
+Attribute VB_Name = "ModTask_01"
+Option Explicit
+
+Public Const strMyTitle As String = "Eric Cheung"
+
+Function GetMiddleThreeStr(ByVal nInputNum As Long) As String
+    
+    Dim strTempNum As String, nNumLen As Integer
+    
+    If nInputNum > 0 Then
+        strTempNum = CStr(nInputNum)
+    Else
+        strTempNum = CStr(-nInputNum)
+    End If
+    
+    nNumLen = Len(strTempNum)
+    
+    If nNumLen = 1 Then
+        GetMiddleThreeStr = "Too Short"
+        Exit Function
+    End If
+    
+    If nNumLen Mod 2 = 0 Then
+        GetMiddleThreeStr = "Even number of digits"
+        Exit Function
+    End If
+    
+    GetMiddleThreeStr = Mid(strTempNum, (nNumLen - 1) / 2, 3)
+
+End Function
+
+Sub Task_01()
+    
+    Dim strMsg As String
+    
+    '' strMsg = GetMiddleThreeStr(1234567) '' Example: 1
+    '' strMsg = GetMiddleThreeStr(-123) '' Example: 2
+    '' strMsg = GetMiddleThreeStr(1) '' Example: 3
+    strMsg = GetMiddleThreeStr(10) '' Example: 4
+    
+    MsgBox strMsg, vbOKOnly, strMyTitle
+    
+End Sub
diff --git a/challenge-135/eric-cheung/excel-vba/ch-2.bas b/challenge-135/eric-cheung/excel-vba/ch-2.bas
new file mode 100755
index 0000000000..539dea06fa
--- /dev/null
+++ b/challenge-135/eric-cheung/excel-vba/ch-2.bas
@@ -0,0 +1,100 @@
+Attribute VB_Name = "ModTask_02"
+Option Explicit
+
+'' Credit:
+'' https://en.wikipedia.org/wiki/SEDOL
+'' http://rosettacode.org/wiki/SEDOLs
+'' http://excelevolution.com/validate-sedol-using-vba/
+
+Public g_objREGEX As Object
+ 
+Public Function CharToDigit(ByVal strSecurity As String) As Variant
+    Dim objMatch As Object
+    Dim objMatches As Object
+    Dim strStagingString As String
+
+    If g_objREGEX Is Nothing Then
+        Set g_objREGEX = CreateObject("vbscript.regexp")
+    End If
+
+    With g_objREGEX
+        .Global = True
+        .IgnoreCase = False
+        .Pattern = "([A-Z])"
+    End With
+
+    strStagingString = Left$(StrConv(strSecurity, vbUnicode), Len(strSecurity) * 2 - 1)
+
+    Set objMatches = g_objREGEX.Execute(strStagingString)
+
+    For Each objMatch In objMatches
+        strStagingString = Replace$(strStagingString, objMatch.Value, Asc(objMatch.Value) - (Asc("A") - 10))
+    Next objMatch
+
+    CharToDigit = Split(strStagingString, Chr$(0))
+End Function
+ 
+Public Function IsSEDOLPattern(ByVal strSEDOL As String) As Boolean
+    If g_objREGEX Is Nothing Then
+        Set g_objREGEX = CreateObject("vbscript.regexp")
+    End If
+ 
+    With g_objREGEX
+        .IgnoreCase = False
+        .Pattern = "^[B-DF-HJ-NP-TV-Z0-9]{6}[0-9]{1}$"
+    End With
+ 
+    IsSEDOLPattern = g_objREGEX.Test(strSEDOL)
+End Function
+
+Public Function IsValidSEDOL(ByVal strSEDOL As String, Optional ByVal blnNew As Boolean = False) As Boolean
+    Dim lngCheckNum As Long
+    Dim varNums As Variant
+    Dim lngResult As Long
+    
+    Const nBaseTen As Integer = 10
+
+    strSEDOL = UCase$(Format$(strSEDOL, "0000000"))
+
+    If Not IsSEDOLPattern(strSEDOL) Then
+        IsValidSEDOL = False
+        Exit Function
+    End If
+
+    If blnNew Then
+        If IsNumeric(Left(strSEDOL, 1)) Then
+            IsValidSEDOL = False
+            Exit Function
+        End If
+    End If
+
+    lngCheckNum = CLng(Right$(strSEDOL, 1))
+
+    varNums = Join$(CharToDigit(Left$(strSEDOL, 6)), ",")
+
+    lngResult = (nBaseTen - (Evaluate("sumproduct({" & varNums & "},{1,3,1,7,3,9})") Mod nBaseTen)) Mod nBaseTen
+
+    IsValidSEDOL = CBool(lngCheckNum = lngResult)
+End Function
+
+Sub Task_02()
+
+    '' Const strSEDOLToCheck As String = "2936921" '' Example: 1
+    '' Const strSEDOLToCheck As String = "1234567" '' Example: 2
+    Const strSEDOLToCheck As String = "B0YBKL9" '' Example: 3
+
+    Dim strMsg As String
+    
+    If IsValidSEDOL(strSEDOLToCheck) Then
+        strMsg = "1"
+    Else
+        strMsg = "0"
+    End If
+
+    MsgBox strMsg, vbOKOnly, strMyTitle
+
+    If Not g_objREGEX Is Nothing Then
+        Set g_objREGEX = Nothing
+    End If
+
+End Sub
-- 
cgit