엑셀 매크로&VBA] 고급 필터 매크로를 보완하는 VBA(변수와 InputBox 함수)

지난 [고급 필터 매크로] 강좌에서 수강 과목수를 변경하고 단추를 누르면, 기존 결과가 지워지고 고급 필터가 실행되는 매크로 작성법을 배웠습니다.

 

수강 과목수가 달라질 때마다 수식을 계속 변경해야 하는데 조금 더 편리한 방법이 없을까 생각해봅시다.

현재 강좌는 '게으름'에서 시작됩니다. ㅋㅋ

 

직접 조건식을 수정하는 것이 아니라 InputBox를 띄워 신청 인원수를 입력 받도록 처리해보겠습니다. 

 

 

이 강좌는 이전 고급 필터 매크로를 학습하셔야 흐름을 이해할 수 있습니다. 

고급 필터 매크로는 아래 링크를 참고하세요. 

 

엑셀 매크로&VBA] 고급 필터 매크로(조건을 변경할 때마다 결과 값이 자동으로 나오도록 매크로 ��

 

실습 파일 다운로드

고급필터VBA_실습파일.xlsm

0.02MB

완성 파일 다운로드

고급필터VBA_완성파일.xlsm

0.02MB

 

실습 파일을 열고 단축키 <Alt + F11>을 누르거나 [개발 도구] - [코드] - [매크로]를 선택한 뒤 매크로 대화상자에서 '고급필터' 매크로를 선택하고 [편집] 단추를 누릅니다. 

 

 

연산자와 수강 과목수를 입력 받을 수 있는 코드를 작성하겠습니다.

 

Sub 고급필터()
'
' 고급필터 매크로
'

'
    
    
    
    
    Range("I4").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Range("G9").Select
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Range("데이터베이스").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "G3:G4"), CopyToRange:=Range("I3:K3"), Unique:=True
End Sub

 

Range("I4").Select 명령 앞에 커서를 두고 <Enter>를 4번 정도 누릅니다. 

 

<Enter> 키를 여러 번 눌러 빈 줄을 넣는 이유는 지금부터 입력할 코드와 원래 있던 코드를 분리하기 위해서 입니다. 

코딩을 할 때 코드를 수정하다 보면 원래 코드를 건드릴 수 있어 미리 공간을 띄워 두는 겁니다.

이렇게 띄워 둔 빈 줄은 코딩이 모두 완료되고 마무리할 때 지울 겁니다.

그렇게 해야 보기 좋은 코드가 되니까요. 

 

