PROGRAMMING WORKSHOP

문서작성자동화|시공검측요청서

문제...

집을 지어서 납품을 하던 제품을 만들어 납품을 하던
만들어지는 프로세스에 따라서 제품을 주문 한 사람과 제룸을 만드는 사람
사이에서는 다양한 문서가 오고 간다
그런 문제에 있어서 질문이 있었던 것을 범묭적으로 응용할수 있는 형태로
바꾸어서 화일 몇개를 만드어 보자
여러 프로세스중의 검수요청서를 관리하고 만드는 과정을 보도록 하자

대개가 이런 것을 만들때 현장실무에서는 VLOOKUP함수드을 사용하여
몇번째 검수 작업이라는 키값을 전달하여 관련된 정보를 이미 마련된
양식의 각각의 셀에서 참조하여 자동화한다
아래 코드를 실행해보시면..VBA없이 일반적으로 수식을 활용하는 방법이다

Sub addValidationAndUseVlookupFormatCondition()
Dim shtX As Worksheet
Dim oScrollBar As Shape
Dim rStart As Range
Dim iRow As Integer
Dim rLinkCell As Range
Dim rTbl As Range
Dim oFormatCondition As FormatCondition
Dim sValidation As String

Set shtX = Worksheets.Add
'' 보고서관리 테이블 쌤플...
Set rStart = shtX.Range("H1")
With rStart
    .Value = "정보_1"
    .AutoFill .Resize(, 5)
    For iRow = 1 To 20
        .Offset(iRow).Resize(, 5).Value = Array(iRow, _
                                                    Chr(Int(Rnd() * 26 + 65)), _
                                                    String(2, Chr(Int(Rnd() * 26 + 65))), _
                                                    Int(Rnd() * 100) + 100, _
                                                    Int(Rnd() * 100) + 100)
                                                    
    Next
End With

'' 보고서 쌤플, 일련번호등을 주면 양식에 관련된 정보를
'' VLOOKUP으로 관리테이블에서 자동으로 읽어오게 한다
With shtX
    Set rTbl = rStart.CurrentRegion
    Set rLinkCell = .Range("B4")
    '' 유효성목록으로 보고서에서 일련번호 선택하도록 시뮤레이션
    For iRow = 1 To 20
        sValidation = sValidation & iRow & ","
    Next
    sValidation = Left(sValidation, Len(sValidation) - 1)
    
    rLinkCell.Validation.Add xlValidateList, , , sValidation
    rLinkCell.Select
    rLinkCell = Int(Rnd() * 20) + 1
    ''유효성검사에서 선택된 일련번호와 관련된 정보와 맵핑되게 VLOOKUP함수의 수식사용
    .Range("A4").Resize(5) = Application.WorksheetFunction.Transpose(rStart.Resize(, 5))
    .Range("B5") = "=VLOOKUP(" & rLinkCell.Address(True, True) & "," & rTbl.Address(True, True) & ",ROW()-ROW(" & rLinkCell.Address(True, True) & ")+1,FALSE)"
    .Range("B5").AutoFill .Range("B5").Resize(4)
End With

'' 현재 선택된 정보를 관리테이블상에 표시하기 위하여 조건부서식
Set oFormatCondition = rTbl.FormatConditions.Add(xlExpression, , "=" & rLinkCell.Address(True, True) & "=" & rTbl.Cells(1).Address(False, True))
With oFormatCondition
    .Font.ColorIndex = 2
    .Interior.ColorIndex = 3
End With
End Sub

여기에서는 그런 수식 싹 걷어내고
VBA로 처리하도록 하자

프로세스의 한 과정을 하는 것이니, 응용하면 다른 과정의 문서를
자동화하는데도 모두 잘 응용하실수 있을 것으로 생각하고...

변수와 상수의 준비...그리고 사용자 인터페이스 UserForm 만들기

어던 소루션을 만들던,
사용될 정보를 계획하는 것이 첫단계이다
자동화소루션이란 결국은 어떤 정보를 읽어 어떻게 처리하여 다른 형태의 정보로
재가공하여 내보내는 것이 일이다
그러니 당연히 읽어 들일 정보, 읽어 들일 정보의 위치,
출력하여야 할 정보, 출력할 정보의 위치에 대한 정보등 온통 정보를 주고 받고
그러다 보니 임시로 담어둘 통도 필요하고..
작업전에 이런 준비작업을 하는 것이 우선이다
물론 하다가 보면 정보의 성격을 재정의하여야 할수도 있지만,
처음 시작할때 , 대강의 사용할 정보를 목록화하는 것이 필요할 것이고
이작업이 결국은 변수와 상수의 선언이 되는 셈이다



