IT 기술 따라잡기

엑셀 프로그래밍 추가 작업을 하고 있는데 몇가지 새로운 로직이 나왔습니다.

필터링 후 row의 갯수를 구하는 것과 필터링 후 특정 필드의 Average를 구하는 것입니다.

우선 필터링 후 row의 갯수를 구하는 것은 아래와 같이 하면 됩니다.

이걸 제 작업에서는 아래와 같이 활용했습니다.

특정 Sheet로 가서 필터링 한 후 특정 필드의 row 갯수를 구하는 겁니다.

우선 제가 작업하는 엑셀 파일에는 첫 sheet 에 링크가 있어서 원하는 sheet로 링크를 클릭해서 가도록 돼 있습니다.

이게 clickLink() 함수 인데요.

Link가 있는 Cell 주수를 String 파라미터로 받습니다. 그리고  goToSheet() 로 링크가 있는 sheet name을 파라미터를 전달합니다.

goToSheet()은 sheetName을 파라미터로 전달 받아서 해당 Sheet로 이동을 하는 함수 입니다.

다시 clickLink()를 보면 goToSheet() 다음에 해당 링크를 클릭합니다.

간단하게 말하면 clickLink()에 링크를 전달하면 그것을 클릭하는 겁니다.

그 다음에 getBelowCell2() 에서 값을 전달받아 firstCell Range에 담는데요.

getBelowCell2 는 필드 이름(String)을 전달 받아서 바로 그 밑의 Cell을 return 합니다.

두번째는 activateLastrow2Cal2()에서 return 받은 Double형을 lastRow에 담습니다.

activateLastrow2Cal2()는 필드이름을 파라미터로 전달 받아서 그 필드의 가장 마지막 row 번호를 return 합니다.

이 값을 가지고 계산할 필드의 마지막 Row를 계산합니다.

그러면 계산할 필드의 첫번째 Cell과 마지막 Cell을 확보한 겁니다.

그 다음에 필터링을 하는데요.

우선 ActivateCell은 필드 이름을 전달 받아서 해당 필드이름(제목)이 있는 Cell을 활성화 시키는 겁니다.

filterOption()은 필터링할 필드 이름과 필터링할 값을 파라미터로 받아서 필터링을 하는 겁니다.

그 전에 IF 문에 로직을 만들었는데 그건 제가 작업하는 엑셀 파일에서 이렇게 해야 되서 넣은 겁니다.

이러면 계산할 필드의 첫번째와 마지막 Cell을 구했고 조건에 맞게 필터링도 했습니다.

그 다음에 나오는 소스코드 입니다.

우선 첫번째 cell 에서 마지막 cell 까지를 rngtest라는 Range에 담고 이 range의 row 수를 count 에 담습니다.

writeTable4()는 전달하는 Sheet Name 의 해당 위치(위에서는 G6) 에 count 값을 write 하려고 만든 겁니다.

그 내용을 보면 전달받은 sheet name을 가진 sheet를 활성화 시키고 전달받은 위치 ("G6") 에 전달받은 값을 write 하는 겁니다.

writeVal1에 offset()을 사용한 것은 조건에 따라서 write할 Cell의 위치("G6") 를 바꾸기 위해서 입니다.

이 부분을 정리하려고 했는데 이전에 만들어 놨던 여러 Component들까지 다 살펴 봤네요.

지난번 VBA 디버깅 툴 사용법 다루면서 Debug.print 문도 배웠는데요.

Immediate Window 에 출력되는 양이 한정돼 있더라구요.

그래서 제가 원하는 전체 로그를 볼 수 있는 방법을 알아봤더니.. 자체적으로 Immediate Window의 출력량을 늘리는 법은 못찾았고 대신에 로그를 파일로 저장하면 되겠더라구요.

그래서 로그를 파일로 저장하는 예제 몇개 정리해 둘까 합니다.

Dim s As String
Dim n As Integer

n = FreeFile()
Open "C:\test.txt" For Output As #n

s = "Hello, world!"
Debug.Print s ' write to immediate
Print #n, s ' write to file

Close #n

Open .... For Aoutput As #n 이라는 구문이 있군요.

해당 텍스트 파일을 #n으로 세팅해 놓고 Print #n, 출력할 문자열 ' write to file 을 하고 마지막에 #n을 Close 해 줍니다.

