[VBA] 어제 작업해 놓은 프로그래밍 정리

영하 25도

막 출근했는데요. 주차장에 차 세워놓고 건물로 들어오는데 공기에 노출된 살들이 너무 아프더라구요.

영하 25도.. 정말 추운 날씨예요.

어제는 영하 30도까지 내려갔었던거 같은데.....

빨리 봄이 왔으면 좋겠습니다.

어제 오후에 인도에서 새로온 매니저가 엑셀일을 하다가 매뉴얼로 하기엔 너무 벅찬 일이라 저에게 프로그래밍을 부탁하더라구요.

요구 조건은 이겁니다.

모든 Sheet 에 있는 특정 컬럼에 있는 값들과 특정 Sheet에 있는 특정 컬럼에 있는 값들을 비교하라.

특정 Sheet의 특정 컬럼에 있는 값들은 몇개의 패턴으로 문자열들이 결합돼 있으니 이것을 split 한 다음에 비교해야 한다.

이걸 10시까지 작업해서 보내줬는데요.

이 작업하면서 만들어논 컴포넌트들이나 로직들을 다음에도 유용하게 사용할 수 있을 것 같아서 여기에 정리해 놓습니다.

Sub AllSheets()
    Dim ws As Worksheet
    For Each ws In Worksheets
        Call checkFieldName(ws)
    Next ws
End Sub

이 Sub는 엑셀 파일내의 모든 Sheet에 대해 For Loop를 도는 겁니다.

Loop안에는 해당 Sheet를 checkFeildName() 함수로 전달합니다.

Sub checkFieldName(ws As Worksheet)
    Dim rngCell As Range
    Dim allCell As Range

    For Each rngCell In Worksheets(ws.Name).UsedRange
        If rngCell.Value = "필드 제목 1" Or rngCell.Value = "필드 제목 2" Then
            Set allCell = Range(rngCell.Offset(1, 0), rngCell.End(xlDown))
            Call CheckReportName(ws, allCell)
        End If
    Next

End Sub

이 Sub는 전달받은 Worksheet 내에 값들이 있는 모든 Cell들에 대해 For Loop문을 돕니다.

이 Loop 안에서는 해당 Cell 의 값이 '필드 제목 1' 이거나 '필드 제목 2' 일 경우 그 Field 제목 바로 밑에서 부터 해당 필드의 값이 있는 맨 마지막 Cell을 allCell이라는 Range에 담습니다.

그리고 Worksheet과 allCell Range를 CheckReportName() 에 전달합니다.

Sub CheckReportName(ws As Worksheet, allCell As Range)
    Dim checkName As String

    For Each Cell In Sheets(ws.Name).Range(allCell.Address).Cells
        checkName = checkExist(Cell.Value)
       
        If (UCase(checkName) = "Y") Then
            Cell.Interior.Color = RGB(36, 36, 255)
        End If
       
        If (UCase(checkName) = "Y1") Then
            Cell.Interior.Color = RGB(255, 255, 143)
        End If
    Next Cell
End Sub

이 Sub에서는 전달받은 Worksheet 내의 전달받은 Range에 대해 Loop를 돕니다.

이 Loop 안에서는 해당 Cell의 값을 checkExist() 에 전달해서 결과를 return 받습니다.

이 return 받은 결과가 Y 이면 해당 셀을 파랗게 그리고 return 받은 결과가 Y1이면 해당 셀을 노랗게 만듭니다.

