반응형
블로그 이미지
개발자로서 현장에서 일하면서 새로 접하는 기술들이나 알게된 정보 등을 정리하기 위한 블로그입니다. 운 좋게 미국에서 큰 회사들의 프로젝트에서 컬설턴트로 일하고 있어서 새로운 기술들을 접할 기회가 많이 있습니다. 미국의 IT 프로젝트에서 사용되는 툴들에 대해 많은 분들과 정보를 공유하고 싶습니다.
솔웅

최근에 받은 트랙백

글 보관함


지난번 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 사용법이 약간 다른가 봅니다.

Sub ReadFile()

'again, we need this strange thing to exist so that ...

Dim fso As New FileSystemObject

'the file we're going to read from

Dim ts As TextStream

'... we can open a text file with reference to it

Set ts = fso.OpenTextFile("C:\Wise Owl\info.txt", ForReading)

'keep reading in lines till no more

Dim ThisLine As String

Dim i As Integer

i = 0

Do Until ts.AtEndOfStream

ThisLine = ts.ReadLine

i = i + 1

Debug.Print "Line " & i, ThisLine

Loop

'close down the file

ts.Close

End Sub


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


반응형

Comment

[VBA] 디버깅 툴 알아보기

2014. 3. 2. 11:35 | Posted by 솔웅


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를 선택할 수도 있습니다. 그러면 선택된 라인에서 재 실행할 겁니다.


SectionBreak


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


반응형

Comment


영하 25도

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

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

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

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


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

요구 조건은 이겁니다.


모든 Sheet 에 있는 특정 컬럼에 있는 값들과 특정 Sheet에 있는 특정 컬럼에 있는 값들을 비교하라.

특정 Sheet의 특정 컬럼에 있는 값들은 몇개의 패턴으로 문자열들이 결합돼 있으니 이것을 split 한 다음에 비교해야 한다.


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

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


Sub AllSheets()
    Dim ws As Worksheet
    For Each ws In Worksheets
        Call checkFieldName(ws)
    Next ws
End Sub


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





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


Sub checkFieldName(ws As Worksheet)
    Dim rngCell As Range
    Dim allCell As Range

    For Each rngCell In Worksheets(ws.Name).UsedRange
        If rngCell.Value = "필드 제목 1" Or rngCell.Value = "필드 제목 2" Then
            Set allCell = Range(rngCell.Offset(1, 0), rngCell.End(xlDown))
            Call CheckReportName(ws, allCell)
        End If
    Next

End Sub


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

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

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


Sub CheckReportName(ws As Worksheet, allCell As Range)
    Dim checkName As String

    For Each Cell In Sheets(ws.Name).Range(allCell.Address).Cells
        checkName = checkExist(Cell.Value)
       
        If (UCase(checkName) = "Y") Then
            Cell.Interior.Color = RGB(36, 36, 255)
        End If
       
        If (UCase(checkName) = "Y1") Then
            Cell.Interior.Color = RGB(255, 255, 143)
        End If
    Next Cell
End Sub


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

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

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


Public Function checkExist(cellVal As String) As String

        Dim tmpArr1 As Variant
        Dim tmpArr2 As Variant
        Dim testStr1 As String
        Dim testStr2 As String
        Dim omitText As String
        Dim checkEx As String
       
        checkEx = "N"
   
    For Each Cell In Sheets("특정 Sheet").Range("범위").Cells
   
        If InStr(1, Cell.Value, "첫번째") > 0 Then
            If InStr(1, Cell.Value, "my.Reports") > 0 Then
                omitText = Mid(Cell.Value, 13, Len(Cell.Value))
            Else
                omitText = Cell.Value
            End If
           
                tmpArr2 = Split(omitText, "첫번째")
                testStr1 = tmpArr2(0)
                testStr2 = tmpArr2(1)
               
                If InStr(UCase(testStr1), UCase(cellVal)) <> 0 Or UCase(cellVal) = UCase(testStr1) Then
                    checkEx = "Y"
                End If
               
                If InStr(UCase(cellVal), UCase(testStr1)) <> 0 Or UCase(cellVal) = UCase(testStr1) Then
                    checkEx = "Y1"
                End If
               
                If InStr(UCase(testStr2), UCase(cellVal)) <> 0 Or UCase(cellVal) = UCase(testStr2) Then
                    checkEx = "Y"
                End If
               
                If InStr(UCase(cellVal), UCase(testStr2)) <> 0 Or UCase(cellVal) = UCase(testStr2) Then
                    checkEx = "Y1"
                End If
        Else
                If InStr(UCase(Cell.Value), UCase(cellVal)) <> 0 Or UCase(cellVal) = UCase(Cell.Value) Then
                    checkEx = "Y"
                End If
               
                If InStr(UCase(cellVal), UCase(Cell.Value)) <> 0 Or UCase(cellVal) = UCase(Cell.Value) Then
                    checkEx = "Y1"
                End If
        End If
   
    Next Cell
   
   
    checkExist = checkEx
