IT 기술 따라잡기

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을 볼 수 있는 방법은 없습니다.

영하 25도

막 출근했는데요. 주차장에 차 세워놓고 건물로 들어오는데 공기에 노출된 살들이 너무 아프더라구요.

영하 25도.. 정말 추운 날씨예요.

어제는 영하 30도까지 내려갔었던거 같은데.....

빨리 봄이 왔으면 좋겠습니다.

어제 오후에 인도에서 새로온 매니저가 엑셀일을 하다가 매뉴얼로 하기엔 너무 벅찬 일이라 저에게 프로그래밍을 부탁하더라구요.

요구 조건은 이겁니다.

이걸 10시까지 작업해서 보내줬는데요.

이 작업하면서 만들어논 컴포넌트들이나 로직들을 다음에도 유용하게 사용할 수 있을 것 같아서 여기에 정리해 놓습니다.

이 Sub는 엑셀 파일내의 모든 Sheet에 대해 For Loop를 도는 겁니다.

Loop안에는 해당 Sheet를 checkFeildName() 함수로 전달합니다.

이 Sub는 전달받은 Worksheet 내에 값들이 있는 모든 Cell들에 대해 For Loop문을 돕니다.

이 Loop 안에서는 해당 Cell 의 값이 '필드 제목 1' 이거나 '필드 제목 2' 일 경우 그 Field 제목 바로 밑에서 부터 해당 필드의 값이 있는 맨 마지막 Cell을 allCell이라는 Range에 담습니다.

그리고 Worksheet과 allCell Range를 CheckReportName() 에 전달합니다.

이 Sub에서는 전달받은 Worksheet 내의 전달받은 Range에 대해 Loop를 돕니다.

이 Loop 안에서는 해당 Cell의 값을 checkExist() 에 전달해서 결과를 return 받습니다.

이 return 받은 결과가 Y 이면 해당 셀을 파랗게 그리고 return 받은 결과가 Y1이면 해당 셀을 노랗게 만듭니다.

이 함수는 특정 sheet의 특정 Range에 대해 Loop를 돕니다.

이 특정 sheet의 이름과 특정 Range는 하드 코딩을 했습니다.

시간이 나면 이 부분도 프로그래밍으로 값을 받아 올 수 있도록 Refactoring 을 할 수 있겠네요.

이 Loop에서는 받은 값을 각 Cell의 값과 비교해서 checkEx(디폴트값 N)라는 String 값을 세팅합니다.

처음에 이 특정 Sheet의 특정 컬럼에 있는 값들은 특정 패턴으로 문자열이 결합돼 있어서 이를 Split 해야 한다고 했습니다.

그 Rule이 Loop 안의 If문 안에 있습니다.

우선 맨 처음 If문에서는 해당 Cell의 값이 '첫번째' 라는 문자열을 포함하는지 여부를 체크합니다.

포함하고 있으면 이 문자열의 첫번째 13글자를 제외한 값을 omitText에 담고 그렇지 않은 경우에는 문자 전체를 omitText String 변수에 담습니다.

그리고 나서 이 '첫번째' 라는 문자열을 기준으로 각 문자열들을 Split 해서 tempArr2라는 배열에 담습니다.

이 배열의 첫번째 값은 testStr1에 두번째 값은 testStr2 라는 String 변수에 담습니다.

이 두 String 을 전달받은 String 과 비교합니다.

우선 전달받은 String 에 testStr1이 포함돼 있거나 두 문자열이 같은 경우에는 checkEx의 값을 Y로 바꿉니다.

반대로 testStr1에 전달받은 문자열을 포함하고 있는 경우에는 checkEx의 값을 Y1으로 바꿉니다.

이 작업을 testStr2에도 마찬가지로 해 줍니다.

여기까지가 해당 Cell 값에 '첫번째'라는 문자열을 가지고 있는 경우이고요 이 '첫번째'라는 문자열을 가지고 있지 않은 경우에는 곧바로 해당 Cell의 값고 전달받은 값을 비교해서 Y와 Y1이라는 값을 checkEx에 세팅을 해 줍니다.

