엑셀에서 입금여부를 O와 X로 구분해두면 필터를 이용해 미입금 건만 확인할 수 있습니다.
저도 처음에는 필요할 때마다 입금여부 열의 필터를 열고 X만 선택해서 미입금 건을 확인했습니다.
데이터가 많지 않을 때는 이것만으로도 충분했습니다.
하지만 실제 업무에서는 미입금만 확인하는 것이 아니라 정보오류, 고지 미완료 등 여러 조건을 반복해서 확인하게 됩니다.
그때마다 각 열을 찾아 필터를 설정하고 다시 해제하는 작업이 생각보다 번거로웠습니다.
그래서 자주 사용하는 조회 조건부터 VBA 버튼으로 만들어 한 번의 클릭으로 확인할 수 있도록 변경했습니다.
이번 글에서는 앞에서 만든 입금여부 열을 이용해 엑셀 VBA 미입금 조회 버튼을 만드는 방법을 정리해보겠습니다.
필터만 사용해도 미입금 건을 확인할 수 있습니다
앞 글에서는 입금여부 열에서 X만 선택해 미입금 건을 확인하는 방법을 정리했습니다.
← 이전 글 | 엑셀에서 미입금 건만 필터로 빠르게 확인하는 방법
기본적인 방법은 간단합니다.
- 입금여부 열의 필터 열기
- 전체 선택 해제
- X 선택
- 확인
그러면 원본 데이터를 별도로 옮기거나 미입금 목록을 다시 만들지 않아도 현재 입금이 완료되지 않은 데이터만 화면에 표시할 수 있습니다.
이 정도라면 굳이 VBA가 필요하지 않을 수도 있습니다.
하지만 같은 작업을 반복해서 사용한다면 이야기가 조금 달라집니다.
반복해서 사용하는 필터는 버튼으로 만들면 편리합니다
제가 관리하는 엑셀 파일에서는 미입금 확인 외에도 여러 가지 검증 작업이 필요했습니다.
예를 들어 미입금 여부를 확인했다가 다시 전체 데이터를 표시하고, 다른 오류를 확인하기 위해 또 다른 열의 필터를 설정하는 식입니다.
처음에는 각각의 작업이 몇 초밖에 걸리지 않기 때문에 크게 불편하다고 생각하지 않았습니다.
하지만 데이터가 늘어나고 확인해야 할 항목도 많아지면서 같은 필터를 반복해서 설정하는 작업 자체를 줄이는 것이 낫겠다고 생각했습니다.
그래서 자주 사용하는 조건은 버튼으로 만들기 시작했습니다.
버튼을 한 번 누르면 입금여부가 X인 데이터만 표시되고, 전체 데이터를 보고 싶을 때는 필터를 초기화하는 방식입니다.
직접 필터 설정 → 반복되는 조건 확인 → VBA 버튼으로 자동화
제가 VBA를 사용하는 이유도 복잡한 프로그램을 만드는 것보다는 이런 반복 작업을 조금씩 줄이기 위해서입니다.
VBA 코드를 입력할 준비를 합니다
이번 글에서는 VBA 편집기 사용법 자체보다는 미입금 건을 버튼으로 조회하는 기능에 집중하겠습니다.
따라서 다음 작업이 가능한 상태를 기준으로 설명합니다.
- VBA 편집기 열기
- 표준 모듈 추가하기
- VBA 코드를 모듈에 입력하기
- 파일을 Excel 매크로 사용 통합 문서(
.xlsm)로 저장하기
기본적인 순서는 다음과 같습니다.
Alt + F11 → 삽입 → 모듈 → 코드 입력 → .xlsm 형식으로 저장
VBA를 처음 사용하거나 위 과정이 익숙하지 않다면 아래 글에서 VBA 사용 준비부터 먼저 확인할 수 있습니다.
VBA 기본 사용법 → | 엑셀 VBA 처음 시작하기 – 편집기 열기부터 모듈 추가와 xlsm 저장까지
VBA 버튼을 만들기 전에 데이터 구조를 확인합니다
이번 예제에서는 앞 글에서 사용한 데이터 구조를 그대로 사용합니다.
예를 들어 다음과 같은 열이 있다고 가정하겠습니다.
건물명 / 호 / 전입전출구분 / 기준날짜 / 이사정산금액 / 입금액 / 차액 / 입금날짜 / 입금자명 / 입금여부 / 비고
여기에서 VBA가 입금액과 정산금액을 다시 계산하도록 만들지는 않습니다.
이미 엑셀 수식에서 입금여부를 O와 X로 판단하고 있기 때문입니다.
VBA 버튼의 역할은 단순합니다.
입금여부 열에서 X인 데이터만 화면에 보여주는 것입니다.
이렇게 수식의 역할과 VBA의 역할을 분리해두면 나중에 기준을 변경하거나 오류를 확인할 때도 구조를 이해하기 쉽습니다.
엑셀 표 이름과 입금여부 열을 확인합니다
이번 예제에서는 데이터가 엑셀 표로 만들어져 있고 표 이름을 tblMove라고 가정하겠습니다.
입금여부 열의 이름은 입금여부입니다.
표 안의 셀을 하나 선택하면 상단의 테이블 디자인 메뉴에서 현재 표 이름을 확인할 수 있습니다.
자신의 표 이름이 Table1처럼 다르게 되어 있다면 아래 VBA 코드의
tblMove
부분을 자신의 실제 표 이름으로 변경해야 합니다.
마찬가지로 입금여부라는 열 이름도 실제 Excel 파일의 열 제목과 동일해야 합니다.
표 이름과 열 이름이 코드와 실제 파일에서 일치하는지 먼저 확인하는 것이 중요합니다.
미입금 건만 조회하는 VBA 코드를 입력합니다
앞에서 준비한 표준 모듈에 다음 코드를 입력합니다.
Sub 입금미확인_조회()
Dim ws As Worksheet
Dim tbl As ListObject
Dim fieldNo As Long
Set ws = ActiveSheet
Set tbl = ws.ListObjects("tblMove")
fieldNo = tbl.ListColumns("입금여부").Index
tbl.Range.AutoFilter Field:=fieldNo, Criteria1:="X"
End Sub
VBA 코드가 하는 일은 생각보다 단순합니다
처음 VBA 코드를 보면 어렵게 느껴질 수 있지만 이번 코드의 역할은 단순합니다.
Set ws = ActiveSheet
현재 보고 있는 시트를 작업 대상으로 지정합니다.
Set tbl = ws.ListObjects("tblMove")
현재 시트에서 tblMove라는 이름의 엑셀 표를 찾습니다.
fieldNo = tbl.ListColumns("입금여부").Index
표에서 입금여부라는 열이 몇 번째에 있는지 찾습니다.
마지막으로
tbl.Range.AutoFilter Field:=fieldNo, Criteria1:="X"
입금여부 열에서 X인 데이터만 표시하도록 필터를 적용합니다.
결국 사람이 직접 하던
입금여부 필터 열기 → X 선택 → 확인
과정을 VBA가 대신 실행하는 것입니다.
열 번호 대신 열 이름을 찾도록 만든 이유
간단한 VBA 예제를 보면 특정 열 번호를 직접 지정하는 코드도 볼 수 있습니다.
예를 들어 입금여부가 표의 10번째 열이라면 다음처럼 만들 수도 있습니다.
Field:=10
현재 상태에서는 정상적으로 작동합니다.
하지만 나중에 표 중간에 새로운 열을 추가하면 입금여부 열이 11번째나 12번째로 이동할 수 있습니다.
VBA에는 여전히 Field:=10이라고 되어 있기 때문에 엉뚱한 열에 필터가 적용될 수 있습니다.
그래서 이번 예제에서는
tbl.ListColumns("입금여부").Index
를 사용해 입금여부라는 열의 현재 위치를 VBA가 직접 찾도록 했습니다.
실무에서 사용하는 Excel 파일은 처음 만든 구조 그대로 계속 사용하는 것보다 업무에 따라 열이 추가되거나 위치가 변경되는 경우가 있습니다.
그래서 저는 가능하면 고정된 열 번호보다 열 이름을 기준으로 찾는 방식을 사용하는 편입니다.
엑셀 시트에 입금미확인 버튼을 만듭니다
코드가 준비되었다면 이제 시트에서 클릭할 버튼을 만들면 됩니다.
개발 도구 → 삽입 → 단추(양식 컨트롤)
을 선택하고 원하는 위치에 버튼을 만듭니다.
버튼을 만들면 매크로 지정 창이 나타납니다.
여기에서 방금 만든
입금미확인_조회
매크로를 선택합니다.
버튼에 표시되는 이름은 사용하기 편하게 정하면 됩니다.
저는 버튼을 봤을 때 기능을 바로 알 수 있도록 입금미확인처럼 짧은 이름을 사용하는 편입니다.
이제 버튼을 누르면 입금여부가 X인 데이터만 화면에 표시됩니다.
필터 초기화 버튼도 함께 만들면 편리합니다
미입금 건을 확인한 뒤 다른 작업을 하려면 적용된 필터를 다시 해제해야 합니다.
따라서 실제 업무에서는 조회 버튼뿐만 아니라 필터 초기화 버튼도 같이 만들어두는 것이 편리합니다.
표준 모듈에 다음 코드를 추가합니다.
Sub 필터초기화()
Dim ws As Worksheet
Set ws = ActiveSheet
If ws.FilterMode Then
ws.ShowAllData
End If
End Sub
이 매크로도 별도의 버튼에 연결합니다.
그러면 실제 사용 과정은 매우 단순해집니다.
입금미확인 버튼 → 미입금 건 확인 → 필터초기화 버튼 → 전체 데이터 확인
필터 메뉴를 반복해서 열고 닫을 필요가 없어집니다.
VBA 자동화는 작은 반복 작업부터 시작하는 것이 좋았습니다
VBA라고 하면 처음부터 복잡한 프로그램을 만들어야 할 것처럼 느껴질 수 있습니다.
하지만 제가 실제 업무에서 VBA를 사용하면서 먼저 효과를 본 것은 이런 작은 반복 작업이었습니다.
한 번의 필터 설정은 몇 초밖에 걸리지 않습니다.
하지만 매일 같은 조건을 찾고, 필터를 해제하고, 다시 다른 조건을 찾는 작업이 반복되면 작은 불편이 계속 쌓입니다.
특히 데이터와 확인해야 할 조건이 많아질수록 어떤 열에서 어떤 조건을 선택해야 하는지 기억하는 것보다 필요한 기능의 버튼을 눌러 결과를 확인하는 방식이 편리했습니다.
잘못된 열이나 조건을 선택하는 실수를 줄이는 데도 도움이 됐습니다.
그래서 저는 모든 작업을 VBA로 자동화하기보다는 반복 횟수가 많고 조건이 명확한 작업부터 버튼으로 바꾸는 방식을 사용하고 있습니다.
미입금 조회 버튼에서 다른 검증 버튼으로 확장할 수 있습니다
이번 글에서는 가장 단순한 예제로 입금여부가 X인 데이터만 조회하는 VBA 버튼을 만들었습니다.
하지만 실제 업무에서는 하나의 조건만 확인하지 않습니다.
정보가 잘못 입력된 데이터, 고지가 완료되지 않은 데이터, 사업자번호를 확인해야 하는 데이터처럼 여러 가지 검증 조건이 생길 수 있습니다.
이런 조건을 각각 검증열로 만들어두면 이번에 만든 미입금 조회 버튼과 비슷한 방식으로 기능을 확장할 수 있습니다.
제가 실제 업무에서
데이터 저장 → 수식으로 상태 검증 → 필요한 상태 필터 조회 → 자주 사용하는 조회조건 VBA 버튼화
순서로 기능을 늘려간 이유도 여기에 있습니다.
처음부터 복잡한 자동화 파일을 만들려고 하기보다는 실제로 반복되는 작업을 하나씩 줄이다 보면 필요한 자동화 구조가 자연스럽게 만들어집니다.
다음에는 여러 검증 조건이나 검색어를 VBA 버튼과 함께 사용하는 방법도 정리해보겠습니다.
← 이전 글 | 엑셀에서 미입금 건만 필터로 빠르게 확인하는 방법
감사합니다.