' ¡Ò˹´Ê¶Ò¹Ð¢Í§ TextBox ÇèÒãËéãªé§Ò¹ä´éËÃ×ÍäÁè
Sub textStatus(textName, textOwner, textCost, textPeriod, textCostOfCapital, textCashFlow)
If textName Then InvestmentForm.txtName.Enabled = True _
Else InvestmentForm.txtName.Enabled = False
If textOwner Then InvestmentForm.txtOwner.Enabled = True _
Else InvestmentForm.txtOwner.Enabled = False
If textCost Then InvestmentForm.txtCost.Enabled = True _
Else InvestmentForm.txtCost.Enabled = False
If textPeriod Then InvestmentForm.txtPeriod.Enabled = True _
Else InvestmentForm.txtPeriod.Enabled = False
If textCostOfCapital Then InvestmentForm.txtCostOfCapital.Enabled = True _
Else InvestmentForm.txtCostOfCapital.Enabled = False

If textCashFlow Then InvestmentForm.txtCashFlow.Enabled = True _
Else InvestmentForm.txtCashFlow.Enabled = False
End Sub
' ¡Ò˹´ÊÕ¾×é¹ËÅѧ¢Í§ TextBox
Sub textBGStatus(textName, textOwner, textCost, textPeriod, textCostOfCapital, textCashFlow)
If textName Then InvestmentForm.txtName.BackColor = &H80000005 _
Else InvestmentForm.txtName.BackColor = &H80000018
If textOwner Then InvestmentForm.txtOwner.BackColor = &H80000005 _
Else InvestmentForm.txtOwner.BackColor = &H80000018
If textCost Then InvestmentForm.txtCost.BackColor = &H80000005 _
Else InvestmentForm.txtCost.BackColor = &H80000018
If textPeriod Then InvestmentForm.txtPeriod.BackColor = &H80000005 _
Else InvestmentForm.txtPeriod.BackColor = &H80000018
If textCostOfCapital Then InvestmentForm.txtCostOfCapital.BackColor = &H80000005 _
Else InvestmentForm.txtCostOfCapital.BackColor = &H80000018
If textCashFlow Then InvestmentForm.txtCashFlow.BackColor = &H80000005 _
Else InvestmentForm.txtCashFlow.BackColor = &H80000018
End Sub
' à¤ÅÕÂÃì¢éͤÇÒÁÀÒÂã¹ TextBox
Sub textValue(textName, textOwner, textCost, textPeriod, textCostOfCapital, textCashFlow)
If textName Then InvestmentForm.txtName.Value = Clear
If textOwner Then InvestmentForm.txtOwner.Value = Clear
If textCost Then InvestmentForm.txtCost.Value = Clear
If textPeriod Then InvestmentForm.txtPeriod.Value = Clear
If textCostOfCapital Then InvestmentForm.txtCostOfCapital.Value = Clear
If textCashFlow Then InvestmentForm.txtCashFlow.Value = Clear
End Sub
' ¡Ò˹´Ê¶Ò¹Ð¢Í§»ØèÁÇèÒãËéãªé§Ò¹ä´éËÃ×ÍäÁè
Sub buttonStatus(saveButton, nextButton, previousButton, addNewButton, editButton, analyzeButton, exitButton)
If saveButton Then InvestmentForm.cmdSaveProject.Locked = False _
Else InvestmentForm.cmdSaveProject.Locked = True
If nextButton Then InvestmentForm.cmdNextYear.Locked = False _
Else InvestmentForm.cmdNextYear.Locked = True
If previousButton Then InvestmentForm.cmdPreviousYear.Locked = False _
Else InvestmentForm.cmdPreviousYear.Locked = True
If addNewButton Then InvestmentForm.cmdAddNewProject.Locked = False _
Else InvestmentForm.cmdAddNewProject.Locked = True
If editButton Then InvestmentForm.cmdEditData.Locked = False _
Else InvestmentForm.cmdEditData.Locked = True
If analyzeButton Then InvestmentForm.cmdAnalyzeAll.Locked = False _
Else InvestmentForm.cmdAnalyzeAll.Locked = True
If exitButton Then InvestmentForm.cmdExit.Locked = False _
Else InvestmentForm.cmdExit.Locked = True
End Sub
' ¡Ò˹´Ê¶Ò¹Ð¢éͤÇÒÁ·Õèà»ç¹¤Ò͸ԺÒ»ØèÁµèÒ§ æ
Sub labelButtonStatus(saveLabel, nextLabel, previousLabel, addNewLabel, editLAbel, analyzeLabel, exitLabel)
If saveLabel Then InvestmentForm.lblSaveProject.Enabled = True _
Else InvestmentForm.lblSaveProject.Enabled = False
If nextLabel Then InvestmentForm.lblNext.Enabled = True _
Else InvestmentForm.lblNext.Enabled = False
If previousLabel Then InvestmentForm.lblPrevious.Enabled = True _
Else InvestmentForm.lblPrevious.Enabled = False
If addNewLabel Then InvestmentForm.lblCreateProject.Enabled = True _
Else InvestmentForm.lblCreateProject.Enabled = False
If editLAbel Then InvestmentForm.lblEdit.Enabled = True _
Else InvestmentForm.lblEdit.Enabled = False
If analyzeLabel Then InvestmentForm.lblAnalyze.Enabled = True _
Else InvestmentForm.lblAnalyze.Enabled = False
If exitLabel Then InvestmentForm.lblExit.Enabled = True _
Else InvestmentForm.lblExit.Enabled = False
End Sub
Sub checkNameString(KeyAscii)
If KeyAscii = 42 Or KeyAscii = 47 Or KeyAscii = 58 Or KeyAscii = 63 Or KeyAscii = 91 Or KeyAscii = 92 Or KeyAscii = 93 Then KeyAscii = 0
End Sub
Sub checkNumeric(KeyAscii, Optional cash)
If KeyAscii < 48 Or KeyAscii > 57 Then
If cash = "No" Then
KeyAscii = 0
Else
If KeyAscii = 45 Then KeyAscii = 45 Else KeyAscii = 0
End If
End If
End Sub
Function numericFormat(dataValue)
On Error Resume Next
numericFormat = FormatNumber(dataValue, 2, , , vbTrue)
If Err.Number = "13" Then Call errorMessage("¢éÍÁÙżԴ¾ÅÒ´", "vbCritical", "¼Ô´¾ÅÒ´")
End Function
Function errorMessage(errData, vbIcon, msgTitle)
result = MsgBox(errData, vbIcon, msgTitle)
errorMessage = result
End Function
Function createProject(proName, proOwner, proCost, proPeriod, proCostOfCapital, OptionalworkingStatus)
 Dim newSheet As Worksheet
 For Each newSheet In Worksheets
 If newSheet.Name = proName Or proName = "ProjAnalysis" Then
 If ActiveSheet.Name = proName And workingStatus = "edit" Then Exit For
 Call errorMessage("?????????????????????????????", vbCritical, "???????")
 InvestmentForm.txtName.SetFocus
 projectName = "No"
 editProjectName = "No"
 Call buttonStatus(1, 0, 0, 0, 0, 0, 1)
 Call labelButtonStatus(1, 0, 0, 0, 0, 0, 1)
 Exit For
 Else
 projectName = "Yes"
 End If
 Next newSheet
 If workingStatus = "edit" And editProjectName <> "No" Then
 projectName = "Edit"
 ActiveSheet.Name = proName
 Call addDataToSheet(proName, proOwner, proCost, proPeriod, proCostOfCapital)
 Call textStatus(0, 0, 0, 0, 0, 1)
 Call textBGStatus(0, 0, 0, 0, 0, 1)
 Call buttonStatus(0, 1, 0, 0, 1, 0, 1)
 Call labelButtonStatus(0, 1, 0, 0, 1, 0, 1)
 InvestmentForm.txtCashFlow.SetFocus
 End If
 If workingStatus <> "edit" And projectName = "Yes" Then
 Application.Worksheets.Add
 On Error Resume Next
 ActiveSheet.Name = proName
 ActiveSheet.Cells.Select
 Selection.FormulaHidden = True
 ActiveSheet.Protect Password:="204360", UserInterfaceOnly:=True
 Call addDataToSheet(proName, proOwner, proCost, proPeriod, proCostOfCapital)
 Call textStatus(0, 0, 0, 0, 0, 1)
 Call textBGStatus(0, 0, 0, 0, 0, 1)
 Call buttonStatus(0, 1, 0, 0, 1, 0, 1)
 Call labelButtonStatus(0, 1, 0, 0, 1, 0, 1)
 InvestmentForm.txtCashFlow.SetFocus
 End If
 createProject = projectName