여기까지 작업을 해서 그 결과를 매니저한테 보냈는데요.

주말에 시간 나면 이 프로그램을 다시 보고 Refactoring 좀 해 봐야겠습니다.

더 간단하게 처리할 수 있는 방법은 없는지.....

Userform

이 글에서는 Excel VBA Userform을 어떻게 생성하는지를 보여드립니다.
여기서는 아래와 같은 userform을 만들어 보겠습니다.

..

Add the Controls

Userform에 control들을 추가하려면 아래와 같이 하세요.

1. Visual Basic Editor를 연다. Project Explorer가 보이지 않으면 View-Project Explorer를 클릭한다.
2. Insert-Userform을 클릭한다. Toolbox가 자동으로 뜨지 않으면 View-Toolbox를 클릭한다.

여기까지 하면 아래와 같은 화면을 보실 수 있습니다.

3. 아래 테이블에 있는 control들을 추가 한다. 이 작업이 끝나면 위에서 본 것과 같은 Userform 모양이 될 것이다. 예를 들어 Toolbox에 있는 TextBox 를 클릭하면 text box control이 생성 될 것이다. 혹은 Userform에 text box를 드래그 할 수도 있다. Car를 할 경우 라디오버튼을 만들기 전에 frame 부터 만드는 것을 잊지 마세요.


4. 아래 테이블에 맞게 이름과 caption들을 바꾸자. Names는 Excel VBA code에서 사용될 것이다. Caption은 화면에 표시되는 글자를 말한다. control의 이름들을 바꾸어 보는 것도 좋은 연습이 될 것이다. 이렇게 바꾸면 코딩을 할 때 훨씬 편하게 할 수 있다. control의 name과 caption을 바꾸려면 View를 클릭하고 Properties 윈도우에서 각 control들을 클릭하면 된다.

Note: combo box는 유저가 아이템을 선택할 수 있는 drop down 리스트 입니다. 한개의 옵션만 선택될 수 있습니다.

이제 Sub UserForm_Initialize를 생성할 겁니다. 이 Userform에 대해 Show 메소드를 사용할 때 이 sub 도 자동적으로 실행될 겁니다.

1. Visual Basic Editor를 연다.
2. Project Explorer에서 DinnerPlannerUserForm을 마우스 오른쪽 클릭을 하고 View Code를 선택한다.
3. left drop-down list에서 Userform을 선택한다. right drop-down list 에서 Initialize를 선택한다.
4. 아래 코드를 복사해 넣는다.

텍스트 박스들은 비어 있을 겁니다. 리스트 박스하고 콤보박스는 채워져 있을 거구요. 체크박스들은 체크되어있지 않을 겁니다.

Assign the Macros

이제 Userform의 첫번째 파트를 만들었습니다. 이제 보기 좋아졌죠? 하지만 Userform 내의 어떤 버튼을 클릭하던지 아직 어떤 일이 일어나지는 않습니다.

1. Visual Basic Editor를 연다.
2. Project Explorer 에서 DinnerPlannerUserForm을 더블클릭한다.
3. Money spin button을 더블 클릭한다.
4. 아래 코드를 추가한다.

이 코드는 spin button을 이용할 때 text box가 update 되도록 하는 부분입니다.

5. OK 버튼을 더블클릭한다.
6. 아래의 코드를 추가한다.

이 라인은 Clear button이 클릭 됐을 때 Sub UserForm_Initialize를 call 하게 됩니다.

9. Cancle Button을 더블클릭한다.
10. 아래 코드를 추가한다.

이 코드는 Cancel button을 클릭했을 때 Userform을 닫도록 합니다.


Test the Userform

Visual Basic Editor를 나갑니다. 아래 처럼 Column 제목을 달고 form 에 내용을 입력한 다음에 OK 버튼을 눌러보세요.

Result:

원문을 보려면 여기를 클릭하세요.

