매회기성부분에 수식넣기

지난화일에서 매회기성의 테이블의 윤곽과 서식만 했었다
이번에는 매회기성에 수식을 자동으로 넣도록 하자
이것도 수식없이 그냥 값으로 처리하면 좋겠지만, 실무자들의 입장에서는
수식이 없으면 공연히 불안하다, 뭔가 잘못된 것은 없는지 조바심이 나게 된다
그러니 수식을 편안하게 넣어주자, 나중에 수식이 너무 많아서 로드가 심하게 걸리면
수식으로 넣었다가, 값으로 넣었다가 마음대로 VBA로 콘트롤 할수 있는 것이니
심하게 걱정할 것이 없다
아래의 그림이 수식을 넣은 결과이다
전회기성물량만 첫회의 것과 나머지가 다를뿐 수식은 모두 똑같다



금회수량만 입력하면 모두 자동계산되겠지
그런데 이런 양식을 사용할때 기성테이블자체의 변동이 없어야 모든 것이
정상적으로 잘 돌아가는데, 사용자들이 자기네들 입맛에 맞추어 열을 삽입하고
삭제하고, 하다 보면 엉망이 될 것이다
그래서 양식은 표준화라는 전제를 항상 지키는 것이 좋을 것이다
그렇지 않으면 상수를 최대한 상세하게 활용하여
특이한 변경이 있을때는 상수만 바꿔어 주던가,
별도의 컨트롤시트와 도움말을 철저히 준비하여 변경사항은 컨트롤시트에서
사용자가 쉽게 설정을 하여 실행되는 코드가 원활하게 돌아가게 하는 것이
개발자들이 하여야 할 일일 것이다

아무튼 수식을 넣는 요령은 A1타입의 주소형식보다는 RC타입의 주소형식이
편리하다..아래와 같이 간단하게 처리할수 있다

Input: iNum=기회회수, rWBS= WBS열범위, rTarget= 그려진 해당기성테이블
output: 각 해당열, 전회물량,누계물량,기성률 3개의 열에 수식이 입력된다
Sub insertFormulaEachPartialCompletionTable(iNum As Integer, rWBS As Range, rTarget As Range)
Dim sBefore As String
Dim sAccumulate As String
Dim sRate As String, rX As Range
Dim iWBSLevel As Integer
If iNum = 1 Then
    sBefore = "0"첫회의 전회기성물량은 0
Else
    sBefore = "=RC[-3]"나머지 전체 각기성테이블의 전회는 일정한 간격의 상대주소
End If
sAccumulate = "=RC[-1]+RC[-2]"누계물량도 역시 같은 상대주소
sRate = "=RC[-1]/RC5"전체도급물량에 대한 비율, 도급물량의 열은 절대주소
WBS열을 순환하면서 WBS의 최하위레벨만 실제작업물량
For Each rX In rWBS.Cells
    If modMain.WBS_MAX = getWBSLevel(rX.Value) Then
        Intersect(rX.EntireRow, rTarget.Columns(1)) = sBefore
        Intersect(rX.EntireRow, rTarget.Columns(3)) = sAccumulate
        With Intersect(rX.EntireRow, rTarget.Columns(4))
            .Value = sRate
            .NumberFormat = "0.00%"
        End With
    End If
Next
End Sub

아래화일을 다운 받아서 위의 내용을 실행해보세요..

기본BasicData시트상에서 기성물량만 입력하고 기성금액등의 계산은
별도의 보고서 시트에서 할 것인가???
아니면 기본BasicData시트상에서 기성금액열등도 아예 포함시켜 놓는 것이 실무자들에게
좋을까?? 기성회수가 많아 보았자..20회정도될 것으로 감안하면
그냥 BasicData시트에 모든 것을 계산하여 놓고 보고서는 이 내용을 그대로 옮기는 것이
좋을 것 같다..
위에서 매회기성입력양식을 좀더 확장하도록 하자
양식의 휠드명을 나타난 상수만 바꿔주면 양식은 자동확장된다



물론 수식부분은 참조하는 Offset위치가 변하므로 수정을 해주어야 할것이다
이것도 자동화를 한다고 치면 휠드명과 하나의 셋트로 참조셀의 위치를
상수화시켜놓으면 될 것이다
그렇게 만들어 보도록 하자..
학습을 위한 목적이 더 큰 것이니까!!!
실무에서 가장 좋은 방법은 이런,저런 다양한 상황의 것을 해보면서
각자의 현장의 실무적인 상황에 적용할수 있도록 하는 응용능력을
키우는 것이 중요 할 것이다

