IT 기술 따라잡기

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

그래서 찾아 봤는데요.

아래 같이 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

이번에는 특정 컬럼의 숫자를 더하는 코드를 만들어 보겠습니다.

제가 그냥 만들어본 표 입니다. (State는 제가 있는 Minnesota 의 약자이구요. 그 안의 값들은 그냥 임의로 넣은 겁니다.)

여기서 Aug 컬럼의 숫자들을 더한 후 New_Sheet 라는 Sheet 에 그 더한 값을 넣겠습니다.

여기서 조건을 문서를작성하다 보면 양식은 위와 같이 되지만 값의 Cell 값은 바뀔 수 있는 상황입니다.

예를 들어 어떤 사람은 이 표를 엑셀 쉬트의 A1 서부터 작성하는 사람이 있고 또 어떤 사람은 한 한 B3 정도부터 작성하는 사람이 있습니다.

그러면 각 값들의 위치들도 그에 따라 달라질 건데요.

이럴 경우에도 구애 받지 않고 Aug 컬럼 값들의 합계를 구해 보겠습니다.

그러기 위해 우선 Aug 의 위치를 알아 내겠습니다.

그리고 바로 그 밑의 Cell 값을 받고 맨 마지막의 Cell 값을 알아 냅니다.

그래서 그 Cell 들의 값을 더하겠습니다.

여기서 어떤 달들은 중간중간 혹은 맨 처음이나 맨 마지막 값이 비어 있을 수 있습니다.

그래서 맨 마지막 Cell 값은 항상 값이 채워져 있는 Seq 나 State로 가서 값이 있는 맨 마지막 Cell을 알아 내겠습니다.

우선 이런한 작업을 할 때 값을 담을 변수들을 만들겠습니다.

firstCell과 lastCell 은 Aug 컬럼의 첫번째와 마지막번째 Cell 값입니다. 

state1stCell 은 state 컬럼의 첫번째 Cell 값이구요.

sumCell은 Aug 컬럼의 값들을 모두 더한 값을 넣을 변수 입니다.

그리고 lastRow는 Long 타입인데요. State 컬럼에서 값이 있는 맨 마지막 Row를 구해서 넣을 변수 입니다.

지난번에 올린 엑셀 파일을 보면 저 테이블은 두번째 Sheet에 있고 첫번째 Sheet 에 저 Sheet로 가는 링크가 있습니다.

우선 이 링크를 클릭하는 코드를 작성하겠습니다.

일단 첫번째 sheet 에서 이 함수를 실행시킨다고 가정하구요.

아무곳에나 커서를 놓습니다. (여기서는 B4).

그리고 첫번째 링크를 클릭합니다.

그러면 두번째 Sheet로 이동했을 겁니다. 여기서 Find 명령어로 Aug라는 필드를 찾아서 그 Cell 을 Activate 합니다.

(위 코드는 Macro를 실행해서 만든 코드 입니다.)

여기까지 하면 아래와 같이 Aug 컬럼이 선택 됩니다.

그러면 Aug 컬럼의 첫번째 Cell 을 구할 수 있습니다.

현재 활성화된 Cell (여기서는 Aug) 에서 한칸 아래로 내려가서 그 Cell을 firstCell 에 담았습니다.

이제 firstCell 에는 62.28 이 있는 Cell 이 담겨져 있습니다.

그 다음에는 Find 를 사용해서 State 가 있는 Cell을 찾아서 활성화 시킵니다.

이제 그 활성화 된 Cell 바로 아래 Cell을 state1stCell 에 담았습니다.

그리고 그 state1stCell을 다시 활성화 시킵니다.

이제 State 컬럼의 첫번째 Row 에 있는 Cell 이 활성화 돼 있을 겁니다.

이제 End(xlDown)을 이용해서 State 컬럼의 맨 마지막 Row로 이동합니다.

그리고 그 row의 값을 lastRow에 담습니다.

이제 값이 있는 맨 마지막 Row의 값을 알았으니 다시 Aug 컬럼으로 가겠습니다.

firstCell 에서 State 컬럼에서 얻은 맨 마지막 Row 값을 이용해서 Aug의 맨 마지막 Row로 갑니다.

그리고 이 Aug의 맨 마지막 Row를 lastCell에 담습니다.

이러면 Aug의 맨 첫번째 Cell 과 맨 마지막 Cell을 얻게 됐습니다.

이것만 알면 Aug의 첫번째 Cell 에서 마지막 Cell 에 걸쳐 있는 값의 총계를 알아 낼 수가 있습니다.

구한 합계는 Aug의 맨 마지막 Cell의 밑에 Row에 적어 넣을겁니다.

그래서 sumCell은 lastCell의 바로 밑의 Cell로 설정해 두었습니다.

위에서도 사용했던 Offset을 여기서도 사용했습니다.

