Excel VBA 자습서 – Visual Basic을 사용하여 스프레드 시트에 코드를 작성하는 방법

프로그램 작성

소개

VBA (Visual Basic for Applications)를 사용하여 Excel 스프레드 시트에 코드를 작성하는 방법에 대한 자습서입니다.

Excel은 Microsoft의 가장 인기있는 제품 중 하나입니다. 2016 년 마이크로 소프트 CEO는 "엑셀이없는 세상을 생각해 보라. 그것은 나에게 불가능하다"고 말했다. 글쎄, 아마도 세상은 Excel 없이는 생각할 수 없습니다.

  • 1996 년에는 3 천만 명이 넘는 Microsoft Excel 사용자가있었습니다 (출처).
  • 현재 약 7 억 5 천만 명의 Microsoft Excel 사용자가 있습니다. 이는 유럽 인구보다 약간 더 많고 1996 년보다 25 배 더 많은 사용자입니다.

우리는 하나의 큰 행복한 가족입니다!

이 자습서에서는 VBA와 Visual Basic을 사용하여 Excel 스프레드 시트에 코드를 작성하는 방법에 대해 알아 봅니다.

전제 조건

이 자습서를 이해하기 위해 사전 프로그래밍 경험이 필요하지 않습니다. 그러나 다음이 필요합니다.

  • Microsoft Excel에 대한 기초부터 중급까지의 지식
  • 이 기사의 VBA 예제를 따르려면 Microsoft Excel, 가급적 최신 버전 (2019)에 액세스해야하지만 Excel 2016 및 Excel 2013은 정상적으로 작동합니다.
  • 새로운 것을 시도하려는 의지

학습 목표

이 기사를 통해 다음 내용을 배울 수 있습니다.

  1. VBA 란?
  2. VBA를 사용하는 이유
  3. VBA를 작성하기 위해 Excel에서 설정하는 방법
  4. VBA로 실제 문제를 해결하는 방법

중요한 개념

다음은이 튜토리얼을 완전히 이해하기 위해 알아야 할 몇 가지 중요한 개념입니다.

개체 : Excel은 개체 지향입니다. 즉, Excel 창, 통합 문서, 시트, 차트, 셀 등 모든 것이 개체라는 것을 의미합니다. VBA를 사용하면 Excel에서 개체를 조작하고 작업을 수행 할 수 있습니다.

객체 지향 프로그래밍에 대한 경험이없고 이것이 완전히 새로운 개념이라면 잠깐 시간을내어 그 점을 이해하십시오!

프로 시저 : 프로시 저는 작업을 수행하는 Visual Basic Editor로 작성된 VBA 코드 청크입니다. 때로는 매크로라고도합니다 (아래 매크로에 대한 자세한 내용). 두 가지 유형의 절차가 있습니다.

  • 서브 루틴 : 하나 이상의 작업을 수행하는 VBA 문 그룹
  • 함수 : 하나 이상의 작업을 수행하고 하나 이상의 값을 반환하는 VBA 문 그룹

참고 : 서브 루틴 내부에서 작동하는 기능을 가질 수 있습니다. 나중에 보게 될 것입니다.

매크로 : 고급 Excel 기능을 배우는 데 시간을 투자했다면 "매크로"라는 개념을 접했을 것입니다. Excel 사용자는 사용자 명령 / 키 입력 / 클릭으로 구성된 매크로를 기록하고 빠른 속도로 재생하여 반복적 인 작업을 수행 할 수 있습니다. 기록 된 매크로는 VBA 코드를 생성하며이를 검사 할 수 있습니다. 간단한 매크로를 기록한 다음 VBA 코드를 보는 것은 실제로 매우 재미 있습니다.

때때로 VBA 프로 시저를 직접 코딩하는 것보다 매크로를 기록하는 것이 더 쉽고 빠를 수 있습니다.

