고급 필터 매크로를 VBA로 보완(변수, InputBox 함수)

안녕하세요.

 

고급 필터 매크로 강좌에서 수식으로 작성한 조건 값을 바꿔 작성한 매크로를 실행하면, 전에 조건으로 만들어진 결과를 지우고 변경한 조건을 기준으로 고급 필터가 실행되는 매크로 만드는 법을 배웠었죠?

 

바꿔야 하는 부분은 수식의 마지막 부분인 중복 과목 수를 나타내는 숫자 부분입니다.

 

이 매크로를 이용해서 조건을 바꾼 새로운 고급 필터 결과를 구하려면 다음 세 단계의 작업해야 하죠.

 

1. 조건 수식이 들어있는 셀을 선택해 셀 수정 상태로 전환

2. 원래 적혀 있던 숫자를 지우고 새로운 숫자를 입력하고 <Enter>를 쳐서 셀 수정을 마무리

3. <결과> 단추를 눌러 매크로 실행

 

이 세단계를 하나로 줄일 방법이 없을까?

<결과> 단추만 누르면 중복 과목 수를 묻는 창이 나타나고, 입력한 값을 이용해 매크로가 바로 실행되는 것을 원합니다.

 

이 '게으름'에서 이야기가 시작됩니다.

 

실습 파일 다운로드

고급필터VBA_실습파일.xlsm

0.02MB

결과 파일 다운로드

고급필터VBA_완성파일.xlsm

0.02MB

 

 

 

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

화면에 표시된 'Microsoft Visaul Basic for Applications'란 제목의 창이 VBA Editor 입니다. 보통 줄여서 VBE라고 합니다.

Microsoft Visaul Basic for Applications은 프로그래밍 언어를 의미하고, 보통 VBA라고 합니다.

이 이야기도 자세히 하자면 한참 길어질테니 여기까지만 하죠.

우리가 알아야 할건 VBA는 프로그래밍 언어이고, VBE는 VBA 편집기라는 것, 딱 여기까지입니다.

 

코드창에 보이는 내용이 앞에서 만든 고급필터 매크로 내용입니다.

엑셀에서 만드는 모든 매크로는 이렇게 코드로 정리되어 저장됩니다.

 

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

 

앞에서 봤던 매크로보다 줄 수도 많고, 줄마다 길이도 길어져서 어려워 보이죠?

처음보면 복잡해 보이지만 자주 보다보면 익숙해져 구분이 가능해 집니다.

 

처음과 마지막의 Sub~End Sub문은 매크로의 시작과 끝을 나타내는 문장이죠.

 

인용부호(')로 시작하는 줄은 주석입니다.

이 매크로에는 단축키를 설정하지 않아서 어떤 단축키가 설정되어 있는지 알려주는 주석은 없습니다.

 

코드를 하나하나 간단하게 살펴보겠습니다.

Range("I4").Select

[I4] 셀을 선택한다는거죠.

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

우리가 매크로를 만들 때 어떤 작업을 했나 생각해 보면 윗줄은 <Ctrl + Shift + 오른쪽 화살표>한 내용이고, 아랫줄은 <Ctrl + Shift + 아래쪽 화살표>한 내용이죠.

이걸 생각하며 코드를 보니 알만한 단어 Right와 Down이 보이죠!

아마 Left와 Up도 어떻게 쓰면 되는지 알 수 있겠죠.

Selection.Delete Shift:=xlUp

이번엔 셀을 삭제하는 내용입니다.

우리가 매크로를 만들 때 했던 작업이 셀을 삭제하는 내용이었으니 당연하겠죠?

끄트머리에 있는 Up은 셀 삭제할 때 나타난 메뉴 '셀을 왼쪽으로 밀기', '셀을 위로 밀기', '행 전체', '열 전체' 중 우리가 선택했던 '셀을 위로 밀기'를 나타내는 겁니다.

Application.CutCopyMode = False
Application.CutCopyMode = False
Application.CutCopyMode = False

이 코드는 딱 봤을 때 느낌으로 범위선택해서 복사할 때 복사냐, 잘라내기냐를 선택하는 것 같은데, 우리가 매크로를 만들 때 복사나 잘라내기에 관련된 일은 한 적이 없는데, 왜 이런 코드가 포함되어 있는지 알 수 없군요.

 

매크로를 만들다 보면 엑셀은 사용자의 의도를 알 수 없기 때문에 필요한 작업보다 더 과하게 필요한 여러 조치를 취하는 경우가 있습니다.

이 코드도 그런 것 중 하나인 것 같습니다.

그렇다해도 같은 위치에 3줄이나 똑같이 적을 필요는 없을 것 같죠?

 

False는 키워드입니다.

논리값 거짓을 나타내죠.

같이 형태로 사용되는 논리값 참을 나타내는 키워드는 True입니다.

Range("데이터베이스").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
    "G3:G4"), CopyToRange:=Range("I3:K3"), Unique:=True

'AdvancedFilter'를 보고 이 코드가 뭘 의미하는지 아셨죠?