그림과 같이 사용하여야 할 정보들은
크게 나누어서
요청서목록이 담겨있는 워크시트
요청서목록이 담겨있는 범위
요청서내용을 가공하여 결과물로 내보내야할 요청서 워크시트
그리고 요청서 시트에 어떤 범위에 목록상의 어떤 값이 들어가야 할지의 범위개체 정보
이런 모든 것을 주고, 받고 한곳에서 콘트롤할수 있는 인터페이스로 제공되는 개체가
UserForm이니 활용을 잘 할수 있어야 할 것이다

아무 콘트롤이 없는 UserForm을 로딩하면사 런타임으로
아래 그림과 같이 UserForm에 필요한 콘트롤들을 위의 정보변수들을 읽거나,쓰면서
만들게 된다



프로그래밍은 아이들 레고브록으로 하나씩 조립하는 것이고..
그러니 프로그래밍은 어른들 장난감이다
조립하는 코드를 하나씩 보자
1)목록시트 선택
목룍은 몇장이 될지 모른다고 가정하자..
프로그래밍은 만들때와 달리 확장 성장한다..그러니 목록이 몇장이 될지 모른다고 생각하면
디자인 타임에 만들어 놓기가 융통성이 없다
그래서 폼이 로딩될때, 목록시트가 몇장으로 선언되었는지 변수를 읽고 런타임으로 만들면
융통성이 있다




''전역변수로 선언된 상수를 읽어서 만든다
''목록시트가 늘어날때는 이곳의 값만 수정해주면 된다
Public Const WORK_LIST_SHEETS As String = "M,F"
''옵션버튼은 RunTime으로 몇개가 만들어질지 디자인 타임에는 모른다
''그래서 전역변수에 집합체를 하나 만들고..
''만들어지는 옵션버튼을 크래스모듈에 연결하여 집합체에 크래스모듈에서의 개체를 채워넣는다
''이유는 옵션버튼의 이벤트프로시져를 활용하기 위한 수단으로 크래스모듈을 사용한다
Public oOptList As Collection
''/////////////////////////////////////////////
''그룹상자(Frame)만들기
Set oBox = Me.Controls.Add("Forms.Frame.1", "frmTop")
oBox.Caption = "목록시트선택"
oBox.Left = 6
oBox.Top = 6
''그룹상자의 폭은 목록시트가 몇장인지에 따라서 자동조정되게 한다
oBox.Width = (UBound(Split(modMain.WORK_LIST_SHEETS, ",")) + 1) * 50 + (6 * 2)
oBox.Height = 35

''그룹상자내의 목록시트를 선택하는 옵션버튼
Set oOptList = New Collection
For Each sShtList In Split(modMain.WORK_LIST_SHEETS, ",")
    Set oOpt = oBox.Controls.Add("Forms.OptionButton.1", "opt" & sShtList)
    oOpt.Left = 6 + iNextControl * 50
    oOpt.Top = 6
    oOpt.Caption = sShtList
    Set oOptCls = New clsOptionButton
    Set oOptCls.oOpt = oOpt
    oOptList.Add oOptCls
    
    iNextControl = iNextControl + 1
Next

''위의 옵션버튼을 크래스모듈의 옵션버튼타입변수와 맵핑하기 위하여
''아래와 같이 옵션버튼만을 위한 크래스모듈을 작성한다
''아래는 clsOptionButton크래스모듈의 내용이다
''하나의 크래스모듈에는 OptionButton 개체변수가 준비되고
''만들어지는 OptionButton마다 Change이벤트프로시져를 활용할수 있게 된다
Public WithEvents oOpt As MSForms.OptionButton

Private Sub oOpt_Change()
Dim sShtName As String
Dim rListSource As Range

sShtName = oOpt.Caption
Set modMain.shtCurrentList = Worksheets(sShtName)
Application.Goto modMain.shtCurrentList.Range("A1")

Set rListSource = modMain.shtCurrentList.Range("A1").CurrentRegion
Set rListSource = rListSource.Offset(1).Resize(rListSource.Rows.Count - 1)
With frmMain
    .lstList.ColumnCount = rListSource.Columns.Count
    .lstList.RowSource = modMain.shtCurrentList.Name & "!" & rListSource.Address
    .lstList.Enabled = True
    .btnRequestSheet.Caption = modMain.REQUEST_SHEET & "가기"
    .Controls("frmSpin").Enabled = True
    .spiStart.Value = 1
    .spiEnd.Value = 1
    .lstList.Selected(0) = True