이제 Aug의 첫번째 셀에서부터 마지막 셀까지 범위 설정을 하고 그 합을 sumCell에 넣었습니다.

그리고 그 값을 Copy 했습니다.

이제 클립보드 내에는 Aug 컬럼의 값들의 합이 담겨져 있습니다.

이제 New_Sheet로 이동해서 D3 위치에 복사한 값을 붙여넣기 합니다.

Selection.PasteSpecial은 Macro를 이용해서 구한 코드 입니다.

여기서는 단순하게 붙여넣기를 한게 아니라 그 Value 를 붙여넣기 했습니다.

제가 필요한 것은 그 셀의 공식이 아니라 값이니까요.

이제 복사해 넣은 값의 왼쪽 셀(C3)로 가서 AugSum 이라고 글자를 써 넣습니다.

이렇게 해서 Aug 컬럼의 모든 값을 더해서 New_Sheet 에 복사해 넣는 코드가 완성됐습니다.

이제 저 양식만 맞추면 표를 어느 위치에 작성하든 Aug 필드의 값들의 합을 구할 수가 있게 됐습니다.

전체 소스코드는 아래와 같습니다.

제가 엑셀 프로그래머가 아니라서 코드가 깔끔하지는 않을 겁니다.

배우시는 분들은 이걸 좀 더 깔끔하게 다듬으시면 더 공부가 잘 될 겁니다.

ForTesting.xlsm

필요하신 분은 위 파일 다운 받으시면 안에 소스코드가 있습니다.

Cntr-Shift-A 는 지난 글에서 다뤘던 Sheet 만드는 매크로이고 Cntr-Shift-B 는 그 새로 만든 매크로에 Aug 컬럼의 값들의 합계를 구해 넣는 매크로 입니다.

둘 다 첫번째 Sheet 에서 실행 하셔야 됩니다.

이번에는 새로운 Sheet 를 생성하는 코드를 작성해 보겠습니다.

일단 Excel file 내의 sheet 들의 맨 마지막 위치에 새로운 sheet 를 생성해서 New_Sheet 라고 이름을 붙이겠습니다.

다만 이 New_Sheet 라는 sheet 가 있으면 새로운 sheet를 생성하지 않고 이 New_Sheet 가 있다는 Message Box를 보이겠습니다.

코드는 아래와 같습니다.

이 함수의 이름은 CreateNewSheet() 로 했습니다.

그다음두 줄은 주석입니다.

이 매크로를 생성할 때 단축키는 Ctrl+Shift+A 로 만들었습니다.

이것은 지난 글에서 다룬 Macro 만들기 할 때 단축키를 넣은 것입니다.

아직 코드 내에서 단축키를 만드는 것은 모릅니다.

첫째 줄에 나오는 DIM 은 변수에 공간을 할당하는 statement 입니다.

Worksheet 형태인 sh 라는 변수를 생성했습니다.

그리고 Boolean type 의 flg라는 변수도 만들었구요.

그리고 for loop문이 나옵니다.

Worksheets 내에 있는 Worksheets들을 차례로 불러내서 어떤 일을 할 겁니다.

이때 불러 낼 때는 해당 Worksheet를 sh라는 변수에 담을 거구요.

이것을 하려고 첫째줄에 sh라는 변수 공간을 만들어 둔 겁니다.

For 문 안을 보면 sh에 담겨있는 Worksheets의 이름을 가져와서 "New_Sheet" 라는 문자와 비교를 하네요.

이 문자가 포함돼 있는 sheet 이름이 있으면 flg 라는 Boolean type 변수에 True를 할당합니다.

그리고 For 문을 Exit 하구요.

만약에 없으면 Next 가 실행되서 그 다음 Worksheet를 살펴봅니다.

이 엑셀 파일에 있는 Worksheet 들을 다 돌았으면 For 문에서 빠져 나오게 되겠죠.

이 For Loop 문의 신택스는 아래와 같습니다.

For counter [ As datatype ] = start To end [ Step step ]
    [ statements ]
    [ Continue For ]
    [ statements ]
    [ Exit For ]
    [ statements ]
Next [ counter ]

자세한 것은 링크를 따라가서 보시구요.

보니까 For Each Loop는 좀 다르군요.

신택스는 아래와 같습니다.

For Each element [ As datatype ] In group
    [ statements ]
    [ Continue For ]
    [ statements ]
    [ Exit For ]
    [ statements ]
Next [ element ]

이것도 자세한 것은 링크를 따라가서 보세요.

이제 For Each Loop 에서 flg 에 대한 값이 할당이 됐습니다.

그 다음 if 문에서는 이 flg 가 True 일 경우에는 "New_Sheet Exist!" 라는 글자가 있는 MsgBox를 띄우고 flg가 True가 아닐 경우에는 그 다음일을 합니다.

