애들센스


심심한 김에 회사에 재능기부?-월급 받잖아~ 오피스/VBA/Office.JS

많은 사람들이 매일 하는 단순작업을 자동화하는 것이 VBA인데, 그런 작업중 하나의 예를 가지고 최선의 VBA코드를 만들어 보려고 한다. 일단 개인적인 의견이지만 최선의 VBA코드는 코드를 되도록 줄이는 것이다. 아예 VBA코드가 없는 게 최선의 VBA프로그래밍이다. 이게 무슨 개똥같은 철학같은 소리이고 어불성설인가? 가지 많은 나무에 바람 잘 날 없다는 속담이 딱 들어 맞는다. 가령 아래와 같은 워크시트가 있는 데, 둘 다 같은 목적과 작업순서를 가진다.

B열의 값이 1.0 이상인 종목에 대하여 C~G열까지 복사하기
(이번 경우에는 C4:G8 (하이라이트 친 부분)이 복사할 셀영역이다)


I열의 값이 25.0 이상인 종목에 대하여 J~Q열까지 복사하기
(이번 경우에는 J4:G7 (하이라이트 친 부분)이 복사할 셀영역이다)


VBA 로 나름 최선의 코드를 만들면 다음과 같다.
Sub GapOver()
    On Error Resume Next
    Const CELL_BEGIN = "B4"
    Call CpyRange(Range(CELL_BEGIN), ">=1", 5)
End Sub

Sub BigPL()
    On Error Resume Next
    Const CELL_BEGIN = "I4"
    Call CpyRange(Range(CELL_BEGIN), ">=25", 8)
End Sub

Sub CpyRange(rngCrit As Range, strCrit As String, numCols As Long)
    Dim cnt As Long
   
    cnt = WorksheetFunction.CountIf(Range(rngCrit, rngCrit.End(xlDown)), strCrit)
    If cnt < 1 Then Exit Sub
    Range(rngCrit.Offset(0, 1), rngCrit.Offset(cnt, 0).Offset(-1, numCols)).Copy
End Sub
 같은 작업이므로 CpyRange() Sub프로시저를 공통으로 만들어 호출한다.
GapOver Sub 프로시저는 판단을 할 셀 영역의 시작부분과 판단기준, 복사할 컬럼의 수를 던져준다(Range(CELL_BEGIN), ">=1", 5)
마찬가지로 BigPL Sub 프로시저는 판단을 할 셀 영역의 시작부분과 판단기준, 복사할 컬럼의 수를 던져준다(Range(CELL_BEGIN), ">=25", 8)

이제 세 개의 매개변수를 넘겨받는 CpyRange() 프로시저는 조건에 맞는 셀의 갯수를 세어서 범위를 계산하고 복사한다. 이렇게 하면 일일이 셀 영역을 지정하여 복사하지 않고 버튼 하나만 누르면 자동으로 클립보드에 복사가 된다. 이후 붙여넣기는 알아서 쓰면 되는 거구...

그런데 코드를 더 줄일 수 있지 않을 까 하는 씰데없는 궁리를 해본다. 가장 중요한 기능-셀 영역을 판단하여 잡는 것-을 VBA가 아닌 엑셀로 할 수 있지 않나...그래서 고안한 아이디어는 OFFSET(), COUNTIF() 함수를 사용하여 셀영역에 이름을 붙이는 것이다.


수식 =OFFSET('일일 리스크관리'!$C$4,0,0,COUNTIF('일일 리스크관리'!$B$4:$B$53,">=1"),5)=OFFSET('일일 리스크관리'!$J$4,0,0,COUNTIF('일일 리스크관리'!$I$4:$I$53,">=25"),8) 는 COUNTIF()의 결과에 따라 동적인 셀 영역을 만들어 준다. COUNTIF()함수는 VBA 코드에서 사용한 바 있는 데, 조건을 만족하는 셀의 갯수를 돌려주는데, 이는 복사할 행의 갯수를 의미한다.

수식이 완성되었으므로 코드는 다시 더 줄어든다.
Sub GapOver()
    Range("종목과다").Copy
End Sub

Sub BigPL()
    Range("손익과다").Copy
End Sub

PS.무료로 간단한 매크로 만들어 드립니다. 대신 이 블로그의 글감으로 사용할 수 있습니다. 그러니 민감한 정보는 주지마세요.

덧글

댓글 입력 영역