엑셀 쉬트에 버튼을 만들고 그 버튼을 클릭하면 어떤 작업이 이뤄지도록 하려면 Developer 메뉴를 이용해야 합니다.

그런데 이 Developer 메뉴는 처음에 메뉴에 나타나지 않습니다.

이 Developer 메뉴를 나타나게 하려면 아래와 같이 하면 됩니다.

1. File 로 가서 Options를 선택한다.

2. Customer Ribbon을 선택하고 우측의 Developer 체크박스를 체크한 후 OK 버튼을 누른다.

3. 그러면 화면과 같이 Developer 메뉴가 나타나고 아래 그림 처럼 엑셀 쉬트에 버튼을 만들 수 있게 됩니다.

엑셀 프로그래밍을 만들면서 유저가 입력하는 값을 받아서 활용하는 기능이 필요하게 됐습니다.

그래서 찾아 봤는데요.

아래 같이 Inputbox를 사용하면 되더라구요.

이렇게 하면 input box 윗 부분에 제목을 달 수 있군요.

그냥 간단히 이렇게 처리해도 됩니다.

Inputbox 에 대한 튜토리얼은 아래 링크를 따라 가시면 보실 수 있습니다.

http://msdn.microsoft.com/en-us/library/office/aa195768%28v=office.11%29.aspx

원래는 유저에게 3개의 값을 받는데 이 값이 month 라서 Dropdown을 사용하려고 했습니다.

그러니까 Inputbox로 한개의 값만 받는게 아니라 3개의 값을 받는데 이게 유저가 입력하지 않고 Dropdown 메뉴에서 고르도록 하려고 했는데요.

그렇게 하려면 다른 조금 복잡하군요.

http://www.excel-easy.com/vba/userform.html

여기로 가면 좋은 예제가 있는 것 같은데 다음엔 이걸 좀 공부해 봐야 겠습니다.

Range를 Return 하려면 아래와 같이 하면 됩니다.

Sub 이 아니라 Function을 만들면 값을 Return 할 수 있습니다.

마지막에 As Variant 를 하면 Range를 Return 할 수 있습니다.

Variant 데이터 타입은 이미 지정된 데이터 타입 이외의 데이터 타입을 다룰 때 사용할 수 있습니다. user-defined 타입도 이 Variant 타입을 사용해서 다루면 됩니다. 그리고 Empty, Error, Nothing, Null 등도 이 Variant 로 처리합니다.

여기로 가시면 좀 더 자세한 정보를 얻을 수 있습니다.

그럼 이 Range를 return 하는 함수를 사용해 보겠습니다.

Ret라는 Range 변수를 지정하고 이 변수에 Test에서 return 받은 Range를 담아 둡니다.

파라미터 값은 A,1,C,5를 전달 합니다.

그럼 Range는 A1에서 C5 까지가 될겁니다.

그 다음에는 이 Ret를 Select 합니다.

이 Sample()을 실행하면 이와 같이 A1에서부터 C5까지 선택됩니다.

지금 참여하는 프로젝트에서는 Oracle 데이터베이스를 Teradata로 바꾸고 있습니다.

그래서 엑셀로 Teradata와 연동해서 현역들의 Report 작업을 도와줄 수 있는 방법이 있지 않을까 해서 그 방법을 좀 Research 하고 있습니다.

좋은 글이 있어서 한번 번역해 봤습니다.

Teradata and MS Excel VBA

How to Get Data from Teradata Directly from Your VBA Excel Application

DB와 Reportings 에 대해 일하는 사람들은 시간을 단축시키는 작업이 가장 필요한 사람들이다. Teradata SQL Macro: A Fast Introduction 글에서 봤듯이 우리 쿼리 안의 fast variable을 대체함으로서 단지 명령 하나로 우리 일을 아주 간단하게 처리할 수 있었다. 만약 이걸 Excel로 하면 어떨까? worksheet에 값들을 copy and paste 해야하고 링크를 만들어야하고 등등 의 일들을 해야 한다. 새로 report를 update 해야 된다면? 이런 작업들을 다시 반복적으로 해야 한다. 또 다시 데이터들이 바뀌면 또 다시 반복해야 하고.
이러한 이유로 VBA와 ODBC 를 사용해서 Teradata로 연결할 수 있으면 아주 편할 것이다. 이러한 반복적인 작업들을 VBA 에게 하라고 시키고 우리는 그 일이 끝나기만을 기다리면 된다. 실제로 이 작업이 어떻게 진행되는지 보자.