End With
End Sub



좀더 적극적이고 쎄련된 소루션을 만들려면 크래스모듈를 활용할줄 알아야한다
좋은 쌤플이 되는 것이니, 긴가민가 했던 분들은 이번에 자신의 것으로 만드시기를

이제 요청서시트와 목록시트를 오고 가게 하는 버튼을 하나 만들고



전역변수로 아래와 같이 만들고, 이것은 버튼이 하나만 만드는 것이니까
그냥 WithEvents키워드로 전역변수화시키면 해당 개체의 이벤트프로시져를 쓸수 있다

Public WithEvents btnRequestSheet As MSForms.CommandButton

그리고 아래와 같이 만든다..

Set Me.btnRequestSheet = Me.Controls.Add("Forms.CommandButton.1", "btnRequestSheet")
Me.btnRequestSheet.Left = oBox.Left + oBox.Width + 12
Me.btnRequestSheet.Top = 20
Me.btnRequestSheet.Width = 100
Me.btnRequestSheet.Caption = modMain.REQUEST_SHEET & "가기"
Me.btnRequestSheet.Height = 20



하나의 그룹상자속에 두개의 TextBox콘트롤과 두개의 Spinner콘트롤
이것도 역시 WithEvents키워드를 사용하여 전역변수에 선언하여 이벤트프로시져를 사용할수 있게한다

Public WithEvents spiStart As MSForms.SpinButton
Public WithEvents spiEnd As MSForms.SpinButton

그리고 아래와 같이 폼이 생성되면서 만들어 넣는다


Set oBoxSpin = Me.Controls.Add("Forms.Frame.1", "frmSpin")
oBoxSpin.Left = Me.btnRequestSheet.Left + Me.btnRequestSheet.Width + 12
oBoxSpin.Top = 6
oBoxSpin.Height = 35
For iNextControl = 1 To 2
    If iNextControl = 1 Then
        Set txtStart = oBoxSpin.Controls.Add("Forms.TextBox.1", "txtStart")
        txtStart.Left = 6 + (iNextControl - 1) * 50
        txtStart.Width = 30
        txtStart.Top = 6
        Set spiStart = oBoxSpin.Controls.Add("Forms.SpinButton.1", "spiStart")
        spiStart.Left = txtStart.Left + txtStart.Width + 3
        spiStart.Top = 3
        spiStart.Min = 1
        spiStart.Max = 1000
    Else
        Set txtEnd = oBoxSpin.Controls.Add("Forms.TextBox.1", "txtEnd")
        txtEnd.Left = 6 + (iNextControl - 1) * 50
        txtEnd.Width = 30
        txtEnd.Top = 6
        Set spiEnd = oBoxSpin.Controls.Add("Forms.SpinButton.1", "spiEnd")
        spiEnd.Left = txtEnd.Left + txtEnd.Width + 3
        spiEnd.Top = 3
        spiEnd.Min = 1
        spiEnd.Max = 1000
    End If
   
Next
oBoxSpin.Width = 110



이제 생산해야할 보고서,요청서의 목록시트를 위에서 만든 옵션버튼의 크릭에 따라서
해당 목록시트로 이동하면서 ,해당 목록시트로 목록을 컨트롤에 채워주고 싶다..
시트를 보지 않고도 쉽게 보는 것이 쎄련된 인터페이스가 될 것이니까..

역시 마찬가지로 RunTime으로 생성된 목록상자의 변수를 WithEvnets로 전역변수로 선언하고..

Public WithEvents lstList As MSForms.ListBox

아래와 같이 만든다


Set lstList = Me.Controls.Add("Forms.ListBox.1", "lstList")
lstList.Left = 6
lstList.Top = oBox.Top + oBox.Height + 6
lstList.Width = 500
lstList.Height = 200
lstList.ColumnHeads = True
lstList.ListStyle = fmListStyleOption
lstList.MultiSelect = fmMultiSelectMulti
''목록의 초기화를 위에서 만든 옵션버튼의 첫째버튼의 이벤트가 실행되게 하여 채워넣게 한다
oOptList(1).oOpt.Value = True

다음은..