End Function
Sub addDataToSheet(proName, proOwner, proCost, proPeriod, proCostOfCapital)
 With ActiveSheet
 .Cells(1, "A").Value = "Project Name"
 .Cells(2, "A").Value = "Project Owner"
 .Cells(3, "A").Value = "Opportunity Cost"
 .Cells(6, "A").Value = "Year"
 .Cells(6, "B").Value = "Cash Flow"
 .Cells(6, "C").Value = "Accumulate CF"
 .Cells(6, "D").Value = "Discounted CF"
 .Cells(6, "E").Value = "Accumulate DCF"
 End With
 Columns("A:E").EntireColumn.AutoFit
 Rows(6).HorizontalAlignment = xlCenter
 Call addFillColorToCell(proName, proOwner, proCostOfCapital)
 Call addCashFlow(proName, proOwner, proCost, proPeriod, proCostOfCapital)
End Sub
Sub addFillColorToCell(proName, proOwner, proCostOfCapital)
 Range("C1:E1").Select
 Selection.Merge
 Selection.Interior.ColorIndex = 36
 Selection.Value = proName
 Range("C2:E2").Select
 Selection.Merge
 Selection.Interior.ColorIndex = 40
 Selection.Value = proOwner
 Range("C3").Select
 Selection.Interior.ColorIndex = 34
 Selection.Value = proCostOfCapital & " %"
 For i = 1 To 4
 Range("B6").Select
 ActiveCell(1, i).Interior.ColorIndex = 32 + i
 Next i