End Function


이 함수는 특정 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 좀 해 봐야겠습니다.


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


반응형

Comment

  1. 영하 25도 와 30도라니....
    상상만해도 춥습니다.
    건강조심하시고 좋은 하루 보내세요!

[VBA] Userform 만들기

2014. 2. 21. 06:28 | Posted by 솔웅


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들을 클릭하면 된다.

Control Name Caption
Userform DinnerPlannerUserForm Dinner Planner
Text Box NameTextBox  
Text Box PhoneTextBox  
List Box CityListBox  
Combo Box DinnerComboBox  
Check Box DateCheckBox1 June 13th
Check Box DateCheckBox2 June 20th
Check Box DateCheckBox3 June 27th
Frame CarFrame Car
Option Button CarOptionButton1 Yes
Option Button CarOptionButton2 No
Text Box MoneyTextBox  
Spin Button MoneySpinButton  
Command Button OKButton OK
Command Button ClearButton Clear
Command Button CancelButton Cancel
7 Labels No need to change Name:, Phone Number:, etc.



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

Show the Userform

Userform을 보이려면 worksheet에 command button을 만들고 아래 코드를 실행하세요.

Private Sub CommandButton1_Click()
    DinnerPlannerUserForm.Show
End Sub



이제 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. 아래 코드를 복사해 넣는다.

Private Sub UserForm_Initialize()

'Empty NameTextBox
NameTextBox.Value = ""

'Empty PhoneTextBox
PhoneTextBox.Value = ""

'Empty CityListBox
CityListBox.Clear

'Fill CityListBox
With CityListBox
    .AddItem "San Fransisco"
    .AddItem "Oakland"
    .AddItem "Richmond"
End With

'Empty DinnerComboBox
DinnerComboBox.Clear

'Fill DinnerComboBox
With DinnerComboBox
    .AddItem "Italian"
    .AddItem "Chinese"
    .AddItem "Frites and Meat"
End With

'Uncheck DataCheckBoxes

DateCheckBox1.Value = False
DateCheckBox2.Value = False
DateCheckBox3.Value = False

'Set no car as default
CarOptionButton2.Value = True

'Empty MoneyTextBox
MoneyTextBox.Value = ""

'Set Focus on NameTextBox
NameTextBox.SetFocus

End Sub



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


Assign the Macros

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

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

Private Sub MoneySpinButton_Change()

MoneyTextBox.Text = MoneySpinButton.Value

End Sub



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

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

Private Sub OKButton_Click()

Dim emptyRow As Long

'Make Sheet1 active
Sheet1.Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer information
Cells(emptyRow, 1).Value = NameTextBox.Value
Cells(emptyRow, 2).Value = PhoneTextBox.Value
Cells(emptyRow, 3).Value = CityListBox.Value
Cells(emptyRow, 4).Value = DinnerComboBox.Value

If DateCheckBox1.Value = True Then Cells(emptyRow, 5).Value = DateCheckBox1.Caption

If DateCheckBox2.Value = True Then Cells(emptyRow, 5).Value = Cells(emptyRow, 5).Value & " " & DateCheckBox2.Caption

If DateCheckBox3.Value = True Then Cells(emptyRow, 5).Value = Cells(emptyRow, 5).Value & " " & DateCheckBox3.Caption

If CarOptionButton1.Value = True Then
    Cells(emptyRow, 6).Value = "Yes"
Else
    Cells(emptyRow, 6).Value = "No"
End If

Cells(emptyRow, 7).Value = MoneyTextBox.Value

End Sub



우선 Sheet1을 Activate 하고 emptyRow 를 찾습니다. emptyRow 변수는 첫번째 빈 row입니다 그리고 기록이 될 때마다 하나씩 증가하게 됩니다. 그리고 나서 하는 일이 Userform에 있는 데이터를 emptyRow의 특정 컬럼에 옮겨 놓는 작업입니다.