예를 들어 프로젝트 관리에서 일할 수 있습니다. 일주일에 한 번 프로젝트 관리 시스템에서 내 보낸 원시 보고서를 리더십을위한 아름답고 깔끔한 보고서로 바꿔야합니다. 예산 초과 프로젝트의 이름은 굵은 빨간색 텍스트로 지정해야합니다. 서식 변경 사항을 매크로로 기록하고 변경해야 할 때마다 실행할 수 있습니다.

VBA 란 무엇입니까?

Visual Basic for Applications는 Microsoft에서 개발 한 프로그래밍 언어입니다. Microsoft Office 제품군의 각 소프트웨어 프로그램은 추가 비용없이 VBA 언어와 함께 번들로 제공됩니다. VBA를 사용하면 Microsoft Office 사용자가 Microsoft Office 소프트웨어 프로그램 내에서 작동하는 작은 프로그램을 만들 수 있습니다.

VBA를 레스토랑 내부의 피자 오븐처럼 생각하십시오. Excel은 레스토랑입니다. 주방에는 대형 냉장고, 스토브 및 일반 ole '오븐과 같은 표준 상업용 가전 제품이 함께 제공되며, 모두 Excel의 표준 기능입니다.

하지만 장작불 피자를 만들고 싶다면? 표준 상업용 베이킹 오븐에서는 할 수 없습니다. VBA는 피자 오븐입니다.

Yum.

Excel에서 VBA를 사용하는 이유는 무엇입니까?

장작불 피자가 최고니까!

하지만 진지하게.

많은 사람들 이 업무의 일부로 Excel에서 많은 시간을 보냅니다 . Excel의 시간도 다르게 이동합니다. 상황에 따라 Excel에서 10 분은 필요한 작업을 수행 할 수없는 경우 영원처럼 느껴질 수 있고, 모든 것이 잘되면 10 시간은 매우 빠르게 지나갈 수 있습니다. 언제 스스로에게 물어봐야 할 때, 왜 내가 Excel에서 10 시간을 보내는가?

때로는 그 시절이 불가피합니다. 그러나 Excel에서 매일 8-10 시간을 반복 작업을 수행하거나, 동일한 프로세스를 많이 반복하거나, 파일의 다른 사용자 이후에 정리를 시도하거나, Excel 파일이 변경된 후 다른 파일을 업데이트하는 경우, VBA 절차가 해결책이 될 수 있습니다.

다음이 필요한 경우 VBA 사용을 고려해야합니다.

  • 반복적 인 작업 자동화
  • 사용자가 스프레드 시트와 상호 작용할 수있는 쉬운 방법 만들기
  • 많은 양의 데이터 조작

Excel에서 VBA를 작성하도록 설정하기

개발자 탭

VBA를 작성하려면 리본에 개발자 탭을 추가해야하므로 이와 같은 리본이 표시됩니다.

리본에 개발 도구 탭을 추가하려면 :

  1. 파일 탭에서 옵션> 리본 사용자 지정으로 이동합니다.
  2. 리본 사용자 지정 및 기본 탭에서 개발자 확인란을 선택합니다.

탭을 표시 한 후 확인란의 선택을 취소하거나 Excel을 다시 설치해야하는 경우가 아니면 개발자 탭이 계속 표시됩니다. 자세한 내용은 Microsoft 도움말 문서를 참조하십시오.

VBA 편집기

개발자 탭으로 이동하여 Visual Basic 단추를 클릭하십시오. 새 창이 나타납니다. 이것은 Visual Basic Editor입니다. 이 자습서에서는 프로젝트 탐색기 창과 속성 속성 창에 대해 잘 알고 있어야합니다.

Excel VBA 예

먼저 우리가 놀 수있는 파일을 만들어 봅시다.

  1. 새 Excel 파일 열기
  2. 매크로 사용 통합 문서 (. xlsm)로 저장
  3. 개발자 탭을 선택하십시오.
  4. VBA 편집기 열기

Visual Basic을 사용하여 스프레드 시트에 코드를 작성하는 데 도움이되는 몇 가지 간단한 예를 들어 보겠습니다.