End Sub
Sub addCashFlow(proName, proOwner, proCost, proPeriod, proCostOfCapital, Optional proCash)
 num = InvestmentForm.lblNumOfUnit.Caption - 1
 Cells(num + 7, "A").Select
 ActiveCell.Value = InvestmentForm.lblNumOfUnit.Caption - 1
 ActiveCell.HorizontalAlignment = xlCenter
 If num = 0 Then
 Cells(num + 7, "B").Value = proCost * (-1)
 Cells(num + 7, "C").Value = "=RC[-1]"
 Cells(num + 7, "D").Value = "=RC[-2]/(1+R3C3)^RC[-3]"
 Cells(num + 7, "E").Value = "=RC[-1]"
 Else
 Cells(num + 7, "B").Value = proCash
 Cells(num + 7, "C").Value = "=RC[-1]+R[-1]C"
 Cells(num + 7, "D").Value = "=RC[-2]/(1+R3C3)^RC[-3]"
 Cells(num + 7, "E").Value = "=RC[-1]+R[-1]C"
 End If
 beginCell = Cells(num + 7, "B").Address
 lastCell = Cells(num + 7, "E").Address
 Range(beginCell, lastCell).Select
 Selection.NumberFormat = "#,##0.00"
End Sub
Sub previousCashFlow(Optional workingStatus)
 atYear = InvestmentForm.lblNumOfUnit.Caption
 InvestmentForm.txtCashFlow.Value = Cells(atYear + 7, "B").Value
End Sub
' ????????????????????? ???????? PP, Discounted PP, NPI, PI ??? IRR
Sub generateResult()
 num = InvestmentForm.lblNumOfUnit.Caption
 atRow = num + 8
 totalRow = Cells(65536, 1).End(xlUp).Row
 For numRow = atRow To totalRow
 Rows(numRow).Select
 Selection.ClearContents
 Selection.Clear
 Next numRow
 Cells(num + 10, "A").Value = "PP"
 Call countYearPP(num)
 Cells(num + 10, "C").Value = "Year"
 Cells(num + 11, "A").Value = "Discounted PP"
 Call countYearDPP(num)
 Cells(num + 11, "C").Value = "Year"
 Cells(num + 12, "A").Value = "NPV"
 Cells(num + 12, "B").Value = "=SUM(R7C4:R[-5]C4)"
 Cells(num + 12, "C").Value = "Baht"
 Cells(num + 13, "A").Value = "PI"
 Cells(num + 13, "B").Value = "=R[-1]C/(-1*R7C4)+1"
 Cells(num + 13, "B").NumberFormat = "#,##0.00"
 Cells(num + 13, "C").Value = "Baht"
 Cells(num + 14, "A").Value = "IRR"
 Cells(num + 14, "B").Value = "=IRR(R7C2:R[-7]C)"
 Cells(num + 14, "B").NumberFormat = "0.00%"
 For i = 1 To 5
 Cells(num + 10, "A").Select
 ActiveCell(i, 1).Interior.ColorIndex = 32 + i
 Next i
 On Error Resume Next
 If TypeName(Cells(num + 14, "B").Value) <> "Double" Then Cells(num + 14, "B").Value = 0
 InvestmentForm.lblNumOfUnit.Caption = 1