우선 VBA 에디터를 열면 몇개의 reference들을 include 해야 한다.

위의 버전만을 사용해야 되는 것은 아니다. 하지만 이 글에서 사용되는 기능들은 위의 버전들에 최적화 돼 있다.

이 reference들을 include 했으면 몇개의 객체들을 생성해야 한다.

이 connection은 DBMS 와 연결하는데 사용된다. Recordset은 SELECT 쿼리를 통해서 얻은 결과들 처리할 거고 Command 는 sql request를 Teradata에 보낼 것이다.

이제 VBA의 평범한 코딩을 하면 된다. 객체들을 선언하고 초기화 한다.

Dim 이후의 이름은 여러분들이 원하는 이름 아무거나 사용하면 된다.

그 다음 단계는 Teradata와의 connection을 여는 것이다. 그러려면 아래 방법대로 코딩을 하는 방법도 있다.

일단 connection이 이루어지면 이제 SQL 뭐리를 실행하라고 명령하기만 하면 된다. 이 command는 ADODB.Command 객체를 통해서 보내진다. 아래가 그 예제이다.

일단 한번에 한개의 request만 실행될 수 있음을 명심하자. 한번에 한개의 SELECT문이나 한개의 INSERT문 혹은 한개의 DELETE문만 실행될 수 있다. Teradata의 Macro에 대한 이전 글을 읽었다면 아마 여러개의 쿼리를 작동시킬 수 있는 script를 만들 수 있을 것이다. 이것은 EXEC sql의 키워드를 사용해야 한다. 아래에 몇개의 예제를 보여주겠다.

맨 마지막 부분이 어떻게 데이터를 받고 이것을 처리하는지에 대한 부분이다. 우선 한번에 한개의 SELECT 쿼리에 대한 결과를 받을 수 있다는 것을 염두에 두자. 이 의미는 해당 macro가 SELECT * FROM table_name;에서 끝나고 다른 SELECT * FROM table_name은 실행하지 않을 수도 있다는 얘기다. 이 결과 값은 ADODB.Recordset 객체에 전달된다. 여기에 대한 몇가지 유용한 메소드와 프로퍼티들을 소개하겠다. MSDN 문서로 가면 더 많은 정보들을 얻을 수 있을 것이다.

그리고 이 프로퍼티들을 아래와 같이 루프문에서 사용할 수 있다.

Do While (rs.EOF = False And rs.BOF = False) '이 루프는 recordset에 데이터가 있을 경우에만 시작한다.

루프로 들어가기 전에 우리가 받은 결과를 제대로 검색하기 위해서는 첫번째 record에 pointer가 위치하도록 해야 한다.
아래 메소드를 사용하면 그 작업을 할 수 있다.

이건 Do While 루프문이 시작되기 전에 실행하도록 해야 할 것이다.

일단 do while 루프안으로 들어가면 한번에 한개의 row를 받을 것이다. 이것은 GetRows 메소드를 사용해서 이 작업을 할 수 있다.

이 메소드는 적어도 한개의 값이 있을 경우에 가능하다. 이 값은 몇개의 row들을 받을 것인지를 나타낸다. p는 row들과 field들의 matrix이다.

한번에 한개 이상의 row들을 가질수 있다고 해도 나는 한번에 한개의 row를 처리하는걸 더 선호한다. 하지만 여러분들을 여러분들 방식대로 하면 된다. 배열등을 이용해서 한번에 여러개의 row들을 처리할 수도 있다. GetRows 메소드는 recordset안의 pointer 이동할 것이다. 그래서 다음 record를 읽을 수 있도록 point할 것이다. 위 예제를 보면 알 수 있듯이 p(0,0) 이렇게 배열을 이용하면 첫번째 field를 말하는 거고 두번째 field는 p(1,0)이 될 것이다.