7. Clear button을 더블클릭한다.
8. 아래의 코드를 추가한다.

Private Sub ClearButton_Click()

Call UserForm_Initialize

End Sub



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

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

Private Sub CancelButton_Click()

Unload Me

End Sub



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


Test the Userform

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

Result:


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



반응형

Comment

[VBA] Developer 메뉴 보이게 하기

2014. 2. 20. 14:03 | Posted by 솔웅


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

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


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


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


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

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



반응형

Comment

[VBA] inputBox 살펴보기

2014. 2. 19. 18:03 | Posted by 솔웅


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


그래서 찾아 봤는데요.


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


Sub test()
    Dim myValue As String
    myValue = InputBox("Give me some input")
    MsgBox myValue
End Sub





Sub test2()
    Dim strResult As String
    strResult = InputBox(prompt:="Please enter amount", _
                                            Title:="Data Entry")
End Sub


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


Public Sub inputExample()
    TestVal = InputBox("type in value.. or not", "Cancel Test")
    If TestVal = "" Then Exit Sub
    MsgBox (TestVal)
End Sub


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


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

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



반응형

Comment

[VBA] Range Return 하기

2014. 2. 11. 17:49 | Posted by 솔웅


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


Public Function Test(c1 As String, r1 As Long, c2 As String, r2 As Long) As Variant
  Set Test = Range(c1 & r1 & ":" & c2 & r2)
End Function


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

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


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

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



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


Sub Sample()
     Dim Ret As Range
     Set Ret = Test("A", 1, "C", 5)
     Ret.Select
End Sub


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

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


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


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




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



반응형

Comment

  1. 익명 2014.02.12 06:53

    비밀댓글입니다

    • 솔웅 2014.02.12 16:53 신고

      초대장 보냈습니다.
      저도 수학에 아주 관심이 많은데....
      프로그래밍 하다 보면 수학 사용하면 훨씬 효율적으로 만들 수 있겠더라구요.
      좋은 글 부탁 드립니다.

[VBA] 엑셀과 Teradata 연동하기

2014. 2. 6. 17:35 | Posted by 솔웅


지금 참여하는 프로젝트에서는 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 해야 한다.



Microsoft ActiveX Data Objects 2.8 Library
Microsoft ActiveX Data Objects Recordset 2.8 Library



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



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



ADODB.Connection
ADODB.Recordset
ADODB.Command



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



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



Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim cmdSQLData As ADODB.Command
Set cmdSQLData = New ADODB.Command



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



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



cn.Open "Data Source=ODBC_connection_name; Database=db_name; Persist Security Info=True; User ID=your_username; Password=your_password; Session Mode=ANSI;"


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



Set cmdSQLData.ActiveConnection = cn
' 이 라인은 이 쿼리가 전달될 데이터베이스가 무엇인지를 얘기하는 것이다.
query = "SELECT * FROM table_name;"
' 실행하기를 원하는 쿼리
cmdSQLData.CommandText = query
' 이 쿼리를 command text로 할당한다.
cmdSQLData.CommandType = adCmdText
' 어떤 종류의 command VBA가 실행될지를 알려준다.
cmdSQLData.CommandTimeout = 0
' 여기에는 아무런 timeout도 설정하지 않을 것이다. 우리가 보낸 쿼리가 실행하는데 얼마의 시간이 걸리던지 결과가 나올때까지 기다리게 된다.
Set rs = cmdSQLData.Execute()
' VBA 가 쿼리를 보내서 실행하도록 하고 그 결과값을 받는다.


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



query = "DELETE FROM table_name ALL;"
cmdSQLData.CommandText = query
cmdSQLData.CommandType = adCmdText
cmdSQLData.CommandTimeout = 0 'No timeout
Set rs = cmdSQLData.Execute()

query = "INSERT INTO table_name (" & Sheet5.Range("A" & z).Value & "," & Sheet5.Range("B" & z).Value & ");"
cmdSQLData.CommandText = query
cmdSQLData.CommandType = adCmdText
cmdSQLData.CommandTimeout = 0 'No timeout
Set rs = cmdSQLData.Execute()
z = z + 1

