UNO-XL-Programming Basic

Excel SOLUTIONS

Problems And Solutions

XL_VBA_BRAIN-TRAINING_씨리즈

Excel 2007 Version

Excel+VB.Net:[VSTO]

지난 화일에서 각시트의 테이블에 접근하는 것을 크래스모듈을 사용하였다
물론 일반모듈에서 해도 상관은 없다
하지만 크래스모듈에 콤팩트하게 관리하면 다른 엑셀소루션에서도 많은 시간을 절약할수 있는
크래스모듈이 될 것이다
왜냐면 엑셀로 만드는 소루션은 모두 범위에 접근하고, 범위에서 정보를 처리하는 일이다
그러니 소루션을 만들때마다, 매번 같은 범위접근코딩을 하지 말고
범위,테이블만 다루는 크래스모듈을 하나 만들어두면 좋은 라이브러리가 된다
사용하는 방법은..
그냥
Dim oMyTable As New clsTable
이라고 개체를 선언하고..
어떤 시트에 있는 테이블을 사용한다는 것을
oMyTable.setTable "시트명"
와 같이 시트명을 전달하면 해당시트의 테이블범위의 모든 부분을
쉽게 접근할수 있게 되는 것



이 시리즈가 끝나면 엑셀소루션을 만드는 좋은 개체 크래스를 하나 챙기는 셈이다
그리고 크래스를 자신의 업무에 맞게 수정보완하면서 활용하면 되는 것이다
예를 들어서
개체를 만들고, 어떤 시트의 이름을 전달하여 해당시트의 테이블을 개체화시키고 싶어서
아래와 같이 작성되어 있다면
만약 전달한 시트의 A1셀에 테이블이 없다면
후속작업은 하나도 이루어지지 않을 것이다

Sub setTable(sShtName As String)
On Error Resume Next
Set TableAll = Worksheets(sShtName).Range("A1").CurrentRegion
End Sub
    

그래서 이렇게 추가 수정하면 좋을 것이다

Sub setTable(sShtName As String)
On Error Resume Next
Set TableAll = Worksheets(sShtName).Range("A1").CurrentRegion
If TableAll.Cells.Count = 1 Then GoTo X
If TableAll.Cells.Count <> Application.CountA(TableAll) Then GoTo X
Exit Sub
X:
Set TableAll = Nothing
End Sub


    

사용하고자 하는 테이블은 A1셀에서 시작하도록 제어한다
그리고 사용하는 쪽의 프로시져에서는

Dim oMyTbl As New clsTable
oMyTbl.setTable "시트명"
If oMyTbl.Table Is Nothing Then MsgBox "현재 사용하려는 시트는 유효한 테이블이 없습니다"

라고 제어해주면 좋을 것이다
기본적인 크래스를 만들고, 사용하고자 하는 소루션의 성격에 맞추어
성장시키면서 활용하면 되는 것

아무튼, 이번 페이지는 삭제,수정,신규저장등의 버튼에 지능을 달아주는 것
수정을 하는 것은 테이블 원본의 행이 늘거나 줄게 되는 것은 없으니
별문제가 없으나
삭제나 신규저장을 하면 테이블원본의 변화와 더불어 이것과 연결되어 있는
콘트롤의 원본정보가 변경되어야 한다
삭제를 하면 원본의 행이 하나 사라지고
rRow.EntireRow.Delete
하면 간단하지만, 원본범위의 주소값을 물고 있는 콘트롤의 RowSource속성을
바꿔주어야 할 것이다
그러니 원본이 바뀔때마다 콘트롤의 연결정보를 바꿔줄 프로시져를 하나 추가한다

어떤 목록상자이던 전달하여 알아서 RowSource속성을 바꿔주게..
이렇게 분리하면 지난화일에서 초기화할때, 중복되게 작성된 내용도
이것을 호출하면 된다

Sub resetListBoxSource(oList As MSForms.ListBox, Optional bSetToLast = True)
On Error Resume Next
Dim sSourceString As String
oList.RowSource = ""
Select Case oList.Name
    Case Me.lstMain.Name
        sSourceString = modMain.oQueryTable.TableRowSourceWithNewString
    Case Me.lstTreatingProcess.Name
        sSourceString = modMain.oProcessTable.TableRowSourceWithNewString
End Select
oList.RowSource = sSourceString

If bSetToLast Then oList.ListIndex = oList.ListCount - 1
End Sub
    