이렇게 하면 우리가 필요한 데이터를 받아서 엑셀파일 어딘가에 copy해 넣을 수 있고 혹은 어뜬 계산을 하던지 해서 관리를 할 수가 있게 된다

일단 DB와의 작업이 끝나면 이 connection을 close 시켜야 한다. 그리고 모든 객체들을 free 시켜야 하고. 이 작업을 위해서는 아래와 같이 처리하면 된다.

Additional useful informations

이 글을 마치면서 몇가지 쉽지 않은 작업에 대해서 언급하고 마무리 짓겠다.

여러개의 데이터베이스에 여러개의 connection을 open 할 수 있다. 그리고 여러개의 Recordset과 Command를 만들수도 있고. 이렇게 하면 데이터를 한 데이터베이스에서 다른 데이터베이스로도 옮길 수 있다.

오직 SELECT만이 recordset에 어떤 결과를 보내준다. DELETE나 INSERT INTO 뭐리를 가지고 surf할 생각은 말라.


어떤 쿼리에 에러가 있어서 실행시 에러가 발생했다면. 이 때 우리는 뭔가 잘못 됐다는 것을 알아야 한다.

이 ErrorHandler 부분은 Sub 안의 아무곳에나 넣으면 된다. 만약 Error가 발생하면 VBA는 이 label을 볼것이고 if문 안에 있는 코드를 실행할 것이다. 여기서는 Err.Description을 MsgBox에 표시하도록 해 놨다.

Teradata Macro 에 SELECT 쿼리를 include하는 것이 가능해도 Teradata Macro에서는 DELETE, INSERT, UPDATE 쿼리를 사용하고 SELECT 쿼리는 따로 일반적인 쿼리처럼 사용하라고 권하고 싶다.

가끔 double quote 문자 (큰 따옴표)를 사용해야 할 때가 있다. 그런데 이것을 그냥 문자열 내에 사용하면 그 따옴표가 있는 부분을 문자열의 끝으로 인식할 수 있다는 것을 염두해 두자.

오늘 동료가 두개의 sheet에 있는 데이터를 비교하는 엑셀 프로그래밍을 문의했는데요.

구글링 해 보니까 아주 좋은 소스를 누가 올려 놨네요.

한번 분석해 봐야겠어요.

Public Sub ReconReport() 
    Dim rngCell As Range 
     
    For Each rngCell In Worksheets("Sheet1").UsedRange 
        If Not rngCell = Worksheets("Sheet2").Cells(rngCell.Row, rngCell.Column) Then _ 
        Let Worksheets("Sheet3").Cells(rngCell.Row, rngCell.Column) = rngCell 
    Next 
End Sub 

처음에 rngCell 이라는 Range 를 정의했습니다.

그리고 For문을 돌리는데요.

Sheet1 이라는 worksheet의 사용된 각 cell들 만큼 루프를 돕니다.

이거 아주 유용한 라인이네요.

UsedRange Property 는 아주 유용하게 이용될 수 있을 것 같습니다.

Worksheets("Sheet1").Activate
ActiveSheet.UsedRange.Select

이렇게 하면 Sheet1 중에 데이터가 있는 셀은 모두 선택이 되겠죠.

데이터가 있는 셀 중에 첫번째 row와 맨 마지막 row를 구하려면 아래와 같이 하면 됩니다.

FirstRow = ActiveSheet.UsedRange.Rows(1).Row 
LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row 
 'or
LastRow = ActiveSheet.UsedRange.Rows(UBound(ActiveSheet.UsedRange.Value)).Row 

특정 Field의 마지막 row를 구할 땐 End(xlUp).Row 를 사용할 수도 있습니다.

