- Added guest contributions by Eric Cheung.

3 files changed, 143 insertions, 0 deletions

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
--- /dev/null
+++ b/challenge-135/eric-cheung/excel-vba/Challenge_135.xlsm
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