그리고 쌤플시트를 매번 폼이 로딩될때마다 새로 만들면
저장,삭제,수정등의 작업의 의미가 없어진다
그래서 아래와 같이 함수를 하나 달아서
이미 각각의 테이블을 갖고 있는 시트가 존재하는지 확인하고 편집(수정,삭제,신규저장)을 할수 있을 것이다

Sub loadFormThinkingMore()
On Error Resume Next
Dim varX As Variant

modMain.bUseCurrentTableAsItIs = True

For Each varX In Array(PETS, CUSTOMERS, TREAT_TYPES, MAIN, QUERY)
    If Not isSheetExist(CStr(varX), ThisWorkbook) Then modMain.bUseCurrentTableAsItIs = False: Exit For
Next

UserForm2.sHow
End Sub

Function isSheetExist(sShtName As String, oBook As Workbook)
On Error Resume Next
isSheetExist = oBook.Worksheets(sShtName).Name = sShtName
End Function

편집하는 테이블은 오직하나의 테이블만 하면 된다



그림과 같이 모든 테이블은 각각의 키값으로 상호 관계를 물고 있으니
모든 연결된 진료진행 테이블에서 각각의 관계되는 테이블의 키값을 입력하고
수정하고 삭제하면 되는 것

신규정보를 입력하려면 각 테이블의 마지막행의 한칸 밑의 새로운 행에 입력을 하는 작업이다
그런데 테이블마다 입력을 위한 코딩을 한다면 똑같은 짓을 여러번하여야 하는 셈이다
테이블이 몇개가 되던 만들어 놓은 사용자정의개체(크래스모듈)에 하나의 메소드만 만들어 놓으면
되는 것이고, 그렇게 간편하게 하기 위하여 크래스모듈을 사용하는 것

아래와 같이 메소드를 하나 만들자

'' 입력할 정보를 문자열로 연결하여 하나의 문자열을 매개변수로 보낸다
Function addNewRow(sValue As String, sDelimiter As String) As Boolean
On Error Resume Next
Dim rNewRow As Range
Dim varValues As Variant
Dim varX As Variant
Dim iCol As Integer

Set rNewRow = Me.TableNewRow
varValues = Split(sValue, sDelimiter)
'' 첫째열 ID열은 자동증가하도록 하였으니 제외하고 나머지 열의 갯수를
'' 확인하여 갯수가 다르면 아웃...
If rNewRow.Cells.Count - 1 <> UBound(varValues) + 1 Then
    MsgBox "열의 갯수에 맞게 정보를 전달하세요"
    addNewRow = False
Else
    iCol = 1
    modMain.bStopEvents = True
    
    For Each varX In varValues
        iCol = iCol + 1
        If IsDate(varX) Then
            rNewRow.Cells(iCol) = CDate(varX)
        ElseIf IsNumeric(varX) Then
            rNewRow.Cells(iCol) = CInt(varX)
        Else
            rNewRow.Cells(iCol) = varX
        End If
    Next
    rNewRow.Cells(1) = Application.Max(rNewRow.Cells(1).EntireColumn) + 1
    rNewRow.Offset(-1).Copy
    rNewRow.PasteSpecial xlPasteFormats
    Application.CutCopyMode = False
    addNewRow = True

    modMain.bStopEvents = False
End If
End Function

실은 위와 같이 신규저장,삭제,수정을 각각 독립적으로 만들어도 좋겠지만
데이타편집이라는 하나의 함수를 만들고, 신규저장,삭제,수정을 모두 한곳에서
하는 것도 좋은 방법이다
아래와 같이, 통합과 분산에서, 통합을 시켜놓는 경우가 될 것이다

''수정과 신규입력버튼크릭..이벤트
Private Sub cmdAdd_Click()
On Error Resume Next
Dim rPetTable As Range
Dim iRowIndex As Integer
Dim iPetIndex As Integer
Dim iTreatIndex As Integer
Dim sTreatDate As String
Dim iCustomerIndex As Integer
Dim sPara As String
Dim sEditType As String

sEditType = cmdAdd.Caption
If Me.cboPets.ListIndex = -1 Or Me.cboTreats.ListIndex = -1 Then MsgBox "동물과 진료내용을 선택하세요": Exit Sub


iPetIndex = Me.cboPets.ListIndex + 1
iTreatIndex = Me.cboTreats.ListIndex + 1
sTreatDate = Me.txtDateAndTime.Text

