[VBA] 엑셀 VBA 사용법

엑셀 VBA?

엑셀 VBA는 엑셀 매크로를 작성하기 위한 프로그래밍 언어입니다. VBA를 사용하면 매크로에 더 복잡한 기능을 추가할 수 있습니다. 엑셀의 기본 기능으로는 구현하기 어려운 작업을 수행하고자 할 때, 반복적인 작업을 자동화하여 시간을 절약하고 업무 효율을 높이고자 할 때 엑셀 VBA 추천합니다.

엑셀 개발도구 활성화

엑셀에서 VBA를 사용하기 위해서는 개발도구 메뉴를 활성화해야 아래 그림처럼 해당 기능 버튼이 활성화 됩니다.

그럼 엑셀에서 VBA를 사용하기 위한 개발도구 메뉴를 활성화 하는 방법을 알아보겠습니다.

1단계. VBA 기능 추가하기

아래 경로의 메뉴에 진입하여 "분석 도구", "분석 도구 - VBA"를 체크하고 확인을 누릅니다.

파일->옵션->추가기능->이동 버튼 클릭 후 추가 기능 팝업창에서 추가 기능 추가

2단계. 메뉴에 개발 도구 추가하기

아래 경로의 메뉴에 진입하여 "개발 도구"를 체크하고 확인을 누릅니다.

파일->옵션->리본 사용자 지정->개발 도구에 체크 후 확인

3단계. 편집창 설정

편집창 옵션에서 "자동 구문 검사" 옵션을 해제해야 합니다. 그렇지 않으면 오타가 발생할 때마다 경고 팝업이 발생해서 정상적인 타이핑이 불가능 합니다.

또한 디버깅 메시지를 확인할 수 있는 "직접 실행"창과 디버깅시 각 변수 저장값을 확인할 수 있는 "지역"창을 띄웁니다.

개발도구->Visual Basic 버튼을 눌러 편집기를 띄웁니다.

위 내용까지 따라했으면 편집창은 아래와 같은 모습입니다.

4단계. xlsx 파일 포맷을 .xlsm 파일 포맷으로 변경 저장

파일 확장자가 .xlsx인 Excel 통합 문서 양식에서는 VBA 매크로를 작성할 수 없습니다. 다른 이름으로 저장하기 창을 띄워서 파일 확장자가 .xlsm인 Excel 매크로 사용 통합 문서로 변경 저장합니다.

5단계. 테스트 매크로 작성 및 확인

테스트 매크로 코드를 작성하고 결과를 확인해 봅니다. 매크로 동작 버튼을 만들고 버튼을 클릭하면 Debug.Print와 MsgBox를 활용하여 메시지 출력을 해보겠습니다.

엑셀 메뉴 삽입-도형에서 원하는 도형을 워크시트에 삽입하여 아래와 같이 클릭할 수 있게 만들어 둡니다.

그리고 그 옆에 대충 아래와 유사하게 샘플 데이터를 입력합니다. 이번 예제에서는 Option Value를 변수로 읽어와서 Debug.Print와 MsgBox를 활용하여 출력해 보겠습니다.

아 그리고 워크시트명은 "vba"로 해둡니다.

버튼역할을 할 도형을 우클릭하여 "매크로 지정" 창을 띄웁니다.

매크로 지정창의 매크로 이름란에 "test"라고 입력 후 "새로 만들기"를 클릭합니다.

그럼 아래와 같이 VBA 창이 뜰겁니다. Sub test() ~ End Sub 사이에 테스크 코드를 작성할 겁니다. 테스트코드를 복사해서 붙여 넣어 보세요.

참고로 들여쓰기는 Tab 버튼으로 합니다.

    Dim shtThisVBA As Worksheet
    Set shtThisVBA = ThisWorkbook.Worksheets("vba")
    
    Dim strTargetFilePath As String
    Dim strTargetFileName As String
    Dim strTargetWorksheetName As String
    Dim lEntryRowNo As Long

    strTargetFilePath = shtThisVBA.Range("E4").Value
    strTargetFileName = shtThisVBA.Range("E5").Value
    strTargetWorksheetName = shtThisVBA.Range("E6").Value
    lEntryRowNo = shtThisVBA.Range("E7").Value
    
    Dim strTargetFullPath As String
    strTargetFullPath = strTargetFilePath + "\" + strTargetFileName
    
    Debug.Print "[INFO] strTargetFilePath: " & strTargetFilePath
    Debug.Print "[INFO] strTargetFileName: " & strTargetFileName
    Debug.Print "[INFO] strTargetWorksheetName: " & strTargetWorksheetName
    Debug.Print "[INFO] strTargetFullPath: " & strTargetFullPath
    
    MsgBox "[INFO] lEntryRowNo: " & vbNewLine & _
            lEntryRowNo, vbCritical

위 코드 입력 후 실행-Sub/사용자 정의 폼 실행 또는 F5를 누르시면 매크로가 실행되고 결과는 아래와 같습니다.