우선 현재엑셀 파일에서 Worksheet이 맨 마지막으로 가구요.

그 다음에 맨 마지막 Sheet 다음에 새로운 Sheet를 만듭니다.

그리고 그 맨 마지막에 있을 Sheet의 이름을 "New_Sheet"라고 지어 줍니다.

이 If 문의 신택스는 이렇습니다.

' Multiple-line syntax:
If condition [ Then ]
    [ statements ]
[ ElseIf elseifcondition [ Then ]
    [ elseifstatements ] ]
[ Else
    [ elsestatements ] ]
End If

' Single-line syntax:
If condition Then [ statements ] [ Else [ elsestatements ] ]

이렇게 하면 처음에 원했던 대로 엑셀 파일의 맨 마지막 Sheet 에 Work_Sheet 라는 새로운 Sheet를 만듭니다.

기존에 이 Sheet가 있으면 안 만들구요.

ForTesting.xlsm

제가 작업한 파일을 올려 놓으니까 필요하신 분들은 참조하세요.

한가지 Tip 으로 말씀 드릴것은 Macro를 쓰려면 엑셀파일을 Macro가 가능한 버전으로 Save As를 해야 합니다.

그러면 저렇게 확장자가 xlsm 이 됩니다.

그렇게 하지 않으면 기껏 작업한것을 모두 날릴 수 있습니다.

직장 생활을 하다 보면 엑셀을 잘 사용하면 일을 훨씬 편하게 할 수 있겠다라는 생각을 많이 합니다.

그래서 한번 엑셀 프로그래밍을 배우려고 합니다.

그동안 마음만 먹고 있었는데 모처럼 연휴에 여유가 있어서 마음만 먹었던 것을 시작 하려고 합니다.

우선 첫번째로 Macro 를 사용하는 방법을 보겠습니다.

일단 1에서 9까지 적어 넣고 11번째 줄에서는 그 수를 모두 더했습니다. (=SUM(B2:B10))

그 오른쪽은 랜덤 함수를 이용해서 수를 랜덤하게 불러오도록 했습니다. (=RAND()*B2)

이제 이 숫자와 수식을 오른쪽에 복사해 넣을 건데요. 이 과정을 매크로로 저장하겠습니다.

첫번째로 View - Macro - Record Macro 를 선택합니다.

그러면 위와 같은 화면이 뜨는데요. 매크로 이름을 넣고 단축키를 정한 다음에 OK 버튼을 클릭합니다.

그러면 그 이후부터 진행되는 과정은 전부다 저장이 될 겁니다.

OK 버튼을 누르고 아까 작성했던 숫자와 수식들을 복사해서 그 옆에 붙여 넣겠습니다.

위와 같이 복사해 넣고 다시 View-Macro로 가셔서 이번에는 Stop Recording을 선택합니다.

그리고 나서 오른쪽에 있는 부분을 지워보세요.

아까 저는 제가 만든 매크로(Macrotest01)의 단축키를 Ctrl-Shift-a 로 했으니까 이 단축키를 누르겠습니다.

여러분은 여러분이 정한 단축키를 눌러 보세요.

그러면 아까 범위를 정하고 복사한 다음에 복사해 넣을 위치에 와서 복사한 내용을 붙여넣기 한 과정이 자동으로 빠르게 반복되면서 값이 복사될 겁니다.

View-Macro로 가셔서 View Macro를 선택하시면 위와 같은 화면을 보실 수 있습니다.

방금 전에 만들었던 Marcotest01이 있죠?

여기서 Run 버튼을 누르셔도 해당 매크로가 실행 됩니다.

지금 프로그래밍을 하나도 하지는 않았지만 사실은 Macro를 Recoding 하는 과정에서 Excel 이 프로그래밍을 자동으로 한 겁니다.

여기서는 Visual Basic이 사용되는데요.

이 과정이 어떻게 프로그래밍이 됐는지 보시죠.

소스를 보시려면 View-Macro로 View Macro를 선택하신 후 Step Info 단추를 누르셔도 되고 그냥 엑셀쉬트에서 Alt-F11 을 누르셔도 됩니다.

Alt-F11을 누르시면 위와 같이 Visual Basic Editor가 나옵니다.

Module1을 더블 클릭하면 소스가 보이는데요.

Visual Basic은 잘 모르지만 소스를 한번 보죠.

Sub라고 돼 있는데 이건 잘 모르겠구요. 자바에서 말하는 메소드나 C에서 말하는 함수 정도 되는것 같습니다.

메소드 이름이 Macrotest01() 이지요. 아까 매크로 만들때 정했던 이름입니다.

그리고 주석을 보면 Ctrl+Shift+A 가 단축키라고 나와 있구요.

그 밑에서부터 메소드의 내용인데요.

