diff options
| -rw-r--r-- | challenge-126/eric-cheung/excel-vba/Challenge_126.xlsm | bin | 0 -> 35210 bytes | |||
| -rw-r--r-- | challenge-126/eric-cheung/excel-vba/ch-1.bas | 62 | ||||
| -rw-r--r-- | challenge-126/eric-cheung/excel-vba/ch-2.bas | 157 |
3 files changed, 219 insertions, 0 deletions
diff --git a/challenge-126/eric-cheung/excel-vba/Challenge_126.xlsm b/challenge-126/eric-cheung/excel-vba/Challenge_126.xlsm Binary files differnew file mode 100644 index 0000000000..b870075d23 --- /dev/null +++ b/challenge-126/eric-cheung/excel-vba/Challenge_126.xlsm diff --git a/challenge-126/eric-cheung/excel-vba/ch-1.bas b/challenge-126/eric-cheung/excel-vba/ch-1.bas new file mode 100644 index 0000000000..0f3d5fdba1 --- /dev/null +++ b/challenge-126/eric-cheung/excel-vba/ch-1.bas @@ -0,0 +1,62 @@ +Attribute VB_Name = "ModTask_01"
+Option Explicit
+Option Base 1
+Public Const strMyTitle As String = "Eric Cheung"
+
+Function GetCountNum(nNum) As Long
+
+ Dim nLoop As Long, nCntItem As Long, nTenNum As Long, nResult As Long, nTemp As Long, nTenPower As Long
+ Dim nNumArr As Variant
+
+ If nNum <= 1 Then
+ GetCountNum = 0
+ Exit Function
+ End If
+
+ nCntItem = 9
+ nNumArr = Array(0, 2, 3, 4, 5, 6, 7, 8, 9)
+
+ nTenNum = 0
+ nResult = 0
+ nTenPower = 1
+
+ Do While True
+ For nLoop = 1 To nCntItem
+ nTemp = nTenNum * 10 + nNumArr(nLoop)
+ If nTemp = nNum Then
+ GetCountNum = nResult 'Exclude 0
+ Exit Function
+ ElseIf nTemp > nNum Then
+ GetCountNum = nResult - 1 'Exclude 0
+ Exit Function
+ End If
+ nResult = nResult + 1
+ Next nLoop
+
+ If nTenNum Mod 10 = 0 Then ''E.g. nTenNum: 0, 20, 30, 40, ...
+ nTenNum = nTenNum + 2
+ ElseIf nTenNum = nTenPower * 10 - 1 Then ''E.g. nTenNum: 9, 99, 999, ...
+ nTenPower = nTenPower * 10
+ nTenNum = nTenNum + nTenPower + 1
+ Else
+ nTenNum = nTenNum + 1
+ End If
+ Loop
+
+End Function
+
+Sub Task_01()
+
+ Dim varInputNum
+ Dim strMsg As String
+
+ Do
+ varInputNum = InputBox("Please enter a positive number", strMyTitle, 15)
+ If varInputNum = "" Then Exit Sub
+ Loop Until varInputNum > 0
+
+ strMsg = "There are " & GetCountNum(varInputNum) & " numbers between 1 and " & varInputNum & " that don't contain digit 1"
+
+ MsgBox strMsg, vbOKOnly, strMyTitle
+
+End Sub
diff --git a/challenge-126/eric-cheung/excel-vba/ch-2.bas b/challenge-126/eric-cheung/excel-vba/ch-2.bas new file mode 100644 index 0000000000..1d75c8adba --- /dev/null +++ b/challenge-126/eric-cheung/excel-vba/ch-2.bas @@ -0,0 +1,157 @@ +Attribute VB_Name = "ModTask_02"
+Option Explicit
+Option Base 1
+
+Function PrintArr(nMatrix, Optional bHide As Boolean = True) As String
+
+ Dim nRowLoop As Integer, nColLoop As Integer
+
+ For nRowLoop = LBound(nMatrix, 1) To UBound(nMatrix, 1)
+ For nColLoop = LBound(nMatrix, 2) To UBound(nMatrix, 2)
+ If PrintArr <> "" Then
+ PrintArr = PrintArr & " "
+ End If
+
+ If bHide And nMatrix(nRowLoop, nColLoop) = 0 Then
+ PrintArr = PrintArr & "*"
+ ElseIf nMatrix(nRowLoop, nColLoop) = -1 Then
+ PrintArr = PrintArr & "x"
+ Else
+ PrintArr = PrintArr & nMatrix(nRowLoop, nColLoop)
+ End If
+ Next nColLoop
+ PrintArr = PrintArr & vbNewLine
+ Next nRowLoop
+
+End Function
+
+Function CountLandMineNearBy(ByRef nMatrix, nRowIndex As Integer, nColIndex As Integer) As Integer
+
+ Dim nRowSize As Integer, nColSize As Integer
+
+ If nMatrix(nRowIndex, nColIndex) = -1 Then
+ CountLandMineNearBy = -1
+ Exit Function
+ End If
+
+ nRowSize = UBound(nMatrix, 1) - LBound(nMatrix, 1) + 1
+ nColSize = UBound(nMatrix, 2) - LBound(nMatrix, 2) + 1
+
+ If nRowIndex - 1 > 0 And nColIndex - 1 > 0 Then
+ If nMatrix(nRowIndex - 1, nColIndex - 1) = -1 Then
+ CountLandMineNearBy = CountLandMineNearBy + 1
+ End If
+ End If
+
+ If nRowIndex - 1 > 0 Then
+ If nMatrix(nRowIndex - 1, nColIndex) = -1 Then
+ CountLandMineNearBy = CountLandMineNearBy + 1
+ End If
+ End If
+
+ If nRowIndex - 1 > 0 And nColIndex < nColSize Then
+ If nMatrix(nRowIndex - 1, nColIndex + 1) = -1 Then
+ CountLandMineNearBy = CountLandMineNearBy + 1
+ End If
+ End If
+
+ If nColIndex - 1 > 0 Then
+ If nMatrix(nRowIndex, nColIndex - 1) = -1 Then
+ CountLandMineNearBy = CountLandMineNearBy + 1
+ End If
+ End If
+
+ If nColIndex < nColSize Then
+ If nMatrix(nRowIndex, nColIndex + 1) = -1 Then
+ CountLandMineNearBy = CountLandMineNearBy + 1
+ End If
+ End If
+
+ If nRowIndex < nRowSize And nColIndex - 1 > 0 Then
+ If nMatrix(nRowIndex + 1, nColIndex - 1) = -1 Then
+ CountLandMineNearBy = CountLandMineNearBy + 1
+ End If
+ End If
+
+ If nRowIndex < nRowSize Then
+ If nMatrix(nRowIndex + 1, nColIndex) = -1 Then
+ CountLandMineNearBy = CountLandMineNearBy + 1
+ End If
+ End If
+
+ If nRowIndex < nRowSize And nColIndex < nColSize Then
+ If nMatrix(nRowIndex + 1, nColIndex + 1) = -1 Then
+ CountLandMineNearBy = CountLandMineNearBy + 1
+ End If
+ End If
+
+End Function
+
+Sub CountLandMineNearByAll(ByRef nMatrix)
+
+ Dim nRowLoop As Integer, nColLoop As Integer
+
+ For nRowLoop = LBound(nMatrix, 1) To UBound(nMatrix, 1)
+ For nColLoop = LBound(nMatrix, 2) To UBound(nMatrix, 2)
+ If nMatrix(nRowLoop, nColLoop) > -1 Then
+ nMatrix(nRowLoop, nColLoop) = CountLandMineNearBy(nMatrix, nRowLoop, nColLoop)
+ End If
+ Next nColLoop
+ Next nRowLoop
+
+End Sub
+
+Sub Task_02()
+
+ Const nRowNum As Integer = 5
+ Const nColNum As Integer = 10
+
+ Dim strMsg As String
+
+ '' Define Rectangle
+ Dim nMineArr(1 To nRowNum, 1 To nColNum) As Integer
+
+ '' ======== Land Mine ========
+ '' Initialize, -1 Represents Land Mine
+ nMineArr(1, 1) = -1
+ nMineArr(5, 1) = -1
+
+ nMineArr(4, 4) = -1
+
+ nMineArr(1, 5) = -1
+ nMineArr(3, 5) = -1
+ nMineArr(4, 5) = -1
+ nMineArr(5, 5) = -1
+
+ nMineArr(1, 7) = -1
+ nMineArr(3, 7) = -1
+
+ nMineArr(1, 8) = -1
+
+ nMineArr(1, 9) = -1
+ nMineArr(3, 9) = -1
+
+ nMineArr(1, 10) = -1
+ nMineArr(2, 10) = -1
+ nMineArr(5, 10) = -1
+ '' ======== Land Mine ========
+
+
+ '' ======== Print Original Array ========
+ ''strMsg = PrintArr(nMineArr)
+ '' ======== Print Original Array ========
+
+ '' ======== Count ========
+ CountLandMineNearByAll nMineArr
+ '' ======== Count ========
+
+ '' ======== Print Count Array ========
+ strMsg = PrintArr(nMineArr, False)
+ '' ======== Print Count Array ========
+
+ '' ======== Output Result ========
+ MsgBox strMsg, vbOKOnly, strMyTitle
+ '' ======== Output Result ========
+
+End Sub
+
|