개발하는 디자이너의 DIY 일상

vba를 이용하여 자동화를 하기 위하여는 어떤 범위에 있는 대상에 대하여 작업이 이루어 지는지를 설정 해주어야 합니다. 보통 range 라는 개체를 이용하여 설정을 해주게 되는데 이 range 를 현재 엑셀 시트에서 사용자가 선택한 영역으로 하고 싶은 경우가 있습니다.

선택을 필요로 하는 대상 셀이 한정 적이거나 자동화 대상이 필요에 따라 자주 변경되는 경우에는 사용자가 선택한 영역을 이용하여 range 를 설정해주는 것이 편리하기도 합니다.

엑셀 시트에서 특정 영역을 선택한 다음 vba 에디터 창으로 가서 아래와 같이 입력한 후 F5 를 눌러 실행해 봅시다.

Sub selectiontest()

    Dim currentSelection As Range
    Dim row_Count As Integer
    Dim column_count As Integer
    
    Set currentSelection = Selection.Cells()
    
    row_Count = currentSelection.Rows.Count
    column_count = currentSelection.Columns.Count
    
    MsgBox (row_Count) '현재선택한 셀의 세로 줄 수
    MsgBox (column_count) '현재선택한 셀의 가로 줄 수
    

End Sub

현재 선택한 셀의 가로 줄 수와 세로 줄 수가 팝창으로 안내가 됩니다.

선택한 모든 셀에 대하여 무엇인가를 해야 한다면 For 구분을 통하여 실행이 가능합니다.

여기서 한번 응용을 해보도록 하겠습니다.

특정 셀안에 색상을 표기하는 16진수 값이 들어있을 때 해당 값을 이용하여 섹의 색상을 칠한다고 하면 아래와 같이 하면 되겠습니다.

먼저 필요한 명령들을 보면 아래와 같습니다. 

선택 영역을 지정하는 함수 : Selection.Cells()

문자열의 일부 영역을 잘라내는 함수 : LEFT(string, number), MID(string,from, number), RIGHT(string, number)

16진수 값을 10진수로 변경해주는 함수 : WorksheetFunction.Hex2Dec(value)

셀의 배경색상을 지정하는 함수 .Interior.Color = RGB(255,255,255)

엑셀의 특정 영역에 16진수 색상 값을 적어 보겠습니다.

해당 셀을 영역으로 선택한 뒤 아래의 코드를 vba 에디터 창에 입력한 후 F5 를 눌러봅시다.

위에 설명했던 코드들이므로 한번 훓어 보시면 되겠습니다.

Sub selectiontest_setCellColor()

    Dim currentSelection As Range
    Dim row_Count As Integer
    Dim column_count As Integer
    
    Set currentSelection = Selection.Cells()
    
    row_Count = currentSelection.Rows.Count
    column_count = currentSelection.Columns.Count
    
    For Each c In currentSelection
        
        Dim clr_red As Integer
        Dim clr_green As Integer
        Dim clr_blue As Integer
        
        clr_red = WorksheetFunction.Hex2Dec(Mid(c.Value, 2, 2))
        clr_green = WorksheetFunction.Hex2Dec(Mid(c.Value, 4, 2))
        clr_blue = WorksheetFunction.Hex2Dec(Right(c.Value, 2))
        
        c.Interior.Color = RGB(clr_red, clr_green, clr_blue)
        
    Next

End Sub

짜잔. 요렇게 색상이 칠해졌습니다.

물론 당연히 선택한 셀이 아닌 기존 방식대로 특정 영역의 모든 셀에 대하여도 동일하게 적용이 가능합니다.

코딩을 하는 이유는 많은 작업을 쉽고 빠르며 정확하게 하기 위함이므로 위와 같은 칸이 수천개가 있다면 당연히 코딩을 하는 것이 좋은 방법이 되겠습니다.

디자이너분들이라고 겁먹지 마시고 과감히 코딩을 배워봅시다.

이상입니다.

엑셀을 사용하는 목적 자체가 근본적으로 문서를 만들기 위함은 아니다 보니 세로 또는 가로로 굉장히 길게 데이터가 나열되어 있는 경우가 많습니다. 우리는 vba 를 이용하여 자동화를 할 것이기 때문에 어디부터 어디까지 자동화를 할것인지 영역을 설정을 해주어야 할 필요가 있는데요. 일반적으로 제가 자주 사용하는 패턴을 몇개 올려보려 합니다.

우선 alt+F11 을 눌러 vba 편집창을 열어줍니다.