또는 매크로를 만든 순서를 생각해서 알았을 수도 있겠군요.

 

고급 필터를 실행하는 코드입니다.

 

첫번째 줄 끝 밑줄( _ )은 코드를 한 줄에 길게 다 써야 하는데, 그렇게 코드를 만들면 보기 좋지 않기 때문에 코드를 끊고 다음 줄에 이어서 쓴다는 표시입니다.

그래서 다음 줄에 이어진 코드에는 계속 연결된 줄이란 걸 표내기 위해 들여쓰기해서 나타내죠.

 

코드가 이어져 있다는 의미로 쓴 밑줄은 줄 마지막에 적어야 하고 꼭 앞 글자와 한 칸 떨어져 있어야 합니다.

밑줄은 키보드에 숫자 0 오른쪽에 있는 빼기(-)키를 <Shift> 키를 누르고 입력하면 나타납니다.

 

이제 본격적으로 새 코드를 입력할 때가 되었습니다.

 

매크로가 실행되자마자 사용자에게 수강 과목 수를 입력 받을 수 있도록 코드를 작성할 겁니다.

 

커서를 Range("I4").Select 줄 앞에 두고 <Enter>키를 여러 번 누릅니다.

여러 번 누르기라고 하면 애매하니까 4번 <Enter> 키를 누르는 걸로 정하죠. ^^

 

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

코딩을 하고, 고치기 위해 지우고 하다가 원래 코드를 건드릴 수도 있으니 미리 공간을 띄워두는 겁니다.

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

그래야 보기 좋은 코드가 될 테니까요.

보통 우리가 보게 되는 완성된 코드들은 이런 과정을 거쳐 만들어진 것을 봅니다.

코드를 만들 때는 그렇게 정돈된 모습이 아닌거죠.

 

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