예제 # 1 : 사용자가 Excel 통합 문서를 열 때 메시지 표시

VBA 편집기에서 삽입-> 새 모듈을 선택합니다.

모듈 창에 다음 코드를 작성하십시오 (붙여 넣기하지 마십시오!).

하위 Auto_Open ()

MsgBox ( "XYZ 통합 문서에 오신 것을 환영합니다.")

End Sub

통합 문서를 저장하고 닫은 다음 다시 엽니 다. 이 대화 상자가 표시되어야합니다.

따다!

어떻게하는 거죠?

프로그래밍에 대한 익숙한 정도에 따라 몇 가지 추측이있을 수 있습니다. 특별히 복잡하지는 않지만 많은 일이 진행되고 있습니다.

  • Sub ( "Subroutine의 약자) : 처음부터"하나 이상의 작업을 수행하는 VBA 문 그룹 "을 기억하십시오.
  • Auto_Open : 특정 서브 루틴입니다. Excel 파일이 열리면 자동으로 코드를 실행합니다. 이것은 프로 시저를 트리거하는 이벤트입니다. Auto_Open은 통합 문서를 수동으로 열 때만 실행됩니다. 통합 문서가 다른 통합 문서의 코드를 통해 열리면 실행되지 않습니다 (Workbook_Open이이를 수행합니다. 둘의 차이점에 대해 자세히 알아 봅니다).
  • 기본적으로 서브 루틴의 액세스는 공용입니다. 이것은 다른 모듈이이 서브 루틴을 사용할 수 있음을 의미합니다. 이 튜토리얼의 모든 예제는 공용 서브 루틴입니다. 필요한 경우 서브 루틴을 개인용으로 선언 할 수 있습니다. 일부 상황에서 필요할 수 있습니다. 서브 루틴 액세스 수정 자에 대해 자세히 알아보십시오.
  • msgBox : 이것은 함수-하나 이상의 작업을 수행하고 값을 반환하는 VBA 문 그룹입니다. 반환 된 값은 "XYZ 통합 문서에 오신 것을 환영합니다."라는 메시지입니다.

요컨대, 이것은 함수를 포함하는 간단한 서브 루틴입니다.

언제 이것을 사용할 수 있습니까?

드물게 (예 : 분기에 한 번) 액세스되지만 다른 VBA 절차에 의해 매일 자동으로 업데이트되는 매우 중요한 파일이있을 수 있습니다. 액세스하면 회사 전체의 여러 부서에있는 많은 사람들이 액세스합니다.

  • 문제점 : 사용자가 파일에 액세스 할 때 대부분의 경우이 파일의 목적 (존재하는 이유), 파일이 자주 업데이트되는 방법, 파일을 유지 관리하는 사람, 파일과 상호 작용하는 방법에 대해 혼란스러워합니다. 신입 사원은 항상 수많은 질문을 가지고 있으며 이러한 질문을 계속해서 반복해야합니다.
  • 해결 방법 : 이러한 자주 대답하는 질문에 대한 간결한 답변이 포함 된 사용자 메시지를 만듭니다.

실제 사례

  • MsgBox 함수를 사용하여 이벤트가있을 때 메시지를 표시합니다. 사용자가 Excel 통합 문서를 닫고, 사용자가 인쇄하고, 새 시트가 통합 문서에 추가되는 등.
  • 사용자가 Excel 통합 문서를 닫기 전에 조건을 충족해야 할 때 메시지를 표시하려면 MsgBox 함수를 사용하십시오.
  • InputBox 함수를 사용하여 사용자로부터 정보 얻기

예제 # 2 : 사용자가 다른 프로 시저를 실행하도록 허용

VBA 편집기에서 삽입-> 새 모듈을 선택합니다.

모듈 창에 다음 코드를 작성하십시오 (붙여 넣기하지 마십시오!).

하위 UserReportQuery ()

사용자 입력을 길게

Dim Answer As Integer

UserInput = vbYesNo

Answer = MsgBox ( "XYZ 보고서를 처리 하시겠습니까?", UserInput)

대답 = vbYes이면 ProcessReport

End Sub

하위 ProcessReport ()

MsgBox ( "XYZ 보고서를 처리해 주셔서 감사합니다.")

End Sub

저장하고 Excel의 개발자 탭으로 돌아가서 "버튼"옵션을 선택합니다. 셀을 클릭하고 버튼에 UserReportQuery 매크로를 할당합니다.

이제 버튼을 클릭하십시오. 이 메시지는 다음과 같이 표시되어야합니다.

"예"를 클릭하거나 Enter 키를 누르십시오.

다시 한번 타다!

보조 서브 루틴 인 ProcessReport는 무엇이든 될 수 있습니다 . 예제 # 3에서 더 많은 가능성을 보여 드리겠습니다. 하지만 먼저 ...

어떻게하는 거죠?

이 예제는 이전 예제를 기반으로하며 몇 가지 새로운 요소가 있습니다. 새로운 내용을 살펴 보겠습니다.

  • Dim UserInput As Long : Dim은 "dimension"의 약어이며 변수 이름을 선언 할 수 있습니다. 이 경우 UserInput은 변수 이름이고 Long은 데이터 유형입니다. 일반 영어로이 줄은 "여기에"UserInput "이라는 변수가 있으며 Long 변수 유형입니다."를 의미합니다.
  • Dim Answer As Integer : 데이터 유형이 Integer 인 "Answer"라는 또 다른 변수를 선언합니다. 여기에서 데이터 유형에 대해 자세히 알아보세요.
  • UserInput = vbYesNo : 변수에 값을 할당합니다. 이 경우 예 및 아니요 단추를 표시하는 vbYesNo입니다. 있습니다 많은 버튼 유형은 더 여기 배운다.
  • Answer = MsgBox ( "XYZ 보고서를 처리 하시겠습니까?", UserInput) : 변수 Answer의 값을 MsgBox 함수와 UserInput 변수로 할당합니다. 예, 변수 내의 변수입니다.
  • If Answer = vbYes Then ProcessReport : 이것은 x가 참인지 말할 수있는 조건문 인 "If 문"이고 y를 수행합니다. 이 경우 사용자가 "예"를 선택한 경우 ProcessReport 서브 루틴을 실행합니다.

언제 이것을 사용할 수 있습니까?

이것은 여러 가지 방법으로 사용될 수 있습니다. 이 기능의 가치와 다양성은 2 차 서브 루틴이하는 일에 의해 더욱 정의됩니다.

예를 들어, 3 개의 다른 주간 보고서를 생성하는 데 사용되는 파일이있을 수 있습니다. 이러한 보고서는 극적으로 다른 방식으로 형식화됩니다.

  • 문제점 : 이러한 보고서 중 하나를 생성해야 할 때마다 사용자가 파일을 열고 서식과 차트를 변경합니다. 등등 등등. 이 파일은 일주일에 3 번 이상 광범위하게 편집되고 있으며 편집 할 때마다 최소 30 분이 걸립니다.
  • 솔루션 : 보고서 유형 당 하나의 버튼을 생성하여 보고서의 필수 구성 요소를 자동으로 재구성하고 필요한 차트를 생성합니다.

실제 사례

  • 사용자가 여러 시트에 특정 정보를 자동으로 채울 수있는 대화 상자를 만듭니다.
  • InputBox 함수를 사용하여 사용자로부터 정보를 가져온 다음 여러 시트에 채워집니다.

예제 # 3 : For-Next 루프를 사용하여 범위에 숫자 추가

For 루프는 특정 값 범위 (배열 또는 셀 범위)에 대해 반복적 인 작업을 수행해야하는 경우 매우 유용합니다. 평이한 영어에서 루프는 "for each x, do y"라고 말합니다.

VBA 편집기에서 삽입-> 새 모듈을 선택합니다.

모듈 창에 다음 코드를 작성하십시오 (붙여 넣기하지 마십시오!).

하위 루프 예제 ()

Dim X As Integer

X = 1에서 100까지

범위 ( "A"& X). 값 = X

다음 X

End Sub

저장하고 Excel의 개발자 탭으로 돌아가서 매크로 버튼을 선택합니다. LoopExample 매크로를 실행하십시오.

다음과 같은 일이 발생해야합니다.

등, 100 번째 줄까지.

어떻게하는 거죠?

  • Dim X As Integer : 변수 X를 Integer의 데이터 유형으로 선언합니다.
  • For X = 1 To 100 : For 루프의 시작입니다. 간단히 말해서, X = 100이 될 때까지 계속 반복하도록 루프에 지시합니다. X는 카운터 입니다. 루프는 X = 100까지 계속 실행되고 마지막으로 한 번 실행 된 다음 중지됩니다.
  • Range ( "A"& X) .Value = X : 루프의 범위와 그 범위에 넣을 내용을 선언합니다. 처음에 X = 1이기 때문에 첫 번째 셀은 A1이되고 루프는 X를 해당 셀에 넣습니다.
  • 다음 X : 루프가 다시 실행되도록합니다.

언제 이것을 사용할 수 있습니까?

For-Next 루프는 VBA의 가장 강력한 기능 중 하나입니다. 수많은 잠재적 인 사용 사례가 있습니다. 이것은 여러 계층의 논리를 필요로하는 더 복잡한 예이지만 For-Next 루프에서 가능성의 세계를 전달합니다.

베이커리에서 판매되는 모든 제품의 목록이 A 열, B 열의 제품 유형 (케이크, 도넛 또는 머핀), C 열의 재료 비용, 각 제품 유형의 시장 평균 비용이 A 열에있을 수 있습니다. 다른 시트.

각 제품의 소매 가격이 얼마인지 파악해야합니다. 재료 가격에 20 %를 더해야하지만 가능하면 시장 평균보다 1.2 % 낮아야한다고 생각합니다. For-Next 루프를 사용하면 이러한 유형의 계산을 수행 할 수 있습니다.

실제 사례

  • 중첩 된 if 문이있는 루프를 사용하여 특정 조건을 충족하는 경우에만 특정 값을 별도의 배열에 추가합니다.
  • 범위의 각 값에 대해 수학적 계산을 수행합니다. 예를 들어 추가 요금을 계산하고 값에 추가합니다.
  • 문자열의 각 문자를 반복하고 모든 숫자를 추출합니다.
  • 배열에서 여러 값을 무작위로 선택

결론

피자와 머핀, 그리고 엑셀 스프레드 시트에서 VBA 코드를 작성하는 방법에 대해 이야기 했으니 이제 학습 점검을 해보겠습니다. 이 질문에 답할 수 있는지 확인하십시오.

  • VBA 란 무엇입니까?
  • Excel에서 VBA를 사용하도록 설정하려면 어떻게해야합니까?
  • VBA를 사용하는 이유와시기는?
  • VBA로 해결할 수있는 문제는 무엇입니까?

이러한 질문에 답할 수있는 방법에 대한 공정한 아이디어가 있다면 성공적이었습니다.

비정기 사용자이든 고급 사용자이든이 자습서가 Excel 스프레드 시트의 코드를 조금만 사용하여 수행 할 수있는 작업에 대한 유용한 정보를 제공하기를 바랍니다.

즐거운 코딩 되세요!

학습 자료

  • 초보자를위한 Excel VBA 프로그래밍, John Walkenbach
  • VBA, Microsoft 문서 시작하기
  • Excel에서 VBA 학습, Lynda

나에 대해 조금

저는 오리건 주 포틀랜드의 아티스트이자 개발자 인 Chloe Tucker입니다. 전직 교육자로서 저는 학습과 교육의 교차점 또는 기술과 예술의 교차점을 지속적으로 찾고 있습니다. Twitter @chloetucker에서 저에게 연락하고 chloe.dev에서 제 웹 사이트를 확인하세요.