테스트코드의 기본적인 내용 간략히 설명드리고 마무리 하겠습니다.

    Dim shtThisVBA As Worksheet
    Set shtThisVBA = ThisWorkbook.Worksheets("vba")

Dim 은 변수선언을 뜻합니다. 문법은 Dim <변수명> As <변수타입> 입니다.

Worksheet 변수타입의 shtThisVBA 변수를 선언하였습니다.

그리고 ThisWorkbook.Worksheets("vba")로 현재 열려있는 엑셀문서에서 vba 워크시트의 객체를 shtThisVBA에 저장하였습니다. ThisWorkbook 객체는 현재 엑셀 문서를 가리킵니다. ThisWorkbook.Worksheets는 현재 엑셀문서내의 워크시트를 반환하는 메서드인데 그 파라미터로 워크시트명을 전달해주면 그 워크시트의 워크시트 타입 객체변수를 반환해줍니다.

변수타입에는 객체타입변수와 일반변수가 있습니다. 일반변수는 그 값을 문자 또는 숫자로 표기 가능한 숫자나 문자 타입이고 엑셀문서, 워크시트와 같이 어떤 것을 가리키는 역할의 변수를 객체변수라고 합니다. 객체변수의 값을 바꿀 때는 객체변수 앞에 Set 을 붙여줘야 합니다.

이제 shtThisVBA 워크시트 객체변수를 통하여 vba 워크시트에 접근할 수 있게 되었습니다. 그 다음으로는 vba 워크시트에 입력해둔 옵션값을 읽어와 보겠습니다.

    Dim strTargetFilePath As String
    Dim strTargetFileName As String
    Dim strTargetWorksheetName As String
    Dim lEntryRowNo As Long

    strTargetFilePath = shtThisVBA.Range("E4").Value
    strTargetFileName = shtThisVBA.Range("E5").Value
    strTargetWorksheetName = shtThisVBA.Range("E6").Value
    lEntryRowNo = shtThisVBA.Range("E7").Value

먼저 읽어올 값을 저장할 변수를 선언합니다. 테스트 샘플 페이지에서 Option Value를 4개 써놨는데 3개는 문자열(텍스트)이고 1개는 숫자입니다. 그러므로 문자열 변수를 3개 선언하고 양의정수 타입 변수를 1개 선언합니다.

변수선언은 Dim으로 한다고 말씀드렸습니다. 그리고 문자타입변수는 String 이고 양의정수 타입은 Long 입니다.

이로서 상단 4줄은 설명이 완료 되었습니다.

Range 메서드는 셀범위를 지정하는 메서드입니다. Range("E4")는 E4셀을 지정하는데 그뒤에 .Value를 붙이면 E4셀에 저장된 값을 읽게 됩니다.

strTargetFilePath = shtThisVBA.Range("E4").Value 는 결국 shtThisVBA 워크시트 객체 변수가 가리키는 vba 워크시트의 E4 셀에 저장된 값을 읽어서 strTatgetFilePath 변수에 저장하는 코드입니다.

마찬가지로 E5, E6의 문자열을 읽어서 해당 변수에 저장하였습니다.

그리고 lEntryRowNo는 Long 타입 변수입니다. 이 변수에는 셀 E7에 들어있는 숫자를 읽어서 저장하였습니다.

E4와 E5는 경로와 파일명을 의미하는 문자열인데 이 두 문자열을 더해 하나의 문자열로 만들어 보겠습니다.

Dim strTargetFullPath As String
strTargetFullPath = strTargetFilePath + "\" + strTargetFileName

이렇게 작성하면 두 문자열이 합쳐지고 중간에 역슬래시('\')가 추가되어 파일명을 포함한 전체 경로를 만들 수 있습니다.

이제 변수에 값이 제대로 들어갔는지 디버깅 메시지로 확인할 차례입니다.

    Debug.Print "[INFO] strTargetFilePath: " & strTargetFilePath
    Debug.Print "[INFO] strTargetFileName: " & strTargetFileName
    Debug.Print "[INFO] strTargetWorksheetName: " & strTargetWorksheetName
    Debug.Print "[INFO] strTargetFullPath: " & strTargetFullPath
    
    MsgBox "[INFO] lEntryRowNo: " & vbNewLine & _
            lEntryRowNo, vbCritical

Debug.Print 를 사용하면 코드 편집기의 "직접 실행" 창에 메시지를 출력할 수 있습니다.

변수는 변수명을 그대로 쓰고, 문자열은 쌍따옴표 사이에 기입하면 됩니다. 문자열과 변수를 같이 출력하려면 그 중간에 & 문자를 두면 됩니다.

MsgBox를 사용하면 팝업 메시지창을 발생시킵니다. Debug.Print와 마찬가지로 문자열은 쌍따옴표 사이에 쓰고, 변수는 & 문자로 연결합니다.

여기까지 엑셀에서 VBA를 활용하여 엑셀 매크로를 작성하는 방법을 알아보았습니다.

'프로그래밍 > Excel VBA' 카테고리의 다른 글

[VBA] 날짜 중복 검출하기(DATE OVERLAP) (0)	2023.12.10