If Not IsDate(sTreatDate) Then MsgBox "날자와 시간을 정상적으로 입력하세요": Exit Sub

iCustomerIndex = Application.VLookup(iPetIndex, modMain.oPetTable.Table, 2, False)
If iCustomerIndex = 0 Then MsgBox "선택한 동물의 관련고객이 불분명합니다," & vbNewLine & _
                                                    "시트의 내용을 손으로 처리하신모양입니다": Exit Sub


sPara = Split(sTreatDate, " ")(0) & "," & Trim(Replace(sTreatDate, Split(sTreatDate, " ")(0), "")) & "," & iPetIndex & "," & iCustomerIndex & "," & iTreatIndex

Select Case sEditType
    Case 신규저장
        modMain.oProcessTable.editRow sPara, ",", 0
        Me.OptionButtonUpdate.Value = True
    Case 수정저장
        Dim iTreatingProcess As Integer
        iTreatingProcess = Me.lstTreatingProcess.ListIndex
        modMain.oProcessTable.editRow sPara, ",", Me.lstTreatingProcess.ListIndex + 1
        Me.OptionButtonNew.Value = True
        Me.OptionButtonUpdate.Value = True
        Me.lstTreatingProcess.ListIndex = iTreatingProcess
End Select

MsgBox sEditType & " 되었습니다"
End Sub
''삭제버튼크릭..이벤트
Private Sub cmdDelete_Click()
On Error Resume Next
Dim iNextIndex As Integer

If MsgBox("현재 선택된 정보를 삭제하시겠습니까?", vbYesNo) = vbNo Then Exit Sub
iNextIndex = Me.lstTreatingProcess.ListIndex + 1
modMain.oProcessTable.editRow "", "", iNextIndex
MsgBox 삭제 & "처리 되었습니다"
Me.lstTreatingProcess.ListIndex = iNextIndex
End Sub
''위의 각버튼에서 호출하는 통합편집함수
Function editRow(sValue As String, sDelimiter As String, iTargetRow As Integer) As Boolean
On Error Resume Next
Dim varValues As Variant
Dim varX As Variant
Dim iCol As Integer
Dim rTargetRow As Range
Dim bNG As Boolean

If sValue = "" Then ''/////////////삭제
    modMain.oProcessTable.Table.Rows(iTargetRow + 1).EntireRow.Delete
    modMain.oQueryTable.Table.Rows(iTargetRow + 1).EntireRow.Delete
    
    
Else ''////////////////////////////////신규입력,수정
    varValues = Split(sValue, sDelimiter)
    Select Case iTargetRow
        Case 0
            Set rTargetRow = Me.TableNewRow
        Case Else
            Set rTargetRow = modMain.oProcessTable.Table.Rows(iTargetRow + 1)
            
    End Select
    If rTargetRow.Cells.Count - 1 <> UBound(varValues) + 1 Then
        MsgBox "열의 갯수에 맞게 정보를 전달하세요"
        editRow = False
    Else
        iCol = 1
        modMain.bStopEvents = True
        For Each varX In varValues
            iCol = iCol + 1
            If IsDate(varX) Then
                rTargetRow.Cells(iCol) = CDate(varX)
            ElseIf IsNumeric(varX) Then
                rTargetRow.Cells(iCol) = CInt(varX)
            Else
                rTargetRow.Cells(iCol) = varX
            End If
        Next
        If iTargetRow = 0 Then '' 신규
            ' 새행에 서식을 기존서식으로..
            rTargetRow.Cells(1) = Application.Max(rTargetRow.Cells(1).EntireColumn) + 1
            rTargetRow.Offset(-1).Copy
            rTargetRow.PasteSpecial xlPasteFormats
            Application.CutCopyMode = False
        Else ''수정
            '' 서식은 그대로 유지되고, 값만 바뀌것..
        End If
        modMain.bStopEvents = False
    End If
End If
End Function

아래화일로 실행해보시면서 수정,삭제,입력을 해보시고
실은 위의 테이블 구성은 좀 덜 세련되었다,
관계형테이블의 이해가 부족할때 초보님들이 할수 있는 방법이였다
좀더 관계형테이블을 활용하는 시각에서 개선,수정하여 다음 페이지에서 이야기하자
이렇게 엑셀시트를 테이블로 하는 것이 끝나면,
억세스테이블을 활용하는 것을 이야기 하도록 하자
그래야 억세스라는 DB가 얼마나 편리한지를 더 실감할수 있으니까..

***[LOG-IN]***