우선 B2부터 C11 까지 선택을 합니다.

그리고 선택한 부분을 Copy 하구요.

그리고 E2 부분으로 가서 Paste를 합니다.

그 다음에 나오는 Application.CutCopyMode=False는 Copy 할 부분을 설정하면 엑셀에서는 그 부분이 점선으로 표시되는데 Esc를 누르지 않는한 그 부분이 계속 클립보드 최 우선순위에 있게 되죠.

여기서는 그 선택한 것을 해제 하는 겁니다.

그 다음 줄은 End Sub으로 메소드의 끝을 알립니다.

자바에선 {} 로 시작과 끝을 처리했는데 비주얼 베이직에서는 Sub과 End Sub으로 메소드 시작과 끝을 처리하나 봅니다.

오늘은 첫시간으로 엑셀의 매크로 기능 활용하는 법과 해당 매크로의 비주얼 베이직 소스 보기를 살펴 봤습니다.

Corona SDK 가 facebook 계정을 통해서 아래 글을 공유했습니다.

좋은 글이라서 정리해 둡니다.

안드로이드 개발자가 유념해야 할 사항 10가지 인데 주로 SEO 와 관련이 있습니다.

Corona SDK 카테고리로 분류할 지 안드로이드 카테고리로 분류할 지 고민하다가 SEO 카테고리로 넣었습니다.

Top 10 Google Play Developer Mistakes


What you need to know about app store optimization and SEO for the Google Play Store and Android.
Google Play Store 와 안드로이드에 대한 앱 스토어 최적화와 SEO를 위해 알아두어야 할 것들

Top 10 Google Play Developer Mistakes

#1: Using Unfriendly SEO App Names

It’s important to have clear and descriptive app names. For improved discoverability, consider long names that combine branding and description of the app.

분명하고 앱을 잘 설명하는 앱 이름을 짓는 것은 중요하다. 

#2: Not Optimizing App Description

The first lines of the description are the most important from user and SEO perspective. First tell the user what the app is about then list out the features and awards.

사용자나 SEO적인 관점에서 보면 앱을 설명할 때 그 첫번째 문장이 아주 중요하다. 유저에게 이 앱이 무엇인지 얘기하고 나서 그 기능들을 설명하라.

#3: Not Having a Video Trailer

A short, keyword optimized video can help your app’s SEO and gain user adoption. Users find the video as the most convincing feature of the app detail page.

짧고 핵심을 설명하는 비디오는 유저들에게 매력적으로 다가가고 여러분 앱의 SEO에 도움이 될 것이다. 유저들은 그 비디오를 앱의 자세한 기능을 설명하고 있다는 확신을 줄 것이다.

#4: Forgetting About Personalization

Allow users to easily rate, review, and +1 your app. Google shows recommendations based on 80 signals including: location, circles, and +1 within circles.

유저가 여러분 앱에 대해 쉽게 rate, review 그리고 +1을 하도록 하라.

#5: Only Focusing on Downloads

Focus on your app’s engagement. Google measures length of install and app activity and will not penalize if a user uninstalls after using an app for awhile.

#6: Not Designing for Tablets

Designing your app for the tablet experience and failing to get the “Designed for Tablets” designation.

여러분 앱을 태블렛에 맞추는 작업을 하라.

#7: Using Unhelpful Anchor Tags

Instead of using the words “Google Play” to link to your app’s Google Play URL, use descriptive terms to help boost your app’s ranking for those keywords.

여러분 앱의 Google Play URL 을 달 때 단지 Google Play 라고만 하지 말고 여러분 앱의 ranking을 올리는데 도움을 줄 수 있는 키워드 같은 것들을 사용하라.

#8: Misspelling Popular Apps

While 50% of search queries are misspellings, do not choose app names that are close variations or misspellings of popular apps. Google will auto correct the query.

검색 단어 중 50%는 철자가 틀리다. 유명한 앱의 다른 철자나 비슷한 단어를 사용하지 마라. 구글이 자동으로 그 검색 단어의 철자를 고쳐 여러분 앱을 보여주지 않을 것이다.

#9: Making Your APK Too Large

Smaller APKs get installed more and uninstalled less. As users run out of memory they will uninstall apps based on size and percentage of usage.

APK 파일 사이즈가 작을수록 더 많이 인스톨 되고 덜 uninstall 된다. 유저들은 메모리가 부족하면 사이즈를 보고 uninstall 을 결정하는 경우가 많다.

#10: Not Creating a Viral Loop

Use Google+ Recommendations API to allow users to +1 an app from within your app. Integrate with Google Play’s game services to make your app more social.

유저가 여러분 앱 안에서 +1을 할 수 있도록 Google+ Recommendations API를 사용하라. Google Play 의 게임 서비스를 사용해서 여러분 앱을 좀 더 social 하게 만들어라.