End Sub
' ? ???????????????????? ??????????????????????????????????????
Sub countYearPP(num)
 For i = 0 To num
 Cells(7 + i, "C").Select
 Accumulate = ActiveCell.Value
 If Sgn(Accumulate) = 1 Then Exit For
 Next i
 ActiveCell.Value = "=R[-1]C[-2]+(Abs(R[-1]C)/RC[-1])"
 totalYear = ActiveCell.Text
 Cells(num + 10, "B").Value = totalYear
 ActiveCell.Value = "=RC[-1]+R[-1]C"
End Sub
' ? ???????????????????? ?????????????????????????????????
Sub countYearDPP(num)
 For i = 0 To num
 Cells(7 + i, "E").Select
 Accumulate = ActiveCell.Value
 If Sgn(Accumulate) = 1 Then Exit For
 Next i
 ActiveCell.Value = "=R[-1]C[-4]+(Abs(R[-1]C)/RC[-1])"
 totalYear = ActiveCell.Text
 Cells(num + 11, "B").Value = totalYear
 ActiveCell.Value = "=RC[-1]+R[-1]C"
End Sub
' ???????????? ???????? “????????????????” ?????????????????????????
Sub createProjectAnalysis(proName)
 Dim analysisSheet As Worksheet
 For Each analysisSheet In Worksheets
 If analysisSheet.Name = "ProjAnalysis" Then
 projectAnalysis = "Yes"
 Exit For
 End If
 Next analysisSheet
 If projectAnalysis <> "Yes" Then
 Application.Worksheets.Add
 On Error Resume Next
 ActiveSheet.Name = "ProjAnalysis"
 ActiveSheet.Protect Password:="204360", UserInterfaceOnly:=True
 Rows(2).HorizontalAlignment = xlCenter
 Cells(1, "A").Value = "ProjAnalysis"
 Cells(1, "A").Interior.ColorIndex = 44
 Cells(2, "A").Value = "Project Name"
 Cells(2, "B").Value = "Investment"
 Cells(2, "C").Value = "WACC"
 Cells(2, "D").Value = "YEAR"
 Cells(2, "E").Value = "PP"
 Cells(2, "F").Value = "DIscounted PP"
 Cells(2, "G").Value = "NPV"
 Cells(2, "H").Value = "PI"
 Cells(2, "I").Value = "IRR"
 Range("A2:I2").Interior.ColorIndex = 34
 Range("A2:I2").Select
 Call cellBorder("VBorder")
 End If
 Worksheets(proName).Select
End Sub
' ??????????????????????
Sub cellBorder(Optional styleBorderV As String, Optional styleBorderH As String)
 With Selection.Borders(xlEdgeLeft)
 .LineStyle = xlContinuous
 .Weight = xlThin
 .ColorIndex = xlAutomatic
 End With
 With Selection.Borders(xlEdgeTop)
 .LineStyle = xlContinuous
 .Weight = xlThin
 .ColorIndex = xlAutomatic
 End With
 With Selection.Borders(xlEdgeBottom)
 .LineStyle = xlContinuous
 .Weight = xlThin
 .ColorIndex = xlAutomatic
 End With
 With Selection.Borders(xlEdgeRight)
 .LineStyle = xlContinuous
 .Weight = xlThin
 .ColorIndex = xlAutomatic
 End With
 If styleBorderV = "VBorder" Then
 With Selection.Borders(xlInsideVertical)
 .LineStyle = xlContinuous
 .Weight = xlThin
 .ColorIndex = xlAutomatic
 End With
 End If
 If styleBorderH = "HBorder" Then
 With Selection.Borders(xlInsideHorizontal)
 .LineStyle = xlContinuous
 .Weight = xlThin
 .ColorIndex = xlAutomatic
 End With
 End If