금액계산을 위한 도급금액을 참조하기 위한 열위치와
새로운 금액을 포함한 테이블의 휠드명을 상수화시키는 것이 좋은 습관 
만약 열의 위치가 변경이 일어나면 이 상수값만 바꾸면 된다
Public Const FORM_TYPE As String = "합계포함"
Public Const BASIC_DATA_COMPLETION_FLDS_WITH_AMOUNT As String = "전회수량,금회수량,누계수량,공정율,재료비,인건비,경비,합계,비고"
Public Const MTL_UNIT_PRICE_COL As Integer = 6
Public Const LAB_UNIT_PRICE_COL As Integer = 8
Public Const EXP_UNIT_PRICE_COL As Integer = 10
Public Const SUM_UNIT_PRICE_COL As Integer = 12


매회기성계산용테이블을 아래와 같이 수정하자

Sub createPartialComTable(rStartCell As Range, iNum As Integer)
Dim arrX As Variant
Dim rWBSCol As Range
On Error Resume Next

금액을 포함한 경우와 물량만 처리하는 경우의 두개의
테이블을 선택적으로 하게 하여도 좋을 것이다

If modMain.FORM_TYPE = "합계포함" Then
	arrX = Split(modMain.BASIC_DATA_COMPLETION_FLDS_WITH_AMOUNT, ",")
Else
	arrX = Split(modMain.BASIC_DATA_COMPLETION_FLDS, ",")
End If
rStartCell = iNum & modMain.BASIC_DATA_COMPLETION_LABEL
Set rWBSCol = modMain.getWBSColumn
With rStartCell.Offset(1).Resize(, UBound(arrX) + 1)
    .Value = arrX
    .Cells(1).Offset(, -2).Copy
    .PasteSpecial xlPasteFormats
    With .Offset(1).Resize(rWBSCol.Rows.Count)
        .Borders(xlInsideHorizontal).Weight = xlHairline
        .Borders(xlInsideVertical).Weight = xlHairline
        .Borders(xlEdgeRight).Weight = xlMedium
        .Borders(xlEdgeBottom).Weight = xlMedium
        paintBackGround .Cells
        insertFormulaEachPartialCompletionTable iNum, rWBSCol, .Cells
    End With
    .Offset(-1).Resize(2).Borders(xlEdgeRight).Weight = xlMedium
    paintBackGround .Offset(-1)
End With
End Sub

수식을 입력하는 프로시져

Sub insertFormulaEachPartialCompletionTable(iNum As Integer, rWBS As Range, rTarget As Range)
Dim sBefore As String
Dim sAccumulate As String
Dim sRate As String, rX As Range
Dim iWBSLevel As Integer
If iNum = 1 Then
    sBefore = "0"
Else
    sBefore = "=RC[-" & rTarget.Rows(1).Cells.Count - 2 & "]"
End If
sAccumulate = "=RC[-1]+RC[-2]"
sRate = "=RC[-1]/RC5"
With rTarget
For Each rX In rWBS.Cells
    If modMain.WBS_MAX = getWBSLevel(rX.Value) Then
        Intersect(rX.EntireRow, .Columns(1)) = sBefore
        Intersect(rX.EntireRow, .Columns(3)) = sAccumulate
        With Intersect(rX.EntireRow, .Columns(4))
            .Value = sRate
            .NumberFormat = "0.00%"
        End With

금액을 포함하는 경우는 아래부분을 추가하면 된다
역시 참조주소형식은 A1타입을 사용하지 말고 R1C1타입으로 하는 것이 간결하다

        If modMain.FORM_TYPE = "합계포함" Then
              Intersect(rX.EntireRow, .Columns(5)) = "=RC" & modMain.MTL_UNIT_PRICE_COL & "*RC[-3]"
              Intersect(rX.EntireRow, .Columns(6)) = "=RC" & modMain.LAB_UNIT_PRICE_COL & "*RC[-4]"
              Intersect(rX.EntireRow, .Columns(7)) = "=RC" & modMain.EXP_UNIT_PRICE_COL & "*RC[-5]"
              Intersect(rX.EntireRow, .Columns(8)) = "=SUM(RC[-3]:RC[-1])"
        End If
    End If
Next
End With
End Sub

위의 기능과 더불어 이번 화일에서 추가된 기능은 아래 그림과 같다

• 01
• 02
• 03
• 04
• 05
• 06
• 07