Sub SelectActualUsedRange()
  Dim FirstCell As Range, LastCell As Range
  Set LastCell = Cells(Cells.Find(What:="*", SearchOrder:=xlRows, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
      Cells.Find(What:="*", SearchOrder:=xlByColumns, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Column)
  Set FirstCell = Cells(Cells.Find(What:="*", After:=LastCell, SearchOrder:=xlRows, _
      SearchDirection:=xlNext, LookIn:=xlValues).Row, _
      Cells.Find(What:="*", After:=LastCell, SearchOrder:=xlByColumns, _
      SearchDirection:=xlNext, LookIn:=xlValues).Column)
  Range(FirstCell, LastCell).Select
End Sub

Sub GetLastRowWithData()
  Dim LastRow As Long
  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
     SearchDirection:=xlPrevious, LookIn:=xlValues).Row
  MsgBox LastRow
End Sub

구글링 하다 보니까 다양한 예제들이 나오네요.

나중에 좀 분석해 봐야 겠습니다.

다시 원래 보던 예제를 볼까요.

For 문 안에 보면 if 문이 나옵니다.

이 if문을 번역해 보죠.

If Not rngCell = Worksheets("Sheet2").Cells(rngCell.Row, rngCell.Column) Then

Sheet1의 각 Cell 이 같은 위치의 Sheet2의 Cell 값과 같지 않으면 

Let Worksheets("Sheet3").Cells(rngCell.Row, rngCell.Column) = rngCell 

Sheet3의 같은 위치에 Sheet1의 해당 값을 넣습니다.

거의 영어 해석하는 것 같네요.

Sheet의 모든 값을 비교하는 로직을 이렇게 간단히 구현할 줄이야....

Excel Programming 하면 할 수록 더 재밌어 지는데요.

엑셀 프로그래밍에서 다른 함수를 호출할 때는 아래와 같이 하면 됩니다.

Call function01

호출시 파라미터를 전달하려면 아래와 같이 하구요.

Call function01("test",1)

function01은 아래와 같이 선언되어 있어야 겠죠.

Sub TotalFuel(text As String, firstNum As Integer)

......

End Sub

함수에서 값을 return 할 경우 아래와 같이 합니다.

Public Function test() As Integer
    test=1
End Function

값을 return 할 때는 Sub 대신에 Function을 사용해야 하나 봅니다.

아직 값을 return 하는 것은 사용해 보지 않았는데 한번 다양하게 사용해 봐야겠네요.

지난번 사용한 테이블에다 Option1 과 Option2 필드를 추가했습니다.

위 그림처럼 Option1 의 0.00 으로 필터링 한 후 Aug 필드의 합계를 구하겠습니다.

며칠 전에 만든건데 지금와서 소스를 보니까 저도 생소하네요.

일단 Seq 필드와 option2 필드 Range를 담을 변수를 만들었습니다.

제 기억으로는 Option2는 사용되질 않는데 아마 Option1을 담을 Range인데 타이핑을 잘 못 했나 봅니다.

그리고 합계 구할 Row 의 범위를 알기 위해 firstRow와 lastRow를 Long 타입의 변수로 정의했습니다.

첫번째 줄은 그냥 아무 셀이나 Select 한 거구요.

그 다음을 보니까 링크를 클릭했습니다. 이건 지난번에 만들었던 소스코드에도 그대로 나오는 겁니다. 이렇게 하면 링크 걸린 페이지로 가겠죠? 링크 걸린 페이지는 위에 보이는 테이블이 있는 페이지 입니다.

그 다음은 일단 이전에 작업할 때 필터링이 된게 있을 지도 모르니까 이 필터링 된 걸 풀고 모든 값을 보이도록 하는 코드 입니다.

그런데 필터링이 안 돼 있으면 이 부분에서 에러가 나니까 이 에러를 나지 않도록 처리도 했습니다.

다음은 State 라는 값을 가진 Cell을 찾아서 Activate 시키고 여기서 쭉 밑으로 데이터가 있는 마지막 Cell 까지 값니다.

그리고 그 셀의 Row를 lastRow에 담습니다.

이 부분은 fistRow를 구하는 부분까지 잘랐는데 뭔 코드가 이렇게 긴지 모르겠네요.

한번 볼까요?

우선 Seq 라는 값을 가진 셀을 찾아서 그 셀을 seqRng에 담습니다. (이건 왜 만들었을까? 지금 봐서는 잘 이해가 안 가네요.)

하여간 그 다음에는 Option2로 검색해서 그것을 option2Rng에 담습니다.

이것도 왜 담는건지 모르겠네요.

다음은 Option1이라는 값을 가진 Cell을 찾아서 그 Cell을 Activate 시킵니다.

앗 잠깐만요...

그 아래에 필터링 하는 부분이 있군요.

거기서 seqRng와 option2Rng가 사용됩니다.

괜히 그 값을 구해서 변수에 담은게 아니었군요.

이렇게 되면 Option1을 Find로 찾아서 Activate 시킨 부분은 아무 의미 없는 코딩 같은데요.

한번 계속 볼까요?

일단 필터링이 있는 범위를 Range로 정하고 나서 몇번째 필드를 필터링 할 건지 알려주고 필터링 할 값은 0.00으로 정해 줍니다.

이렇게 되면 Option1 필드 중 0.00을 가진 놈들로만 필터링이 됩니다.

다음에 하는 일은 Aug라는 값을 가진 셀을 찾아서 그 아래 셀을 Activate 시킵니다.

여기까지 보니까 확실하게 Option1이라는 값을 가진 셀을 Find 한 부분은 필요가 없는 부분이란게 확실 하네요.

이제 Aug셀의 바로 아래 Row를 firstRow로 지정했습니다.

이렇게 되면 firstRow와 lastRow가 모두 구해 졌습니다.

첫번째 줄은 Aug 필드의 맨 마지막 Row 바로 밑을 Activate 시킨 겁니다.

거기다가 구한 합계를 넣기 위해서죠.

그 밑에 줄에서 SUBTOTAL을 사용하는데요.

이 함수는 필터링된 값만 가지고 합계를 내기 위해 사용했습니다.

그 안에 9는 합계를 낼거라는 의미이구요.

끝에 R[-1]C는 현재 Activate 된 셀의 바로 윗줄을 말하는 겁니다.

Aug 컬럼 중 합계를 구할 맨 밑줄이 되죠.

그 바로 전에는 Aug 셀의 바로 밑셀을 구하는 겁니다.

Aug 셀의 바로 밑셀부터 맨 밑셀까지를 정해 준거구요. 그 값들을 SUBTOTAL로 합계를 내게 됩니다.

그러면 그 합계가 맨 위에 있던 테이블 그림 처럼 Aug 필드의 맨 밑에 있는 셀 바로 밑에 표시 됩니다.

그 다음에는 그 값을 Copy 하구요. New_Sheet로 가서 적당한 위치에 복사한 값을 붙여넣기를 합니다.

이제 이정도 까지만 하면 제가 업무 하면서 만들고 싶었던 매크로를 만들 수 있겠네요.

필요한 기술은 다 Research 한 것 같습니다.

저는 이제 제가 필요한 매크로를 만들어야 겠습니다.

처음에는 새로운 Sheet를 만들었고 그 다음에는 특정 필드의 값들의 합계를 구했고 이번에는 필터링한 후 특정 필드의 값들의 합을 구했습니다.

지금까지 만든 소스코드는 Research 목적으로 만든거라서 실제 사용하려면 Refactoring 과정을 거쳐야 합니다.

불필요한 코드들은 없애고 좀 더 간단하게 만들 수 있는 로직은 간단하게 고치고 반복적으로 사용되는 로직은 따로 Component 를 만들어서 사용할 수 있도록 고치고 등등이요.

혹시 이 Refactoring 과정을 거치지 않은 소스코드라도 참고하시고 싶으신 분들을 위해서 제가 작업한 파일을 아래에 올려 놓을께요.

ForTesting.xlsm