위의 목록상자에서 여러개의 요청서를 선택하여 한꺼번에 출력을 하고 싶을때
여러개가 선택될때마다 그림의 탭이 해당되는 목록만큼 만들어지기도 하고 삭제가 되기도 하게 한다
목록상자의 선택과 연동이 되는 것이다
이유는 여러개를 선택한후 각각의 요청서의 내용을 볼수 있게도 하고
해당 요청서의 그림화일을 선택하기도하고,추가하기도 하는 기능을 만든다
그림화일은 해당 요청성마다 몇개의 사진이 들어가는지 알수 없으니까..
해당요청서마다 각각 편집을 가능하게 하는 셈이다

이런 기능은 여러분들의 현장에서 다양하게 응용하여 쎄련된 도구를 만들수 있는 좋은 쌤플이다

역시 마찬가지로 전역변수로 WithEvents로 선언한다

Public WithEvents multiTab As MSForms.TabStrip

그리고 아래와 같이 만들어 붙이자

Dim oTab As MSForms.Tab
Set multiTab = Me.Controls.Add("Forms.TabStrip.1", "myTabs")
multiTab.Tabs.Remove 1
multiTab.Tabs(0).Caption = "사진"
multiTab.Top = lstList.Top + lstList.Height + 12
multiTab.Width = lstList.Width
multiTab.Height = 25



이제 스크롤바콘트롤과 이미지콘트롤과 버튼콘트롤 몇개를 전역변수에 WithEvents로 선언하자
이미지콘트롤을 이벤트가 필요없으니 제외하고..

Public WithEvents picScrollBar As MSForms.ScrollBar
Public WithEvents picBox As MSForms.Image
Public WithEvents btnPreview As MSForms.CommandButton
Public WithEvents btnPDF As MSForms.CommandButton
Public WithEvents btnToBook As MSForms.CommandButton
Public WithEvents btnPrint As MSForms.CommandButton
Public WithEvents btnImage As MSForms.CommandButton


스크롤바는 각각의 요청서에 사진이 몇장이 있을지 모르니까..
스크롤바를 움직일때마다 다음 사진으로 이동하게 하기 위함이다
아래와 같이 만들어 붙인다


''여러개의 사진을 이동 관찰할 스크롤바
Set picScrollBar = Me.Controls.Add("Forms.ScrollBar.1", "myScrollBar")
picScrollBar.Top = multiTab.Top + multiTab.Height + 3
picScrollBar.Left = 6
picScrollBar.Width = 60
picScrollBar.Height = 15
Set picBox = Me.Controls.Add("Forms.Image.1", "myImage")
picBox.Left = 6
picBox.Width = lstList.Width - 120 ' / 3 * 2
picBox.Top = picScrollBar.Top + 15
picBox.Height = 200


Dim iLeft As Integer
iLeft = picBox.Left + picBox.Width + 6
Dim sBtn As String
Dim sBtns As Variant

''실제작업의 최종실행용 버튼들
'' 버튼이 더 필요하면 전역변수에 WithEvents로 선언한후 아래의 배열에 추가시킨다
'' 아래 프로시져를 호출하여 배열속에 있는 버튼갯수만큼 만든다	
    makeButtons sBtn, iLeft, picBox.Top, iNextControl
Next

''//////////////////

Sub makeButtons(sBtn As String, iLeft As Integer, iTop As Integer, iX As Integer)
Const WIDTH_ As Integer = 100
Const HEIGHT_ As Integer = 20
Dim oBtn As MSForms.CommandButton
Set oBtn = Me.Controls.Add("Forms.CommandButton.1", "btn" & sBtn)
oBtn.Caption = Array("미리보기", "PDF", "문서변환", "출력", "그림새로선택저장")(iX)
oBtn.Left = iLeft
oBtn.Top = iTop + (HEIGHT_ + 3) * (iX)
oBtn.Width = WIDTH_
oBtn.Height = HEIGHT_
Select Case sBtn
    Case "btnPreview"
    Set btnPreview = oBtn
    Case "btnPDF"
    Set btnPDF = oBtn
    Case "btnToBook"
    Set btnToBook = oBtn
    Case "btnPrint"
    Set btnPrint = oBtn
    Case "btnImage"
    Set btnImage = oBtn 
End Select
End Sub

각각의 콘트롤 이벤트프로시져 내용은 일부된 것도 있지만
대부분 다음 화일에서 계속 진행하도록 하고
아래의 화일에서는 단순히 프로젝트를 위한 변수나 상수들의 선언상태와
그리고 위의 모든 필요한 콘트롤을 생성하는 과정을 잘 이해하고 보시고,여러분들의
소루션에도 많이 응용하시기를...

***[LOG-IN]***