문제와 개요
프로젝트관리부분에서 설비담당자의 질문을 하나의 화일로는 처리 불가한 것이라
몇장의 페이지로 풀어 나가 보도록 한다
매달 발생하는 공사의 기성에 대한 작업을 하는 것은
엑셀이 없으면 삽질을 하여야 한다
어느 정도 사용하여도 결국은 복사, 붙여넣기, 복사,붙여 넣기의 연속이다
프로젝트하나 마치면, 통합문서의 이름이 복사하면서 옮겨지고
그러면서 쓰레기 통이 되고, 종국에는 통합문서가 걸레가 되어 버린다
계정하나 잘못되면 또, 눈을 까뒤집고 찾아서 처리하여야 하는 등
프로젝트관리중에서 중요한 부분이다
발주처로 부터 돈을 받아야 공사를 진행을 계속하여 나가는 것이니
아주 중요할 것이다
또한 매달 발생하는 것을 전체의 흐름으로도 보고 있어야 할 것이다
기성의 개념은 같은 것이니, 큰 프로젝트이던, 작은 프로젝트이던
응용하여 각자의 부서에서 개선해 보시기 바란다
또한 진행하면서 질문하신분의 입맛에 맞는 쪽으로 만들어 간다
그래서 수시로 수정,보완,추가를 해나갈 것이다
ERP등 큰 데이타베이스를 돌리고 프로젝트의 각담당자별로
필요한 조건을 주고 버튼을 크릭하면 만들어지는 그런 전체회사단위의
자동화가 된다면 좋겠지만, 대개의 단위현장에서는
아마도 먼 훗날 이야기인 것 같다
또한 기성신청에 대한 정도의 높고 낮음을 현장감각에 따라서
적절히 조정하여야 하는 상황에서는 또한 큰 시스템은 오히려 걸리적거림이다
역시 엑셀로 단위현장별로 적절히 자동화하는 것이 현재의 답일 것이다
진행속도는 질문하신분의 피드백이 적절하면 빨리 진행되고
그렇지 않으면 조금 버벅거릴수도 있다
질문하신분이 하고자 하는 의도
프로젝트의 시작에서 종료까지 하나의 Master화일을 유지한다
이 화일에는 프로젝트의 총집계부분이 누적되어 가고
이곳에서 필요한 명령을 하면 몇회기성 통합문서가 만들어지게
한다고 보면 된다
각회별기성통합문서에 만들어지는 시트는 모두 6장
1)도급기성검토의견서
2)기계설비공종비율
3)집계표
4)내역서
5)물량산출집계표
6)물량산출내역서
하나의 통합문서에 필요할때마다 계속 자동화 발생하는
회별기성서류가 되는 셈이다
이렇게 자동화로 만들기 위한 기본정보들이 Master문서에 있고
Master문서내에는 각 기성이 만들어지는 히스토리가 되는 내용이
들어있게 될 것이고 필요에 따라서 전체의 분석을 위한
정보의 블랙박스가 되야 할 것이다
그러니 각 회별기성문서가 없어져도 Master에서 다시
명령을 하여 해당 회의 기성문서를 만들면 된다
잘 만들고 못만들고는 질문한 분의 상상력이 크면 크게,
작으면 작게 될 것이다
이런 소루션을 만들때 가장 큰문제
엑셀이 좋은 점은 입맛에 맞게 서식도 하고 콘트롤도 갖다 붙이고
이런 저런 나름대로의 멋을 내고 전체적인 그림없이
이런 저런 디테일에 억메어 버린다는 점이 거의 모든 엑셀을 하는 분들의 문제다
모든 정보는 부모레벨의 정보가 있고, 삼촌레벨의 정보가 있고
형님레벨의 정보가 있다는 단계적 정리 정돈을 잘 할수 없다는 것이다
하나의 프로젝트를 시작하려면 기본정보라는 것이 자리를 잘 잡고 있어야 한다
예를 들면 프로젝트를 수행하기 위하여 부여 받은 예산이 있다
이 예산을 한장의 시트에 수식같은 것 없이 계산된 정보 없이
순수한 원시정보만 잘 보관하고 있어야 이것이 프로젝트의 블랙박스의 원액이다
이 시트에 이런 저런 서식을 하고 셀을 병합을 하고
나름 보기 좋게 하려고 하지만, 이것은 모든 후속적인 일이
이리 꼬이고 저리 꼬여서 헷갈림의 원본이 된다
이것이 질서있게 잘 자리 잡아야 놓아야 다음에 수도 없는 보고서와
분석을 하는 원본이 되는 것이다
엑셀의 좋은 점이 최대의 약점이 될 수 있다는 점, 잊지 마시기를!!
콘트롤 타워 만들기
대개 소루션을 만들면서 시트마다 버튼을 달고 ,정신이 없다
특히 VBA를 어느 정도 익숙해지면서, 버튼 이곳, 저곳 달아매는 것에
흥미있어 한다
시트에는 절대 버튼을 붙이지 말자
물론 한두개 필요한 곳에서는 상관없지만 될수 있는 한
한두장짜리 간단한 도구를 만든다면 괞찮겠지만..
될 수 있는 대로
UserForm을 활용하는 것을 좋아하는 것이 좋다
UserForm을 즐겨 사용하는 습관이 나중에 VB.Net을 하던 Java 안드로이드를 하던
쉽게 갈 수 있다
시트상에 콘트롤을 달아 매는 습관만 갖고 있다면,
엑셀밖으로 발전해 나갈수 없다
아무튼 한곳에서 콘트롤 하는 콘트롤타워가 있어야 하고
이것을 수행할수 있는 것이 UserForm인 것이다
우선 하나 만들어 보자
그림과 같이 디자인타임에는 빈탕의 UserForm으로 시작하여
런타임에 프로그래밍적으로 만드는 것이 폼을 좀더 정확하게 잘 그리고
시간을 절약한다(코딩에 조금 숙달하면)
이렇게 그리는것이 좋은 것은 아래와 같이 보고서구성시트명을
상수로 정해 놓고, 만약 보고서시트명을 바꾼다거나, 추가하거나 할때
이 상수만 고치면 UserForm은 이 상수의 내용대로 만들어지게 하는 것이
프로그래밍을 하는것이 흥미로워진다
Public Const SHEET_LIST As String = "도급기성검토의견서,기계설비공종비율,집계표,내역서,물량산출집계표,물량산출내역서"
마치 Java안드로이드 코너에서 XML로 인터페이스를 만드는대신에
RunTime에 콘트롤을 심는 것도 같은 맥락의 이야기다
그런데 여기에서 버튼을 크릭하면 어떤 일을 하게 하여야 하는데
이때 각 버튼의 Click이벤트를 만드는 것이 재미있다
몇일전에 다루었던 VSTO코너에서의 엑셀쌤플화일을 같이 보시면 좋을 것이다
쌤플화일을 보시고 연구한후 위의 보고서 상수값을 풀어서 버튼을 위의
그림과 같이 한번 만들어 보시기 바란다
시도를 한번 해보면 여러분의 실력은 당연히 확장된다
아무튼 버튼의 Click 이벤트를 다루려면 Class모듈을 활용하여야 한다
VBA내에 크래스모듈이 있는 이유중의 가장 큰 이유가 이 부분이다
크래스모듈에는 아래와 같은 몇줄만 들어가면 된다
Option Explicit
Public WithEvents oBtn As MSForms.CommandButton
Private Sub oBtn_Click()
MsgBox " 이곳에 [" & oBtn.Caption & "] 버튼을 크릭하면 실행될 내용을 넣으면 된다"
End Sub
버튼이 하나가 만들어지면서 위의 크래스에서 만들어지는 개체가 버튼에
하나씩 추가되어 만들어진다고 보면 된다
만약 버튼이 10개가 만들어지면 위의 크래스모듈에서 개체가 10개가
만들어져서 버튼과 연결이 되는 것.
물론 크래스모듈이 생성해주는개체의 역할을 버튼이 크릭하는 신호를
받아서 처리해주는 기능만 들어 있는 셈이다
이런 기능을 만들어서 버튼에 붙여 주려면 크래스모듈이 있어야 한다는 이야기
그리고 위의 UserForm은 아직 확정된 것은 아닐것이다
어떻게 변할지는 만들고자 하시는 분의 의욕에 따라서
만들다 보면 욕심이 나고 , 그러다 보면 기능이 추가되고
그러면 콘트롤타워의 인터페이스도 자연히 변하고
엑셀의 좋은 것은 그렇게 상황에 맞추어 적절히 만져나갈수 있다는 점이다
아무것도 들어 있지 않은 빈탕의 UserForm의 코드를 아래와 같이 작성한다
'크래스모듈로 생성되는 개체를 모아두는곳
'버튼을 30개 만든다면 크래스모듈에서 생성된 개체가
'30개라는 이야기고 이것을 집합체 혹은 배열에 모아서 외부변수로
'보관하도록 한다
Dim oButtons As Collection
'UserForm이 로딩될때 실행되는 Initialize이벤트를 활용하여
'버튼만들기 createButtons
'폼의 컨트롤개체서식 setForm
Private Sub UserForm_Initialize()
createButtons
setForm
End Sub
'버튼을 만드는 프로시져
Private Sub createButtons()
Dim oShts As Variant, varX As Variant
Dim oBtn As MSForms.CommandButton
Dim iStartX As String
Dim iStartY As String
Dim iX As Integer, iY As Integer
Dim oButton As clsButton
'버튼의 갯수가 되고 버튼의 캡션정보가 되는
'Main 모듈시트의 상수를 배열로 풀어서 oShts에 담고
oShts = Split(modMain.SHEET_LIST, ",")
iStartX = 6
iStartY = 6
'외부 집합체 변수 New로 초기화, 이것을 초보님들은 잊어 먹구..
'왜 안되지??왜 안되지??머리를 쥐어 뜯는다
'초기화 잊지 마라!!
Set oButtons = New Collection
'위에서 풀어 놓은 배열 oShts를 순환하면서 버튼을 만든다
For Each varX In oShts
'Button은 "Forms.CommandButton.1" 이 크래스명이다
'이것을 사용하여야 UserForm의 콘트롤을 만든다
Set oBtn = Me.Controls.Add("Forms.CommandButton.1")
oBtn.Caption = varX
oBtn.Width = 90
oBtn.Height = 24
oBtn.Left = iStartX + iX * oBtn.Width
oBtn.Top = iStartY
'이렇게 만든 버튼을 크래스모듈로 개체를 생성하면서
'이 개체가 내부적으로 갖고 있는(크래스모듈에 설정한) Button개체와
'맵핑을 시켜야 비로서 크래스모듈에 작성한 Click이벤트를
'사용할수 있는 자격이 생긴다
Set oButton = New clsButton
Set oButton.oBtn = oBtn
'이렇게 만든 것이 생명을 유지하고 있어야 하므로
'외부에 선언한 집합체에 추가 시킨다
oButtons.Add oButton
iX = iX + 1
Next
'버튼은 다 만들고, 이제 콤보상자를 하나 더 만들자
'이것은 매회 기성마다 발생하는 별도의 화일을 보관한 폴더내의
'화일의 열람을 자동화하기 위한 목록을 작성할 컨트롤이다
'이것도 역시 목록을 선택하면 어떤 일을 하게 하여야 할 것이고
'그러러면 이것도 역시 별도의 크래스모듈을 하나 준비하거나
'하여야 할 것이다
'이것을 만드는 것은 다음 화일에서 하기로 하고
'여기에서는 아직 이벤트를 사용할수 있는 기능은 없고
'UserForm에만 배치하는 모양만 있는 허당콘트롤이다
Dim oCombo As MSForms.ComboBox
Set oCombo = Me.Controls.Add("Forms.ComboBox.1")
oCombo.Left = iStartX
oCombo.Top = iStartY + 24 + 6
oCombo.Width = 90 * 1.5
oCombo.Height = 24
'라벨콘트롤도 역시 위와 같은 요령으로 만든다
'이것은 현재 몇회기성을 작성할 것인지 표시를 하는 역할
Dim oLabel As MSForms.Label
Set oLabel = Me.Controls.Add("Forms.Label.1")
oLabel.Caption = ".."
oLabel.Left = oCombo.Left + oCombo.Width + 6
oLabel.Top = oCombo.Top
oLabel.Width = 120
'콤보상자를 만들었으면 콤보상자에 목록을 채워야 한다
'아래의 프로시져에서 채워넣는 작업을 한다
fillComboAndLabel oCombo, oLabel
'만들어진 컨트롤에 따라서 폼의 사이즈가 정해질 것이다
'초등학교 산수로 풀어서..크기 정하고
Me.Width = iStartX + iX * 90 + iStartX * 2
Me.Height = iStartY + 24 * 2 + iStartY + 25
End Sub
'콤보상자 목록 채우기
'Main모듈시트에 선언한 상수,매회기성저장폴더이름,로
'폴더의 내용을 읽어서, 즉 화일의 갯수와 이름
'콤보상자에 채우고
'화일의 상태를 라벨컨트롤에 써준다
Private Sub fillComboAndLabel(oCom As MSForms.ComboBox, oL As MSForms.Label)
Dim sPath As String, sFileName As String, iFile As Integer
sPath = ThisWorkbook.Path & "\" & modMain.FOLDER_NAME & "\"
sFileName = Dir(sPath)
If sFileName = "" Then
oL.Caption = "현재 기성작성은 1회기성"
Else
Do
oCom.AddItem sFileName
iFile = iFile + 1
sFileName = Dir
Loop While sFileName <> ""
oL.Caption = "현재 기성작성은 " & iFile + 1 & "회기성"
End If
End Sub
'콘트롤의 폰트서식등을 정해주는 프로시져
Sub setForm()
On Error Resume Next
Me.StartUpPosition = 0
Me.Caption = modMain.PROJ_NAME
Me.Left = 0
Me.Top = 0
Dim oCtl As Control
For Each oCtl In Me.Controls
oCtl.Font.Name = "맑은 고딕"
oCtl.Font.Size = 9
Next
End Sub
아래 화일의 버튼을 생성하는 씨앗인 상수목록을 적절히
바꿔보면서 흥미있게 관찰하시고, 응용을 한번이상 해보시기를..
***[LOG-IN]***