query = "SELECT * FROM SVKCCP_MSI_D.stanicek_Delivery_per_weeks;"
cmdSQLData.CommandText = query
cmdSQLData.CommandType = adCmdText
cmdSQLData.CommandTimeout = 0 'No timeout
Set rs = cmdSQLData.Execute()

query = "EXEC macro_name ('2009-01-01','2009-02-15','2008-01-01','2008-02-15','2009-02-09','2009-02-15','2008-02-11','2008-02-17');"
cmdSQLData.CommandText = query
cmdSQLData.CommandType = adCmdText
cmdSQLData.CommandTimeout = 0 'No timeout
Set rs = cmdSQLData.Execute()



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



우선 결과들의 어느 지점에 있는지를 알 수 있는 핵심 프로퍼티 두개를 보자.

rs.EOF
' EOF 는 End of File의 약자이다. true이면 받은 결과의 마지막 row에 왔다는 얘기다.

rs.BOF
'BOF는 Begin of File의 약자이다. true이면 받은 결과의 맨 처음에 있다는 얘기다.

아래 경우는 어떤 경우일까?

rs.EOF = True And rs.BOF = True
' EOF도 true이고 BOF도 true라는 얘기는 result에 데이터가 하나도 없다는 얘기다.



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



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


p = rs.GetRows(1)
Sheet1.Range("B" & row).Value = p(0, 0)
Sheet1.Range("C" & row).Value = p(1, 0)
Sheet1.Range("D" & row).Value = p(2, 0)
Sheet1.Range("E" & row).Value = p(3, 0)
Sheet1.Range("F" & row).Value = p(4, 0)
Sheet1.Range("G" & row).Value = p(5, 0)
Sheet1.Range("H" & row).Value = p(6, 0)
Sheet1.Range("I" & row).Value = p(7, 0)
Sheet1.Range("J" & row).Value = p(8, 0)
row = row + 1
Loop



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



rs.MoveFirst



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



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



p = rs.GetRows(1)



이 메소드는 적어도 한개의 값이 있을 경우에 가능하다. 이 값은 몇개의 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 시켜야 하고. 이 작업을 위해서는 아래와 같이 처리하면 된다.


cn.Close
Set cn = Nothing
Set rs = Nothing
Set cmdSQLData = Nothing


Additional useful informations



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



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



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


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



On Error GoTo ErrorHandler '이 라인을 Sub의 시작부분에 넣자.

ErrorHandler:
If (Len(Err.Description) > 0) Then
    MsgBox (Err.Description)
End If



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



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



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

반응형

Comment


오늘 동료가 두개의 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 PickedActualUsedRange()
  Range("A1").Resize(Cells.Find(What:="*", SearchOrder:=xlRows, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
      Cells.Find(What:="*", SearchOrder:=xlByColumns, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Column).Select
End Sub


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 하면 할 수록 더 재밌어 지는데요.



반응형

Comment

  1. 안녕하세요~포스팅 잘보고 갑니다.^^

  2. 홍용기 2014.02.06 02:41

    안녕하세요,
    저는 학생입니다.
    최근에 진행중인 프로젝트를 수행하다보니,
    여러 씨트에 대하여 반복작업을 수행한 후, 다시 종합해야 하는 일을 자주 만나곤 합니다.

    포스팅 덕분에 수작업을 줄일 수 있을거 같은 희망이 생깁니다.
    감사합니다.

    • 솔웅 2014.02.07 00:02 신고

      저도 이제 새로 배우는 중 입니다.
      좋은 정보 있으면 같이 나눠요. ^^


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


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 하는 것은 사용해 보지 않았는데 한번 다양하게 사용해 봐야겠네요.


반응형

Comment

  1. 양동이 2018.06.26 02:22

    그 호출하려는 함수가 다른 곳에 있을 때에도 같은 방법으로 되나요?
    예를 들어 sheet1에서 sheet2나 현재_통합_문서에 만들어둔 함수 function01을 부르려고 할 때도 그냥 하면 될까요?

    • 솔웅 2018.06.27 05:50 신고

      오래되서 구체적으로 어떤지는 잘 모르겠네요.
      일반적으로 프로그래밍 할 때 다른 클래스에 있는 함수를 호출하려면 위에 import or include 로 외부 클래스를 설정한 다음에 객체를 만들어서 그 클래스 안에 있는 함수를 호출 합니다.
      내부 함수가 아니라면 객체를 만들어서 호출 해야 할 겁니다.

이전 1 2 3 4 5 6 다음