ChatGPT로 엑셀 업무 자동화2 - VBA 편집기 사용하기

김철수, <챗GPT와 업무자동화>, 위키북스, 2023.

부제 : 1분 만에 끝내는 엑셀, 구글 시트, 파워포인트, 파이썬 자동화 with ChatGPT

 

위 책 보면서 엑셀 업무 자동화 연습하기!

엑셀 2번째 주제로 VBA 편집기 이용하는 방법!

세상에 세상에..... Visual Basic이라니....... 초등학생 때 꾸역꾸역 수업들었던 비주얼 베이직을 지금 2024년에 만날 줄이야!

 

핵심!!!

*VBA 코드를 익히는 것이 목적이 아님★★
ChatGPT에 물어보고 대략적인 내용과 뭘 어떻게 수정하면 되는지만 알면 됨

여기에서는 '모듈'만 사용!

 

 

엑셀에 개발도구>VBA 편집기를 찾지 못하겠다면?

1. 우선, '개발 도구' 탭이 보이지 않을 때

엑셀 상단의 탭 빈 공간에서 오른쪽 마우스 누르고

"리본 메뉴 사용자 지정" 선택.

엑셀 개발도구 VBA편집기 안 보일 때

2. 위와 같은 화면에서 '개발 도구' 앞에 체크해준다.

 

 

 

VBA 편집기 여는 방법

1. 개발도구 탭 > 가장 왼쪽의 "Visual Basic" 메뉴 클릭

2. 아래 같은 화면이 뜬다.

여기서 작업을 할 관련있는 워크시트 위에서 

오른쪽 마우스 > 삽입 > '모듈'을 선택

VBA편집기

 

 

 

양식 통일시키기 연습!
날짜 맞추기 조건

 

연습용 워크시트의 H열에 날짜들이 나열되어 있는데,

날짜인 것과 날짜가 아닌 것들이 섞여 있다.

예를 들어서, 2023-12-42, 2023-13-24 는 존재할 수 없기 때문에 날짜가 아니다.

 

연습 문제!

2023-01-28이 최종일이라 가정하고,

▶ 날짜인데, 2023-01-28 이후의 날짜는 2023-01-28로 모두 바꾼다.

▶ 날짜가 아닌 것은 2023-01-28로 모두 바꾼다.

 

먼저 ChatGPT에 물어보자!

아래처럼 VBA 코드를 알려줬다.

Sub ChangeDate()
    ' 워크시트 설정
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("통합시트")
    
    ' 변경하고자 하는 날짜 설정
    Dim targetDate As Date
    targetDate = DateSerial(2023, 1, 28)
    
    ' H열에 대해 각 셀을 확인
    Dim cell As Range
    For Each cell In ws.Range("H2:H" & ws.Cells(ws.Rows.Count, "H").End(xlUp).Row)
        ' 셀의 값이 날짜 형식이면서 targetDate 이후이거나 날짜 형식이 아닌 경우
        If (IsDate(cell.Value) And cell.Value > targetDate) Or Not IsDate(cell.Value) Then
            ' 셀의 값을 targetDate로 변경
            cell.Value = targetDate
        End If
    Next cell
End Sub

 

2. 처음 시작할 때 책에 나온 핵심을 인용하여 말했지만,

엑셀 자동화 업무에 적용하기 위한 수준에서는 이게 무슨 코드인지 다 알 필요는 없다.

 

대~~~충 개념만 알면 도리 것 같은데

공통적으로 Dim, As 라는 코드가 보인다.

Dim ws As Worksheet

Dim cell As Range

• Dim : Dimension 치수, 크기, 차원
• Dim 뒤에 임의로 만든 변수 이름이 나온다.
• As 다음에 변수의 유형

3. 그러면 VBA 코드에서 변수의 유형은 무엇이 있는지 ChatGPT에 물어볼까?!

VBA에서 사용할 수 있는 주요 변수 유형들은 다음과 같습니다:

 

변수 유형	설명	범위
Byte	부호 없는 8비트 값	0 ~ 255
Integer	부호 있는 16비트 정수	-32,768 ~ 32,767
Long	부호 있는 32비트 정수	-2,147,483,648 ~ 2,147,483,647
Single	부호 있는 싱글 프리시전 실수 (32비트 부동소수점 수)	-3.402823E38 ~ -1.401298E-45, 0, 1.401298E-45 ~ 3.402823E38
Double	부호 있는 더블 프리시전 실수 (64비트 부동소수점 수)	-1.79769313486232E308 ~ -4.94065645841247E-324, 0, 4.94065645841247E-324 ~ 1.79769313486232E308
Currency	화폐 단위로 표현하는 64비트 숫자	-922,337,203,685,477.5808 ~ 922,337,203,685,477.5807
Decimal	96비트 숫자	+/- 79,228,162,514,264,337,593,543,950,335
Boolean	불리언 값	True, False
String	문자열	0 ~ 약 20억 개의 문자
Date	날짜 및 시간	100/1/1 ~ 9999/12/31
Object	객체 참조	모든 객체 타입
Variant	모든 유형의 데이터를 저장할 수 있으며, 기본적으로는 Empty 상태	모든 범위
User-Defined (사용자 정의)	사용자가 정의한 타입	사용자 정의

이 표는 VBA에서 사용되는 대표적인 데이터 타입들을 정리한 것입니다. 실제로는 이 외에도 다양한 데이터 타입이 사용될 수 있습니다. 각 변수 유형의 범위는 해당 변수가 저장할 수 있는 값의 범위를 나타냅니다.

 

Range는 '행열 : 시트에서 선택한 범위'를 의미한다.

 

4. VBA 편집기 '모듈'에 넣고 실행!

중요한 것은 실행하기 전에 함수가 입력된 창을 한 번 클릭하고 

실행 버튼을 눌러야 한다.

 

5. 결과 확인

아래처럼 2024-01-22과 같이 2023-01-28 이후 날짜는 2023-01-28로 변경되었다.

날짜가 아닌 것은(32일) 2023-01-28로 역시 변경되었다.

 

ChatGPT에 아래 질문들도 해서 VBA 코드 요청할 수 있다.

엑셀 X열에 숫자가 있다. 평균과 표준편차를 이용해 최대 이상치를 찾아 셀 배경을 노란색으로 칠하는 VBA 코드를 짜 줘.

 

 

여러 엑셀 파일을 하나로 합치기

 

어떤 폴더에 엑셀 파일이 여러 개 있다. 이 파일을 하나씩 열어서 첫 번째 시트에 있는 내용을 복사해서 하나로 합치는 vba 코드를 짜 줘.

뤼튼 챗GPT에 물어보기

특징!! 챗GPT는 앞의 질문과 대답을 기억한다.

먼저 제시해 준 코드를 사용했을 때 제대로 작동하지 않았다면

ChatGPT와 대화해가면서 코드를 수정해서 받을 수 있다.

통합시트를 개별시트로 쪼개서 파일 만들기

 

아래처럼 ChatGPT에게 물어볼 수 있다.

엑셀 B열에 지점명이 여러 가지 있다. 각 지점명으로 엑셀 시트를 새로 만들어서 해당 지점명의 데이터만 입력하게 하는 VBA 코드를 만들어 줘. 첫 행도 모든 시트에 복사해줘

 

엑셀에 있는 모든 시트를 특정 폴더에 각각의 엑셀 파일로 저장하는 VBA 코드를 짜 줘.