End Sub
Sub InvestmentAnalysis(proName, proPeriod)
 Application.Worksheets("ProjAnalysis").Select
 With Worksheets("ProjAnalysis")
 startRow = .Cells(1, 1).End(xlDown).Row
 totalRow = .Cells(65536, 1).End(xlUp).Row
 For numRow = startRow + 1 To totalRow
 .Rows(numRow).Select
 Selection.ClearContents
 Selection.Clear
 Next numRow
 End With
 Call addDataProjectAnalysis(proName, proPeriod)
 nextRow = Worksheets("ProjAnalysis").Cells(65536, 1).End(xlUp).Row + 3
 Worksheets("ProjAnalysis").Select
 Cells(nextRow, "A").Value = "Project Analysis From"
 Cells(nextRow, "A").Interior.ColorIndex = 44
 Call investmentTable(nextRow + 1, "PP", 34, "Minimum is", "=MIN(R3C:R[-4]C)", "E", -3)
 Call investmentTable(nextRow + 2, "Discount PP", 34, "Minimum is", "=MIN(R3C[1]:R[-5]C[1])", "F", -4)
 Call investmentTable(nextRow + 3, "NPV", 34, "Maximum is", "=MAX(R3C[2]:R[-6]C[2])", "G", -5)
 Call investmentTable(nextRow + 4, "PI", 34, "Maximum is", "=MAX(R3C[3]:R[-7]C[3])", "H", -6)
 Call investmentTable(nextRow + 5, "IRR", 34, "Maximum is", "=MAX(R3C[4]:R[-8]C[4])", "I", -7)
 beginCell = Cells(nextRow + 1, "A").Address
 lastCell = Cells(nextRow + 5, "E").Address
 Range(beginCell, lastCell).Select
 Call cellBorder("VBorder", "HBorder")
 beginCell = Cells(nextRow + 1, "B").Address
 lastCell = Cells(nextRow + 5, "D").Address
 Range(beginCell, lastCell).Select
 Selection.HorizontalAlignment = xlCenter
 Call decisionTable
 Worksheets(proName).Select
End Sub
' ????????????????????????? “???????????????????????” ???????????????????????????
Sub addDataProjectAnalysis(proName, proPeriod)
 nextRow = Worksheets("ProjAnalysis").Cells(65536, 1).End(xlUp).Row + 1
 With Worksheets("ProjAnalysis")
 .Cells(nextRow, "A").Value = Worksheets(proName).Range("C1").Text
 .Cells(nextRow, "B").Value = Abs(Worksheets(proName).Range("B7").Text)
 .Cells(nextRow, "B").NumberFormat = "#,##0.00"
 .Cells(nextRow, "C").Value = Worksheets(proName).Range("C3").Text
 .Cells(nextRow, "D").Value = proPeriod
 totalRow = Worksheets(proName).Cells(65536, 1).End(xlUp).Row
 .Cells(nextRow, "E").Value = Worksheets(proName).Cells(totalRow - 4, "B").Text
 .Cells(nextRow, "F").Value = Worksheets(proName).Cells(totalRow - 3, "B").Text
 .Cells(nextRow, "G").Value = Worksheets(proName).Cells(totalRow - 2, "B").Text
 .Cells(nextRow, "H").Value = Worksheets(proName).Cells(totalRow - 1, "B").Text
 .Cells(nextRow, "I").Value = Worksheets(proName).Cells(totalRow, "B").Text
 beginCell = Cells(nextRow, "A").Address
 lastCell = Cells(nextRow, "I").Address
 Range(beginCell, lastCell).Select
 Call cellBorder("VBorder")
 End With
End Sub
' ????????????????????? 2 ??? "????????????????????"
Sub investmentTable(insertToRow, valueA, colorA, valueB, valueE, compareColumn, disCompare)
 Cells(insertToRow, "A").Value = valueA
 Cells(insertToRow, "A").Interior.ColorIndex = colorA
 Cells(insertToRow, "B").Value = valueB
 Cells(insertToRow, "D").Value = "="
 Cells(insertToRow, "E").Value = valueE
 Call searchCell(insertToRow, compareColumn, disCompare)