Public Function checkExist(cellVal As String) As String

        Dim tmpArr1 As Variant
        Dim tmpArr2 As Variant
        Dim testStr1 As String
        Dim testStr2 As String
        Dim omitText As String
        Dim checkEx As String
       
        checkEx = "N"
   
    For Each Cell In Sheets("특정 Sheet").Range("범위").Cells
   
        If InStr(1, Cell.Value, "첫번째") > 0 Then
            If InStr(1, Cell.Value, "my.Reports") > 0 Then
                omitText = Mid(Cell.Value, 13, Len(Cell.Value))
            Else
                omitText = Cell.Value
            End If
           
                tmpArr2 = Split(omitText, "첫번째")
                testStr1 = tmpArr2(0)
                testStr2 = tmpArr2(1)
               
                If InStr(UCase(testStr1), UCase(cellVal)) <> 0 Or UCase(cellVal) = UCase(testStr1) Then
                    checkEx = "Y"
                End If
               
                If InStr(UCase(cellVal), UCase(testStr1)) <> 0 Or UCase(cellVal) = UCase(testStr1) Then
                    checkEx = "Y1"
                End If
               
                If InStr(UCase(testStr2), UCase(cellVal)) <> 0 Or UCase(cellVal) = UCase(testStr2) Then
                    checkEx = "Y"
                End If
               
                If InStr(UCase(cellVal), UCase(testStr2)) <> 0 Or UCase(cellVal) = UCase(testStr2) Then
                    checkEx = "Y1"
                End If
        Else
                If InStr(UCase(Cell.Value), UCase(cellVal)) <> 0 Or UCase(cellVal) = UCase(Cell.Value) Then
                    checkEx = "Y"
                End If
               
                If InStr(UCase(cellVal), UCase(Cell.Value)) <> 0 Or UCase(cellVal) = UCase(Cell.Value) Then
                    checkEx = "Y1"
                End If
        End If
   
    Next Cell
   
   
    checkExist = checkEx
End Function

이 함수는 특정 sheet의 특정 Range에 대해 Loop를 돕니다.

이 특정 sheet의 이름과 특정 Range는 하드 코딩을 했습니다.

시간이 나면 이 부분도 프로그래밍으로 값을 받아 올 수 있도록 Refactoring 을 할 수 있겠네요.

이 Loop에서는 받은 값을 각 Cell의 값과 비교해서 checkEx(디폴트값 N)라는 String 값을 세팅합니다.

처음에 이 특정 Sheet의 특정 컬럼에 있는 값들은 특정 패턴으로 문자열이 결합돼 있어서 이를 Split 해야 한다고 했습니다.

그 Rule이 Loop 안의 If문 안에 있습니다.

우선 맨 처음 If문에서는 해당 Cell의 값이 '첫번째' 라는 문자열을 포함하는지 여부를 체크합니다.

포함하고 있으면 이 문자열의 첫번째 13글자를 제외한 값을 omitText에 담고 그렇지 않은 경우에는 문자 전체를 omitText String 변수에 담습니다.

그리고 나서 이 '첫번째' 라는 문자열을 기준으로 각 문자열들을 Split 해서 tempArr2라는 배열에 담습니다.

이 배열의 첫번째 값은 testStr1에 두번째 값은 testStr2 라는 String 변수에 담습니다.

이 두 String 을 전달받은 String 과 비교합니다.

우선 전달받은 String 에 testStr1이 포함돼 있거나 두 문자열이 같은 경우에는 checkEx의 값을 Y로 바꿉니다.

반대로 testStr1에 전달받은 문자열을 포함하고 있는 경우에는 checkEx의 값을 Y1으로 바꿉니다.

이 작업을 testStr2에도 마찬가지로 해 줍니다.

여기까지가 해당 Cell 값에 '첫번째'라는 문자열을 가지고 있는 경우이고요 이 '첫번째'라는 문자열을 가지고 있지 않은 경우에는 곧바로 해당 Cell의 값고 전달받은 값을 비교해서 Y와 Y1이라는 값을 checkEx에 세팅을 해 줍니다.

여기까지 작업을 해서 그 결과를 매니저한테 보냈는데요.

주말에 시간 나면 이 프로그램을 다시 보고 Refactoring 좀 해 봐야겠습니다.

더 간단하게 처리할 수 있는 방법은 없는지.....