inputbox( 까지 입력하면 엑셀에서 함수를 입력할 때 인수 목록을 보여 주듯이 VBE에서도 풍선도움말이 나타납니다.

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

일단 쭉 적어보죠.

inputbox("2~5 사이의 정수를 입력하세요.","수강과목수 입력",2)

첫번째 인수는 Prompt로 '2~5 사이의 정수를 입력하세요.'라고 적었습니다.

VBA에서 문자열은 쌍따옴표로 묶어 입력해야 합니다.

두번째 인수 Title에 '수강과목수 입력'이라고 적었습니다.

Title은 입력상자 위 제목 부분에 표시되는 내용입니다.

세번째 인수는 Default인데 기본값을 설정하는 인수이고, 숫자 2를 적었습니다.

=inputbox("2~5 사이의 정수를 입력하세요.","수강과목수 입력",2)

커서를 줄 처음으로 옮겨 이퀄(=)을 입력합니다.

inputbox는 함수입니다.

괄호를 적은 이유는 함수이기 때문입니다.

함수는 실행되고 결과 값을 반환합니다.

엑셀에서 셀에 =Sum(1,3)이라고 입력하고 <Enter>키를 누르면 4가 셀에 보이게 되죠.

Sum 함수가 인수 1과 3을 더해 4를 구하고 셀에 나타낸 겁니다.

수강과목수=inputbox("2~5 사이의 정수를 입력하세요.","수강과목수 입력",2)

이퀄(=) 앞에 수강과목수라고 입력합니다.

 

VBA에는 엑셀과 다르게 결과를 나타낼 셀이 없기 때문에 inputbox 함수가 실행된 뒤 나오는 값을 저장해 둘 곳이 필요한데, 이퀄 앞에 적은 '수강과목수'에 저장하게 됩니다.

프로그램이 실행되는 중에 어떤 값을 저장해 두는 것을 변수라고 합니다.

 

변수 말고도 몇가지 더 있지만 일단 여기에선 변수까지만 알아보죠.

 

내일은 내일의 해가 뜰테니까요. ^^

 

변수를 만들 때는 매크로 이름을 정하는 것과 같은 규칙이 적용됩니다.

첫글자는 문자로 시작해야 하고, 중간에 빈 칸이 있으면 안됩니다.

영어나 한글, 어떤거라도 상관없지만 코딩할 때 예약어가 모두 영어라 한글로 변수명을 정하면 한영 전환키를 누르며 입력해야 해서 불편합니다.

하지만 한글로 변수명을 적으면 코드를 이해하는데 도움이 됩니다.

수강과목수 = InputBox("2~5 사이의 정수를 입력하세요.", "수강과목수 입력", 2)

커서를 제일 뒤로 옮겨 <Enter> 키를 누릅니다.

 

이퀄 앞, 뒤와 쉼표 뒤에 빈 칸이 하나씩 삽입되고 inputbox 글자 중 i와 b가 대문자로 바뀝니다.

VBE가 코드를 인식해 등록된 예약어를 자동으로 정리해 준 겁니다.

InputBox는 등록된 함수이므로 철자를 정확히 적었다면 등록된 글자 모양대로 대소문자 맞춰 보여주는거죠.

앞으로 다른 코딩을 할 때도 이렇게 하는걸 잘 기억하면 오류를 막는데 큰 도움이 됩니다.

 

딱 한 줄 새로 적었지만 제대로 동작하는지 매크로를 실행해 볼 수 있습니다.

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

 

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

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

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

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

 

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

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

여기서 뭔가 문제가 생겼다면 경고창이 나타납니다.

 

우리가 적은 한 줄 코드 이외 나머지는 제대로 동작하는 매크로를 가져온 것이라 다시 확인하지 않아도 되죠.

 

문제가 없다고 보고 이어서 계속 코딩합니다.

수식=range("G4"

이퀄(=) 앞의 수식은 변수입니다.

변수 이름은 임의로 정할 수 있습니다.

 

Range는 개체(Object)입니다.

개체가 무엇이냐는건 지금 이야기하지 않겠습니다.

꽤나 긴 이야기이고, 몰라도 지금 코딩하는데 문제 없기 때문이죠.

Range 개체는 다른 매크로에서도 사용했던 겁니다.

 

 

range("G4")는 엑셀의 [G4] 셀을 의미합니다.

[G4] 셀은 고급 필터 조건을 수식으로 작성해둔 셀입니다.

=COUNTIF($A$4:$A$90,A4)=2

현재 수식의 결과는 FALSE 입니다.

 

우리는 수식의 결과가 필요한게 아니라 수식 그 자체가 필요하고 그 중 제일 마지막 숫자를 InputBox 함수를 통해 입력 받은 수강과목수 변수의 내용으로 바꿔야 하죠.

 

수식=range("G4").

VBE에서 개체 이름을 입력하고 점(.)을 찍으면 메소드(Method)와 속성(Property) 선택창이 표시됩니다.

메소드는 초록색으로 지우개가 날라가는 것처럼 표시되고, 속성은 표를 손으로 가르키고 있는 모양입니다.

당장은 그냥 그런게 있다 정도로 넘어가죠.

 

range("G4") 라고 쓰거나 range("G4").value 라고 쓰면 [G4] 셀의 결과값인 FALSE라는 글자를 의미합니다.

 

[G4] 셀에 입력된 수식을 나타내려면 range("G4").formula 라고 쓰면 됩니다.

formula 글자를 다쓰지 말고 메소드/속성 선택창이 표시된 상태에서 앞 세 글자 'for'을 쓰면 'for'로 시작하는 목록을 보여 줍니다.

엑셀에서 함수를 입력할 때 이퀄(=)을 쓴 다음 함수의 첫 두, 세 글자를 입력해 선택하는 것과 같은 방식입니다.

 

수식=range("G4").Formula 

Formula 속성에 커서가 놓여졌다면 <Spacebar> 키를 누르거나 마우스로 더블클릭합니다.

수식 = Range("G4").Formula

커서를 줄 끝에 두고 <Enter> 키를 누르면 이퀄(=) 앞, 뒤로 빈 칸이 생기고 range의 첫 글자가 대문자 R로 바뀝니다.

 

수식 변수에는 '=COUNTIF($A$4:$A$90,A4)=2'가 텍스트 형태로 저장되어 있습니다.

엑셀에서는 이 텍스트가 수식이겠지만, VBA에서는 그냥 글자일 뿐이죠.

 

Left와 Len 함수를 이용해 마지막 숫자를 제외한 나머지 부분을 구하고 문자 결합 연산자 &(Ampersand)를 이용해 InputBox 함수로 받은 숫자를 뒤에 붙여 [G4] 셀의 수식으로 입력하겠습니다.

range("G4").Formula=left(수식,len(수식)-1)&수강과목수

Left와 Len 함수는 엑셀에 똑같은 함수가 있고, 사용법도 똑같지만 여기서 사용한 건 VBA 함수입니다.

엑셀 함수와 VBA 함수는 대부분 비슷하지만 기능이 다르거나 이름이 다른 함수들이 있으므로 사용할 때 주의해야 합니다.

또 문자 결합 연산자 &를 쓸 때 변수명에 바로 붙여서 적으면 문자 결합 연산자 역할이 아닌 다른 기능을 하게 되니 조심해야 합니다.

Range("G4").Formula = Left(수식, Len(수식) - 1) & 수강과목수

끝에서 <Enter> 키를 누르면 첫글자는 대문자로 바뀌고 빈 칸이 적당히 들어가 보기 좋은 모양의 코드가 됩니다.

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

'
    수강과목수 = InputBox("2~5 사이의 정수를 입력하세요.", "수강과목수 입력", 2)
    수식 = Range("G4").Formula
    Range("G4").Formula = Left(수식, Len(수식) - 1) & 수강과목수
        
    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가 실행되어 엑셀 창이 뒤에 숨어 있게 되죠.

엑셀의 [G6] 셀에 있는 <결과> 단추를 눌러 실행하면 실행이 완료된 후 엑셀 창으로 다시 복귀하게 됩니다.

저장하고 마무리합니다.