(아직 실습을 안 해 봐서 정확히 이게 맞는건지는 확실하지 않습니다.

이렇게 Input과 Output 사용법이 약간 다른가 봅니다.

이건 약간 다른 방법 같은데... 이번 주말에 전부 한번 실제로 돌려 봐야겠네요.

Debugging VBA Code

Introduction

프로그래밍에서 디버깅을 하는 것은 가장 중요한 부분중의 하나입니다. VBA 디버깅 툴에 대해 잘 알면 디버깅을 좀 더 쉽고 생산적으로 할 수 있습니디다. 이 글에서는 VBA의 built in 디버깅 툴에서 여러분이 어플리케이션을 테스트하거나 디버깅하는데 도움이 되는 몇가지를 다룰 겁니다.

SectionBreak

Stepping Through Code

코드를 디버깅하기 위해 우선 해야 하는 것은 한번에 한라인의 코드를 살필 수 있어야 하는 겁니다. 코드를 쭉 훑어 보려면 살펴 볼 코드의 첫번째 줄에 커서를 위치시킵니다. 그리고 F8을 누르거나 Debut 메뉴를 누릅니다. 다음에 실행될 코드가 검은 글씨에 노란 배경색으로 표시될 겁니다.  이 노란 배경이 쳐진 줄은 아직 실행이 안 된 겁니다. 바로 다음에 실행 될 라인 입니다.

그 코드가 다른 procedure를 call 한다면 F8을 누르면 호출된 procedure로 들어갈 겁니다. 호출된 procedure로 들어가고 싶지 않으면 SHIFT F8을 하면 됩니다. SHIFT F8을 누르면 호출된 procedure로 들어가지 않고 그냥 실행만 합니다. 그리고 그 다음번 줄에서 디버깅을 위해 정지할 겁니다. 이미 그 procedure로 들어왔다면 CTRL F8를 누르시면 모두 실행이 될 겁니다. 그리고 어느때던지 F5를 누르거나 Run menu에서 Continue를 누르면 이후의 모든 과정이 실행될 겁니다. (pause 구문이 있으면 당연히 서겠죠)

step-by-step 모드일 때 Immediate window에서 변수 값을 변경할 수 있습니다.

Break Points And The Stop Command

Breakpoint는 디버깅 모드에서 살펴보기 위해 실행을 잠깐 멈추도록 하는 표시입니다. 이 표시를 하려면 F9을 누르거나 코드 왼쪽 부분을 클릭하면 됩니다. 그리고 Debug menu에서 Toggle Breakpoint를 선택하는 방법도 있습니다. breakpoint로 설정 되면 흰색글씨에 벽돌색 배경화면으로 표시됩니다. 그리고 코드를 실행시키면 이 라인에서 멈추어 검은 글씨에 노란 배경화면이 돼 있을 겁니다. 아까 말했듯이 노란색 부분은 아직 실행이 되지 않은 부분 입니다.

breakpoint에서 멈췄을 때 Immediate window 에서 변수 값을 바꿀 수 있다. 변수의 내용을 보려면 ?를 입력하고 변수 이름을 넣고 엔터키를 치면 된다. 변수이름 = 새로운 값 형식으로 Immediate window에 넣고 엔터키를 치면 변수 값을 변경할 수 있다.

Immediate window가 보이지 않으면 (대개 VBA 에디터의 밑에 부분에 있다. CTRL G를 누르거나 View menu에서 Immediate Window를 선택하면 된다.

breakpoint를 없애려면 커서를 해당 줄에 위치시킨 후 F9을 누르세요. Debug 메뉴에서 Clear All Breakpoint를 선택하거나 CTRL SHIFT F9을 누르면 모든 Breakpoint를 없앨 수 있습니다.

VBA에서는 Stop 명령어도 제공합니다. 해당 라인에서 프로그램의 실행을 멈추고 break 상태로 만들 수 있습니다.

디버깅이 끝나면 모든 breakpoint들을 clear 하는 것을 잊지 마세요. (디버그 메뉴에서 Clear All Breakpoint를 누르거나 CTRL SHIFT F9을 누르기) 그리고 Stop statement도 없애거나 주석처리해 주시구요.

breakpoint를 사용했던 아니면 디버깅 모드에서 한줄 한줄 넘어가면서 왔던 어떤 줄에 멈추어 있다면 다음 줄을 수정해서 실행시킬 수 있습니다. 그리고 현재의 라인이 있는 section을 재 실행 할 수도 있구요. 실행을 원하는 라인에 오른쪽 마우스를 클릭하시고 실행 하고 Run menu에서 Next Statement를 선택할 수도 있습니다. 그러면 선택된 라인에서 재 실행할 겁니다.

The Debug Command

VBA는 두개의 파라미터를 가진 Debug 객체를 제공합니다. Print와 Assert을 사용해서 변수값을 표시할 수 있고 프로그램의 흐름을 콘트롤 할 수 있습니다. Debug.Print를 사용하면 Immediate window에서 출력 결과를 확인하실 수 있습니다. 이것은 프로그램의 실행에는 아무런 영향을 미치지 않습니다. Immediate window에 글자를 출력하고 난 후 다음 부분이 계속 실행됩니다. 변수 이름과 원하는 글자를 사용해서 Immediate 창에 원하는 글자를 출력하실 수 있습니다.

   

 Debug.Print "The value of variable X is: " & X

한번에 여러개의 변수를 출력할 수도 있습니다. 콤마로 변수를 구분해 주시면 됩니다.

   

Debug.Print X, Y, Z

   

Debug.Assert는 conditional breakpoint로서 Assert문장이 False 일 경우 실행을 멈춥니다.

  

  Debug.Assert Var >= 0

   

이 경우에는 변수가 0보다 작을 경우 멈추게 되겠죠. 이 Assert 메소드는 C 언어에서 가져온 겁니다. 그래서 C에서 사용하는 방법을 아시면 그대로 사용하시면 됩니다.

디버깅이 끝나시면 이 Debut.Print나 Debug.Assert 구문을 없애시거나 주석처리하는 것을 잊지 마세요.

SectionBreak

The Locals Window

Locals windows는 현재 진행되는 부분의 모든 변수들의 값을 볼 수 있도록 해 줍니다. 이 Local window를 보시려면 View 메뉴에서 Locals Window를 선택하세요. Immediate window에서 원하는 변수를 출력하거나 변경하실 수도 있습니다. 변수는 1개의 라인에 표시됩니다. Range 같이 여러 값이 들어 있으면 트리구조로 그 값을 보여줍니다. (Collpsible)

SectionBreak

The Watch Window

Watch window 는 모든 Watches를 보여줍니다. View menu에서 Watch Window를 선택하시면 보실 수 있습니다. Expression이 True일 때 혹은 변수가 그 값이 변경되거나 할 때 실행을 pause 할 수 있도록 해 주는 instruction입니다. 변수에 Watch를 생성하시려면 Watch window를 열고 Watch window에서 right-click을 하신 후 Add Watch를 선택하세요. Add Watch dialog에서는 Expression text box에 Watch를 원하시는 변수 이름을 넣으세요. 그 다음에 Break When Value Changes를 선택하시면 됩니다. 그러면 실행시 변수 값이 변경되는 라인에서 멈추게 될 겁니다. 실행이 멈추면 그 변수의 값이 변경돼 있는 상태인 겁니다.

Watch 를 remove 하려면 Watch window에서 right-click을 하시고 팝업메뉴에서 Delete Watch를 선택하세요. Watch를 변경하시려면 Edit Watch를 선택하시면 됩니다.

SectionBreak

The Call Stack

Call Stack은 VBA가 관리하는 data structure 입니다. 어떤 procedure가 어떤 다른 procedure를 call 했는지를 추적해 주죠. 만약 AAA procedure가 BBB를 call 하고 이 BBB는 CCC를 Call 한다면 Call Stack window는 가장 최근의 procedure서부터 리스트에 표시를 해 줄 겁니다. 실행된 procedure들의 chain은 현재까지 오는 동안의 진행과정을 보여 줄 겁니다. View ment에서 Call Stack 을 선택하면 이 Call Stack window를 보실 수 있습니다. 현재까지 오는 동안 어떤 procedure들이 실행됐는지 추적할 때 아주 유용하죠. programmatic 하게 이 call stack을 볼 수 있는 방법은 없습니다.