inputbox( 까지 입력하면 VBE 도움말이 나타납니다.

첫번째 Prompt는 필수 요소이고, 대괄호([  ])로 묶여 있는 나머지 인수들은 선택 요소(생략 가능)입니다.

 

 

inputbox("비교연산자와 2~5 사이의 정수를 입력하세요. 예) 2과목 이상일 경우 >=2","수강과목수 입력",">=2")

 

1. Prompt 인수에 '비교연산자와 2~5 사이의 정수를 입력하세요. 예) 2과목 이상일 경우 >=2'라고 입력합니다. 문자열은 큰따옴표로 묶어 입력해야 합니다.

2. Title 인수에 '수강과목수 입력'이라고 적었습니다. Title은 입력상자 위 제목 부분에 표시되는 내용입니다.

3. Default 인수는 기본값을 설정하는 인수로, '>=2'를 입력합니다. 

 

위의 코드를 작성하면 이후 매크로를 실행했을 때 아래와 같이 실행됩니다. 

InputBox를 보니 각 인수가 어디에 사용되었는지 알 수 있죠?

Default 인수에 입력한 >=2가 입력창에 기본값으로 입력되어 있네요. 

 

 

이제 InputBox에서 입력받은 과목수를 저장할 장소가 필요합니다. 

바로 값을 담아두는 변수입니다. 

 

수강과목수=inputbox("비교연산자와 2~5 사이의 정수를 입력하세요. 예) 2과목 이상일 경우 >=2","수강과목수 입력",">=2")

 

InputBox 앞에 '수강과목수='을 입력합니다. 

VBA는 엑셀과 다르게 결과를 나타낼 셀이 없기 때문에 InputBox 함수가 실행된 뒤 나오는 값을 저장해 둘 곳이 필요합니다.

=(이퀄) 앞에 적은 '수강과목수'가 변수가 되고 변수에 InputBox에서 입력받은 값을 저장합니다. 

 

제대로 동작하는지 매크로를 실행해 보겠습니다. 

코딩하는 중간중간 제대로 코딩했는지 검사해 보는 건 좋은 습관입니다.

 

[표준] 도구모음의 실행 단추를 누르거나 단축키 <F5>를 누릅니다.

 

 

VBE 뒤에 가려져 있던 엑셀 창이 나타나고 InputBox 함수가 실행되어 수강과목수 입력 창이 표시됩니다.

[확인]이나 [취소] 단추 중 어떤 걸 눌러도 상관없습니다.

어차피 코딩에는 입력창이 나타나는 부분만 만들었고 다른 동작에 아무런 영향도 미치지 않습니다. 

 

[확인] 단추를 누르면 다시 VBE가 나타납니다.

 

 

작성한 코드가 정상적으로 동작했습니다.

만약 문제가 생겼다면 경고창이 나타납니다.

 

그런데 여기서 보기가 조금 불편한 부분이 있습니다. 

1. inputbox 프롬프트에 나타나는 메시지가 지시사항과 예문이 줄이 나누어져 있지 않고 한 줄에 나타납니다. 

2. 코딩 화면에 inputbox 코딩이 길게 이어져 한 화면에 보이지 않습니다. 

 

먼저 프롬프트 인수에 줄바꿈을 하려면 vbCrLf를 사용하면 됩니다.

vbCrLf는 VBA에서 약속된 상수로 <Enter>를 누른 것과 같은 동작을 합니다.

 

수강과목수 = InputBox("비교연산자와 2~5 사이의 정수를 입력하세요." & vbCrLf & "예) 2과목 이상일 경우 >=2", "수강과목수 입력", ">=2")

 

지시사항 문자열과 예문 문자열을 분리하고, 사이에 vbCrLf를 넣어 연결합니다.

 

수강과목수 = InputBox("비교연산자와 2~5 사이의 정수를 입력하세요." & vbCrLf & _
	"예) 2과목 이상일 경우 >=2", "수강과목수 입력", ">=2")

 

이번에는 한 화면에 표시되지 않을 만큼 길게 나열된 코드를 여러 줄로 분리하는 것입니다.

InputBox 함수를 보면 인수가 내용이 길어 줄을 나누면서 _(밑줄)로 연결했습니다. 

그리고 고급 필터 코드 줄 끝에도 _(밑줄)로 연결했습니다. 

바로 한 줄의 코드를 다음 줄로 분리한다는 표시입니다.

첫번째 줄 끝에 한 칸 띄우고 밑줄을 입력하고, 다음 줄은 들여쓰기 해서 나머지를 입력합니다.

 

 

이번에는 고급 필터 조건식을 직접 수정하지 않고 InputBox에서 입력 받은 과목수에 맞게 내부적으로 수식이 수정되도록 해야 합니다. 

'수식' 변수에 조건식이 입력된 엑셀 워크시트 [G4] 셀의 수식을 대입하는 코드를 작성하겠습니다. 

수식=range("g4").for를 입력하면 자동으로 Range 개체의 메서드(Method)와 속성(Property) 목록이 뜹니다. 

 

 

Fomula 속성에서 <Spacebar>를 누르거나 더블 클릭합니다. 

 

수식 = Range("G4").Formula

 

range("G4").formula는 엑셀 워크시트 [G4] 셀에 수식을 표시하라는 Range 개체의 매서드입니다. 

마지막으로 range("G4").Formula="=COUNTIF($A$4:$A$90,A4)" & 수강과목수라고 작성합니다. 

 

 

Range("G4").Formula = "=COUNTIF($A$4:$A$90,A4)" & 수강과목수

 

수강과목수 변수에 연산자와 수강과목수가 같이 들어 있기 때문에 COUNTIF 함수 바로 뒤에 붙여 나타내면 됩니다.

 

Sub 고급필터()
'
' 고급필터 매크로
'

'
    수강과목수 = InputBox("비교연산자와 2~5 사이의 정수를 입력하세요." & vbCrLf & _
        "예) 2과목 이상일 경우 >=2", "수강과목수 입력", ">=2")
    
    수식 = Range("G4").Formula
    Range("G4").Formula = "=COUNTIF($A$4:$A$90,A4)" & 수강과목수
    
    Range("I4").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Range("G9").Select
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Range("데이터베이스").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "G3:G4"), CopyToRange:=Range("I3:K3"), Unique:=True
End Sub

 

코드가 완성되었습니다.

빈 줄은 지우고 정리를 좀 한 다음 실행을 해 봅니다.

그 전에 꼭 해야 할 작업이 있습니다.

바로 [저장]입니다.

 

중간중간에도 저장을 했어야 하고, 실행하기 전에 꼭 저장하는 습관을 들여야 합니다.

실행하다 오류가 생겨 비정상적인 방법으로 프로그램을 멈추게 되면 저장하지 않은 내용은 모두 사라집니다.

우리는 세 줄 정도 코딩했기 때문에 문제가 생겨도 처음부터 다시 쓰는데 별 어려움이 없겠지만, 앞으로 아주 긴 코딩을 할 때도 있겠죠.

 

 

이제 실행해 봅니다.

[표준] 도구모음의 [실행] 단추를 누르거나 단축키 <F5> 키를 누릅니다.

 

입력 상자에 >=3을 입력하고 [확인] 단추를 누릅니다.

 

 

3과목 이상인 신청인 목록이 필터됩니다. 

 

VBE에서 실행하면 실행이 완료된 후 다시 VBE가 실행되어 엑셀 창이 뒤에 숨게 됩니다. 

<결과> 단추를 [G3:G4] 셀 범위에 맞게 위치를 옮기고 크기를 변경합니다. 

 

 

저장하고 마무리합니다.