End Sub
' ??????????????????????????????????????????????????????
Sub searchCell(nextRow, compareColumn, disCell)
 selectColumn = "E"
 valueColumn = "C"
 Dim countProject As Integer
 firstTable = Worksheets("ProjAnalysis").Cells(1, 1).End(xlDown).Row
 For i = 3 To firstTable
 If Cells(nextRow, selectColumn).Value = Cells(i, compareColumn).Value Then
 Count = Count + 1
 Cells(i, compareColumn).Select
 If Count = 1 Then
 Cells(nextRow, valueColumn).Value = ActiveCell(1, disCell).Value
 Else
 Cells(nextRow, valueColumn).Value = Cells(nextRow, valueColumn).Value & ", " & ActiveCell(1, disCell).Value
 End If
 End If
 Next i
End Sub
' ???????????????????????3 ??? “?????????????”
Sub decisionTable()
 Worksheets("ProjAnalysis").Select
 nextRow = Worksheets("ProjAnalysis").Cells(65536, 1).End(xlUp).Row + 3
 Cells(nextRow, "A").Value = "Decision Table"
 Cells(nextRow, "A").Interior.ColorIndex = 44
 Cells(nextRow + 1, "A").Value = "Project Name"
 Cells(nextRow + 1, "A").Interior.ColorIndex = 34
 Cells(nextRow + 1, "B").Value = "PP"
 Cells(nextRow + 1, "B").Interior.ColorIndex = 34
 Cells(nextRow + 1, "C").Value = "Discounted PP"
 Cells(nextRow + 1, "C").Interior.ColorIndex = 34
 Cells(nextRow + 1, "D").Value = "NPV"
 Cells(nextRow + 1, "D").Interior.ColorIndex = 34
 Cells(nextRow + 1, "E").Value = "PI"
 Cells(nextRow + 1, "E").Interior.ColorIndex = 34
 Cells(nextRow + 1, "F").Value = "IRR"
 Cells(nextRow + 1, "F").Interior.ColorIndex = 34
 Cells(nextRow + 1, "G").Value = "Index Key"
 Cells(nextRow + 1, "G").Interior.ColorIndex = 34
 Rows(nextRow + 1).HorizontalAlignment = xlCenter
 totalProject = Worksheets("ProjAnalysis").Cells(1, 1).End(xlDown).Row
 nextRow = Worksheets("ProjAnalysis").Cells(65536, 1).End(xlUp).Row
 j = 1
 For i = 3 To totalProject
 Cells(nextRow + j, "A").Value = Cells(i, "A").Value
 Cells(nextRow + j, "G").Value = "=COUNTA(RC[-5]:RC[-1])"
 Cells(nextRow + j, "G").HorizontalAlignment = xlCenter
 j = j + 1
 Next i
 beginCell = Cells(nextRow, "A").Address
 lastCell = Cells(nextRow + totalProject - 2, "G").Address
 Range(beginCell, lastCell).Select
 Call cellBorder("VBorder", "HBorder")
 Call decisionValue(totalProject - 2, 5, totalProject + 2, 2)
 Call decisionValue(totalProject - 2, 6, totalProject + 3, 3)
 Call decisionValue(totalProject - 2, 7, totalProject + 4, 4)
 Call decisionValue(totalProject - 2, 8, totalProject + 5, 5)
 Call decisionValue(totalProject - 2, 9, totalProject + 6, 6)
 Columns("A:I").EntireColumn.AutoFit
 ActiveWindow.DisplayGridlines = False
End Sub
' ?????????????????????????????3 ??????????????? ??????????????????? ???????
Sub decisionValue(totalProject, C, R, insertCol)
 With ActiveSheet
 Range("A3").Select
 For i = 1 To totalProject
 If ActiveCell(i, C).Value = ActiveCell(R, 5).Value Then
 ActiveCell(totalProject + 12 + i, insertCol).Value = "P"
 ActiveCell(totalProject + 12 + i, insertCol).Font.Name = "Wingdings 2"
 ActiveCell(totalProject + 12 + i, insertCol).HorizontalAlignment = xlCenter
 End If
 Next i
 .Cells.Select
 Selection.FormulaHidden = True
 .Cells(1, 1).Select
 End With
End Sub