From 889c2e03812848cfd50ac9fd9d8e80fea64d883c Mon Sep 17 00:00:00 2001 From: Mohammad S Anwar Date: Wed, 14 Jul 2021 13:09:08 +0100 Subject: - Added Excel VBA solutions by Eric Cheung. --- .../eric-cheung/excel-vba/Challenge_121.xlsm | Bin 0 -> 47113 bytes challenge-121/eric-cheung/excel-vba/ch-1.bas | 40 +++++ challenge-121/eric-cheung/excel-vba/ch-2.bas | 170 +++++++++++++++++++++ 3 files changed, 210 insertions(+) create mode 100644 challenge-121/eric-cheung/excel-vba/Challenge_121.xlsm create mode 100644 challenge-121/eric-cheung/excel-vba/ch-1.bas create mode 100644 challenge-121/eric-cheung/excel-vba/ch-2.bas diff --git a/challenge-121/eric-cheung/excel-vba/Challenge_121.xlsm b/challenge-121/eric-cheung/excel-vba/Challenge_121.xlsm new file mode 100644 index 0000000000..2027c729d9 Binary files /dev/null and b/challenge-121/eric-cheung/excel-vba/Challenge_121.xlsm differ diff --git a/challenge-121/eric-cheung/excel-vba/ch-1.bas b/challenge-121/eric-cheung/excel-vba/ch-1.bas new file mode 100644 index 0000000000..b10777aa44 --- /dev/null +++ b/challenge-121/eric-cheung/excel-vba/ch-1.bas @@ -0,0 +1,40 @@ +Attribute VB_Name = "ModTask_01" +Option Explicit +Public Const strMyTitle As String = "Eric Cheung" + +Function InvertBit(nNum, nPos) As Integer + + Dim strBin As String, strNuBin As String + + strBin = Format(Application.WorksheetFunction.Dec2Bin(nNum), "00000000") + + If nPos < 8 Then + strNuBin = Left(strBin, 8 - nPos) + End If + + strNuBin = strNuBin & Format(1 - Val(Left(Right(strBin, nPos), 1)), "0") + + If nPos > 1 Then + strNuBin = strNuBin & Right(strBin, nPos - 1) + End If + + InvertBit = Application.WorksheetFunction.Bin2Dec(strNuBin) + +End Function + +Sub Task_01() + + Dim varInputNum, varPosNum + + Do + varInputNum = InputBox("Please enter a number between 1 to 255", strMyTitle, 12) + If varInputNum = "" Then Exit Sub + + varPosNum = InputBox("Please enter a number between 1 to 8", strMyTitle, 3) + If varPosNum = "" Then Exit Sub + + Loop Until varInputNum > 0 And varInputNum < 256 And varPosNum > 0 And varPosNum < 9 + + MsgBox "The answer for inverting " & varPosNum & "th bit from the end of " & varInputNum & " is: " & InvertBit(varInputNum, varPosNum) + +End Sub diff --git a/challenge-121/eric-cheung/excel-vba/ch-2.bas b/challenge-121/eric-cheung/excel-vba/ch-2.bas new file mode 100644 index 0000000000..1a3afb2b08 --- /dev/null +++ b/challenge-121/eric-cheung/excel-vba/ch-2.bas @@ -0,0 +1,170 @@ +Attribute VB_Name = "ModTask_02" +Option Explicit + +Function GetMatrixPath(RngFill As Range, ByRef nMinCost As Integer) + + Dim nMatrixSize As Integer, nCntLoop As Integer + Dim nSubLoop As Integer, nCheckLoop As Integer + Dim nArrCnt() As Integer, nArrPath() As Integer + Dim bArrTempUsed() As Boolean, bArrCheck() As Boolean + Dim nCost As Integer + Dim bPathValid As Boolean, bFirstPath As Boolean + Dim nLastPos As Integer + + nMatrixSize = RngFill.Rows.Count + + ReDim nArrCnt(1 To nMatrixSize) + ReDim nArrPath(1 To nMatrixSize) + ReDim bArrTempUsed(1 To nMatrixSize) + ReDim bArrCheck(1 To nMatrixSize) + + 'Initialize + nArrCnt(1) = 1 + + bFirstPath = True + nMinCost = 0 + + '''Loop + nCntLoop = 1 + Do + Do While nArrCnt(nCntLoop) <= nMatrixSize + Do While nCntLoop < nMatrixSize + nCntLoop = nCntLoop + 1 + nArrCnt(nCntLoop) = 1 + Loop + + '' ===== Check Path Is Valid ===== + bPathValid = True + For nSubLoop = 1 To nMatrixSize + bArrTempUsed(nSubLoop) = False + Next nSubLoop + + For nSubLoop = 1 To nMatrixSize + If bArrTempUsed(nArrCnt(nSubLoop)) Then + bPathValid = False + Exit For + Else + bArrTempUsed(nArrCnt(nSubLoop)) = True + End If + Next nSubLoop + '' ===== Check Path Is Valid ===== + + If bPathValid Then + nCost = 0 + nLastPos = nArrCnt(1) + For nSubLoop = 1 To nMatrixSize + If nSubLoop = nMatrixSize Then + nCost = nCost + RngFill(nLastPos, nArrCnt(1)) + Else + nCost = nCost + RngFill(nLastPos, nArrCnt(nSubLoop + 1)) + nLastPos = nArrCnt(nSubLoop + 1) + End If + Next nSubLoop + + If bFirstPath Or nCost < nMinCost Then + For nSubLoop = 1 To nMatrixSize + nArrPath(nSubLoop) = nArrCnt(nSubLoop) + Next nSubLoop + nMinCost = nCost + bFirstPath = False + End If + End If + + nArrCnt(nCntLoop) = nArrCnt(nCntLoop) + 1 + Loop + + nCntLoop = nCntLoop - 1 + nArrCnt(nCntLoop) = nArrCnt(nCntLoop) + 1 + + Loop Until nArrCnt(1) > nMatrixSize + + GetMatrixPath = nArrPath + +End Function + +Private Sub Task_02_Func(rngToCheck As Range) + + Dim arrResult + Dim nPathCost As Integer, nLoop As Integer + Dim strTourPath As String + + If rngToCheck.Rows.Count <> rngToCheck.Columns.Count Then + Exit Sub + End If + + arrResult = GetMatrixPath(rngToCheck, nPathCost) + + For nLoop = LBound(arrResult) To UBound(arrResult) + If strTourPath <> "" Then + strTourPath = strTourPath & ", " + End If + strTourPath = strTourPath & Format(arrResult(nLoop) - 1, "0") + Next nLoop + + strTourPath = strTourPath & ", " & Format(arrResult(1) - 1, "0") + strTourPath = "Min Cost: " & nPathCost & ", " & "Tour: " & strTourPath + + MsgBox strTourPath + +End Sub + +Private Sub Task_02_GenTable(nNum) + + Dim nLoop_01 As Integer, nLoop_02 As Integer + Dim rngPos As Range + + Set rngPos = Range("B9") + + For nLoop_01 = 1 To nNum + With rngPos.Offset(nLoop_01, 0) + .Value = nLoop_01 - 1 + .HorizontalAlignment = xlCenter + End With + + With rngPos.Offset(0, nLoop_01) + .Value = nLoop_01 - 1 + .HorizontalAlignment = xlCenter + End With + + For nLoop_02 = 1 To nNum + With rngPos.Offset(nLoop_01, nLoop_02) + .Value = Int((8 - 1 + 1) * Rnd + 1) + .HorizontalAlignment = xlCenter + End With + Next nLoop_02 + Next nLoop_01 + + Task_02_Func Range(rngPos.Offset(1, 1), rngPos.Offset(nNum, nNum)) + +End Sub + +Sub Task_02() + Task_02_Func Range("Rng_Task02") +End Sub + +Sub Task_02_Bonus_1() + + Dim varInputNum + + Do + varInputNum = InputBox("Please enter a number", strMyTitle, 5) + If varInputNum = "" Then Exit Sub + Loop Until varInputNum > 1 + + Task_02_GenTable varInputNum + +End Sub + +Sub Task_02_Bonus_2() + + Dim varInputNum + + Do + varInputNum = InputBox("Please enter either 12 or 15", strMyTitle, 12) + If varInputNum = "" Then Exit Sub + Loop Until varInputNum = 12 Or varInputNum = 15 + + Task_02_GenTable varInputNum + +End Sub + -- cgit