좌측 트리구조에서 현재 엑셀 파일에 포함되어 있는 시트의 이름과 마지막에 ThisWorkbook 이라는 트리가 보일텐데요. 저는 보통 ThisWorkbook 라는 부분에 코드를 작성합니다. 왜나하면 현재 엑셀 파일내의 모든 시트에 자동화를 하기에 적당한 위치이기 때문입니다. 대신 스크립트 동작을 위하여 어떤 시트에서 동작을 할 것인지를 설정 해 주어야 합니다. 해당 위치를 더블클릭하면 스크립트 에디터 창이 열리게 됩니다.

아래는 제가 가장 많이 사용하는 패턴의 시트, 영역 설정하는 코드 입니다.

Sub checkError()

    Dim asht As Worksheet
    Dim rngA As Range
    
    Set asht = ActiveSheet
    
    Set rngA = asht.Range("a3")
    Set rngA = asht.Range(rngA, rngA.End(xlDown))
        
End Sub

Dim 은 엑셀에서 변수를 정의할때 사용하는 명령어 고요. Dim 뒤에 있는 asht 가 변수 명이 되겠습니다.

뒤에 As Worksheet 라는 것은 앞에 선언한 asht 가 워크시트를 저장할 변수임을 알려주게 됩니다.

역시 rngA 는 변수이며 Range 즉 엑셀의 어떤 영역을 지칭합니다. 그 아래에 set 으로 시작하는 부분에서 위에 정의한 변수에 실제 유의미한 정보를 설정해 주게 됩니다.

asht 는 현재 활성화된 워크시트를,

rngA 는 일단 현재 시트의 a3 칸을 지정한 뒤 다시 a3 으로부터 아래 마지막까지를 설정하는 명령입니다.

이렇게 하면 특정 위치로부터 행의 가장 아래까지 설정이 가능합니다. 행이 몇행까지 있는지 알지 못하거나 경우에따라 변경이 될 수 있는 상황에서 이렇게 작성하면 일일이 코드 수정을 하지 않아도 되는 장점이 있습니다. 

이런식으로 작성할 때 가끔 문제가 발생할 수 있는 여지가 있는데요, a 열에 어떤 행이 중간에 값이 비어 있을때 입니다. Range.End(xlDown) 이라는 명령은 값이 있는 마지막까지를 탐색하기 때문이죠. 매번 마지막 행을 확인할 필요가 없으니 편리한 기능이기는 한데 기준이 되는 a 열의 중간에 값이 비어있을 수도 있다면 이렇게는 안됩니다. (중간에 빈칸이 필요한 경우 의미 없는 문자열(예> N/A)을 삽입하여 자동화 시 걸러내는 방법을 사용하는 것이 좋습니다)

만약 시작부터 끝을 알고 있는 경우, 또는 빈 칸이 포함되어 있는 경우 아래와 같이 정의를 해주어야 하죠.

Dim rngA As Range
Set rngA = asht.Range("a3","a3000")

이렇게 하면 a3 ~ a3000 까지 각 셀에 값이 있건 없건 영역으로 설정이 됩니다.

제가 소스 코드를 ThisWorkbook 에 작성을 한다고 하였고 그 때문에 스크립트가 동작할 시트가 어떤 시트인지를 지정해주어야 한다고 하였는데요. 만약 특정 시트에 바로 스크립트를 작성할 것이라면 아래와 같이 간단하게 영역을 설정 할 수 있습니다.

Sub checkError()

    Dim rngA As Range
    Set rngA = [a3:a3000]

End Sub

바로 대괄호 [ ] 를 이용하여 설정하는 방법입니다. 동일하게 a3 ~ a3000 까지를 설정하는 코드입니다. 시트는 현재 이미 선택이 되어 있기 때문에 별도로 시트를 설정할 필요가 없죠. 

특정 셀 하나를 설정하려면 아래와 같이하면 됩니다.

Sub checkError()

    Dim rngA As Range
    Set rngA = [a125]

End Sub

저는 대부분의 자동화 코드를 이런식으로 시작합니다. 

그 아래 부분에 For 구문을 이용하여 필요한 기능을 적용할 수 있습니다.

다음 편에서는 For 구문을 이용하여 반복 처리하는 부분과 offset 이라는 기능을 이용하여 선택된 셀로 부터 특정 위치에 있는 셀을 지정하는 방법을 알아보겠습니다.

2019/07/12 - [DEV/VBA] - [vba] For 구문 이용하기와 offset 사용 해 보기

2020/02/05 - [DEV/VBA] - [VBA] 셀의 넓이를 픽셀로 지정하기 (set column width by pixels)