VBA Tips..
순환문에서의 변수
For Each rX In rColumn.Cells
.....If rX.Interior.ColorIndex = rColor.Interior.ColorIndexThen
............lSum = lSum + rX.Value
.....End If
Next rX
위의 구문은 아래와 같이 작성하는 것이 좋다
Dim iColor As Integer
iColor=rColor.Interior.ColorIndex
For Each rX In rColumn.Cells
.....If rX.Interior.ColorIndex = iColor Then lSum=lSum+rX
Next
순환문내에서 계속 rColor.Interior.ColorIndex를 계산해내는 것보다는
순환문밖에서 하나의 변수에 한번계산으로 값을 받고
순환문내에서는 값만 전달하도록 한다
그러나 아래와 같은 경우, 순환이 선언되는 부분에서는
순환을 하면서도 Ubound(arrX)를 한번만 계산하니까..
구태여 변수를 선언하지 않아도 된다
For iX=1 To Ubound(arrX)
.....
Next
순환이 선언되는 부분에서는 딱 한번만 계산이 되는 셈이다
그래서 아래와 같이 rData라는 변수를 순환문내에서 다시 잡아도
순환문 시작할때의 rData는 영향을 받지 않는다
Sub test()
Dim rData As Range, iNext As Integer
Set rData = ActiveSheet.Range("A1:A10")
Dim rX As Range
For Each rX In rData
.....iNext = iNext + 1
..........If iNext = 5 Then
..................Set rData = Range("B1:B5")
..................rData.Value = 20
..........End If
......rX = iNext
Next
End Sub
중요한 개념이니 꼭 테스트들을 해보시기 바란다
엑셀계산엔진의 기본|순환참조와 반복계산
VBA와 VBA없는 수식에서의 가장 큰 차이는
순환을 할수 없다는 것이다
몇번을 계속 반복하면서 계산을 시킬수 없는 것이다
하지만 수식에서도
반복하여 계산을 시킬수 있다
말 그대로 반복계산(Iteration)을 시킬수 있는 것이고
이것을 수식을 작성하고 관찰하여 보도록 하자
순환참조를 일부러 일으키는 경우가 여러분들의 업무에
어떤 것이 있을지 찾아 보시고 활용한다면
더욱 좋을 것이다
더불어 INDEX함수와 RAND함수,IF함수의 연습도 하시고!!
HowToUseFormula_iteration.
사용자정의 함수가 안움직여요!!
사용자정의 함수(UserDefinedFunction)을 이럭저럭 만들어서
착착 원하는 값을 뽑아주는데..
어렵쇼...??다른 엑셀함수와 수식은 움직이는데 내가 만든 함수는
딱딱하게 굳어서 안움직여요 ..커서 놓고 때려야 움직여요??!@
안타까운 일이다..기껏 만들었는데..
Function 나의 함수()
......Application.Volatile
......주절..주절 값을 만들고..내보낸다
End Function
위의 빨강줄 하나 더 추가하시면 된다
MS쟁이들이 그러지 않아도 엑셀함수만 사용해도 로드가 걸리는데..
사용자함수까지 로드가 걸리게 하고 싶지 않았던 모양이다
그래서 선택적으로 자동계산되게 하도록 하였고
선택적으로 자동계산을 하는 명령이
Application오브젝트의 Volatile이다
영어단어 의미대로 촐싹거리고 움직이라는 소리다
오른쪽마우스 크릭으로 얻어지는 숨겨진 메뉴2개
어떤 셀에 수식이 있다
이것을 수식을 없애고 다른 장소에 값만 옮기고 싶다
숨겨진 메뉴를 모르면 아마도 복사하여 선택하여 붙여넣기..뭐..복잡을 떨어야 한다
그러나 수식이 있는 셀이 선택된 상태에서 마우스를 테두리에 샤악 올려 놓으면 사방화살표로
바뀔때 오른쪽마우스버튼을 누른 상태로 주욱 끌어다가 원하는 위치에 쌱 놓으면
숨겨진 메뉴가 하나 뜬다..
이곳에서 값만 붙여넣기메뉴가 있으니 이것을 선택하면 간단..
또 다른 메뉴하나는 테두리 우측하단 카망점을 오른쪽마우스를 누른 상태로
주욱 끌어내리면 아주 다양한 숨겨진 메뉴가 또 하나 있으니 이것도 활용하면
좋을 것이다
지킬것은 지키는 코딩습관
종이와 연필료 머리로 구상한것을 대강 정리하고
코딩에 들어간다
코딩을 하면서 생각이 변한다..
어떤 때는 당초에 생각한 것과 전혀 다른 방향으로 자판질을 해대고 있다
그래서 몇일 있다가는 자신도 헷갈린다..
더욱이 갑자기 딴일이 생겨서 딴일에 몰입하다고 다시 딜다보면
전혀 알아 볼수가 없다
그렇게 딴 짓하다가 와서도 척보면 알아볼수 있는 코딩이 좋은 코딩이다
그러려면 최소한 지켜야 할 것들이 있다
Sub 프로시져()
변수 선언부| Declare Section
-----------------------------------------------
Dim rngDatas As Range,rngMemberList As Range,rX As Range
----------------------------------------------
변수값 초기화 | Assign Section
----------------------------------------------
Set rngDatas=Worksheets("XXX").UsedRange
Set rngMemberList=Worksheets("YYY").Range("A1").CurrentRegion...
-----------------------------------------------
작업 부분 | Express Section
-----------------------------------------------
For Each rX In rngDatas.Rows
............작업을 해나가는 구문들..
...........
Next
End Sub
와 같이 큰덩어리로 3개는 반드시 지키고
왠만하면 변수의 이름을 설명적으로 해준다면 나중에 보아도 변수만
살펴보면 한참있다가 보아도 뭔짓을 하고 있었는지 가름하게 되고
쉽게 다시 중단되었던 작업을 할 수 있다
그렇지 않으면 머리 쥐어뜯다가 에이!@#$ 다시 짜는 것이 났겠다
라고 확 지워 버리고 다시 하게 된다
잘못 작성된 코드는 그렇게 날려 버리는 것이 효율적이다
그러나 문서 작성하듯이 최소한 위의 3요소는 잘 정리 정돈하면서
작성한다면 많은 도움이 될 것이다
외국고수들의 것을 보면 죽기 살기로 무식할 정도로 위의 3가지는 지킨다
어느 경우에는 작업부분보다도 변수를 정의하고 초기화시키는 구문이
더 많은 경우도 종종있다
뭔 소린지 잘 이해가 안 될때는 경험자가 하는 것을 그대로
흉내내는 것이 곧 좋은 창작이 된다
한줄 한줄..정성껏 잘 작성된 코드는 하나의 예술이다!!
한줄 한줄..심취하면 그것이 또한 곧 명상이다!!
그래서 이 나이에도 이렇게 즐겁게 한다
이런 즐거움을 같이 느꼈으면 하고 자꾸 떠드는 것이다!!!
무턱대고 순환문만 돌리면 바보 | VBA
초보때는 무턱대고 순환문을 좋아한다..
신기하니까..
그러나 고수가 되면 효율을 생각하게 된다
창고에 물건이 가득한데 X라는 물건을 찾으려고 한다
미련한 놈은 그냥 무턱대고 창고의 첫번째선반에서 부터 마지막 선반까지
찾을 것이고
영특한 놈은 X라는 물건이 창고에 있기는 있는지 확인할 수 있는 방법이
있는지 알아보고 찾기 시작할 것이다
그럼 엑셀프로그래밍에서는 있는지 없는지 어떻게 확인 할수 있을까??
Application.CountIf("창고범위","X")
를 하면 창고에 X가 몇개 있는지 알수 있다
일의 반은 한셈이 된다
Dim iXNumber As Integer,rX As Integer
iXNumber=Application.CountIf(창고,"X")
If iXNumber>0 Then
.....For Each rX In 창고.Cells
..........If rX="X" Then
.................찾았으니 뭔가 하고...
.................iXNumber=iXNumber-1
.................iXNumber는 찾았으니 하나씩 없애주고
..........End If
..........If iXNumber=0 Then Exit For
.....Next
EndIf
위의 것도 어떤 면에서는 비효율
Find메소드와 FindNext메소드를 안다면 iXNumber만큼 Find과 FindNext를
사용하는 것도 하나를 더 아는 사람의 효율이 될 것이다
와 같이 개념으로 무엇을 하던 사전 첵크를 하는 습관은 아주 좋은 습관이다
사용자정의 폼에서 콘트롤 배치시간단축 | UserForm Menu Bar
이런 좋은 도구가 있는데 있는지 조차 모른다
콘트롤을 그릴때 눈이 빠지게 마우스를 달달 거리고 할 필요가 없다
아래의 그림과 같이 메뉴를 하나 선택하고 콘트롤을 여러개를 선택하면
왼쪽으로 맞추기,오른쪽으로 맞추기,가운데로 맟추기등등 편리한 기능이 있다
이것을 활용하면 깔끔하고 빠르게 사용자정의 폼을 정돈 할수 있다
뭔일을 하기전에는 꼭 찍어요!!!| Select First!!!
어이..누가 이것좀 하지!!!
이러면 누가 하겠는가??
어이..길동이 이것 좀 하지..
그럼 길동이가 하게 되어 있다..직장에서 쫓겨나지 않으려면..
그런데 그냥 허공에 대고 떠들면 에너지 낭비다
어느 프로그램이나 마찬가지지만 작업을 하려면 선택이라는 것을 먼저 해야 된다
특히 엑셀이라던가 그래픽프로그램들은 이 습관이 필수고..
이것이 자연스러워 질때 도로주행을 해도 된다
또한 이것이 잘 안된다고 머리를 쥐어뜯을 필요도 없다..조보면 다 겪는 일이니까.
이런 질문이 오전에 있었다
저기요..화일에서는 분명히 콘트롤(ActiveX)을 선택하고 속성창을
보면 해당콘트롤의 속성이 나타나잖아요..
그런데 제가 하면 속성창에 Sheet에 대한 속성만 나타나는데요..
어케된 일이야요????
위에 질문에 답이 있었다..콘트롤을 [선택]하고
분명히 선택하고라고 하면서 선택이 습관이 안되어서 시트의 어떤범위가
선택이 된 상태이고 이것은 곧..워크시트가 선택된 상태이고
이 상태는 속성창에 선택된 오브젝트 워크시트의 속성이 나타나는 것이다
당연한것이다
마우스로 해당 콘트롤을 선택하는 순간 속성창에는
선택된 콘트롤의 속성이 나타난다..
그런데요..
콘트롤을 선택하려고 하는데 손가락 표시만 나타나고
당초에 그렸던 +자 표시는 안나타나는데요???!!
아하..선택이라는 개념은 이미 아시고 계신데..
선택을 하려니 선택이 안된다는 것이였구나???
이것도 초보님들이 겪는 [디자인모드]와 [실행모드]가 감이 잘 안잡혀서
그렇다..
그려서 사용하던 콘트롤을 다시 속성같은 것을 수정하고 위치를 바꾸려면
[디자인모드]와 [실행모드]를 토글링하는 버튼을 크릭해 주면 된다
이것이 모두 운전하는 습관과 같다
초보때는 등골에 긴장이 팍되어서 전후좌우가 잘 안뵈고 앞만 뵌다
좀 이력이 나면 그냥 옆사람과 잇빨까면서..전화질하면서
앞도 보고 뒤도 보고 옆도 보고 그냥 보려고 안해도 눈에 들어 온다
마찬가지다..
[선택]잊지 마시고..[디자인모드]와 [실행모드]를 잘 구분하시고..
열이나 행을 한번에 자리 바꾸기 | Move Columns At One Time!!
그림과 같이 파랑색 열두개를 첫째열 다음으로 옮기고 싶다
그냥 마우스로 삽입될곳에 빈열두개를 삽입한후 마우스로 끌어다 옮기고
옮겨진 빈열을 삭제하면 된다
시간이 좀 걸려서 그렇지 문제가 하나도 안된다
이야기 했다 시피 몰라도 되는 것이지만
마우스 한번에 처리하는 것을 알면 편리한 것이다
아래와 같이 Shift키를 누른 상태에서 마우스로 끌어서 옮기면
열과 열,행과 행 사이사이로 자유롭게 옮길수 있다
VBA로 옮기려면
Range.Insert 메소드를 사용한다
HowToUseExcelControls.
엔터키를 칠때 셀의 이동 | Move Next Cell as you want
엔터키를 칠때 셀의 이동은 도구/옵션/편집에서 Enter키의 이동방향을
설정해주면 되지만..
아래의 그림과 같은 방뱡으로 엔터키를 칠때마다 셀의 이동이 이루어진다면
편리할 것 같지 않을까?
알고 보면 컬럼브스의 달걀이다
그림과 같이 선택후 이름을 지어주고 입력할때 이름을 선택하면
그림과 같이 선택되겠지..
그리고 입력하고 엔터키치고 또 입력하고 엔터키치면 알아서 선택된 셀을
순환하게 되겠지!!
그러면 아래와 같이 테이블에서 입력을 할때는 어떻게 하면 좋을까?
절대주소와 상대주소를 이해하면 해결된다..
열방향 절대주소 행방향 상대주소로 테이블내의 하나의 행을 선택후 이름을 지어준다
그러면 행을 바꿔서 선택한후 이름을 선택하면 테이블내의 해당행이 모두 선택될 것이다
그런데 이것도 역시 반만 해결된다..다음행을 갈때는 한번 마우스질을 해야 한다
완벽히 자동화시키려면 어떻게 할까??
VBA를 사용하면 된다
아래와 같이..
HowToUseExcelControls.
잘되던 날짜의 그룹핑이 안되요 | Cannot group the selection
피봇테이블은 참으로 편리한 분석도구이다
특히 날짜를 월,분기,년도등으로 그룹핑을 해서 보기 좋게
분석을 해준다
그런데 아주 잘 되던 그룹핑이
와 같은 재수없는 메시지가 뜨고 더이상 그룹핑이 안된다
어쩐일인가???
기본적으로 날짜가 그룹핑이 안되는 경우는
1, 빈셀이 포함되어있거나
2, 날짜형식이 틀릿하거나
3, 문자형식의 정보가 들어있거나
등등의 엑셀이 날짜로서 인식이 안되니 못하겠다고 버티는 것이나
아니..쪼금 전까지 됐었다니까요..
모르면 모른다..그러지 그런 건 나도 안다니까...@R#@$누굴 바보로 아나..@#@!
그렇다면 아래와 같은 경우다..
[날짜]를 피봇에서 제거하시고 [날짜2]를 피봇테이블로 끌어서
사용하면 된다..
여러번사용하다보면 자기도 모르게 이런 저런 필드가 생기고
관리를 잘 하면서 사용하여야 한다
에러를 일부러 일으킨다 | Intentionally occur Error
가끔 에러를 일부러 일으키는 경우가 있다
어떤 목록에 중복되는 않는 유일한 값만 모으고 싶다고 할때
에러가 나더라도 그냥 진행하라!!!!라는 명령을 아래와 같이 작성하고
Dim rX As Range
Dim oX As New Collection
On Error Reume Next
For Each rX In SomeColumn.Cells
.........oX.Add rX, CStr(rX)
Next
라고 하면 oX집합체에 SomeColumn에 있는 정보를 중복되는 것 빼고
유일한 값만 모아주게 된다
집합체오브젝트의 Add메소드의 두번째 인수가 Key값이고 이것은
문자열로 전달하여야 하고..이것은 중복되면 안된다 라는 원칙에
위배되게 하여 모으는 것이다
만약 On Error Resume Next 를 하지 않는다면
중복되는 것을 집합체에 더하는 순간 에러가 나는 것이다
그러나 에러가 나도(On Error) 통과하고 다음 작업(Resume Next)!!!!
라고 해놓으면
중복되는 것은 에러가 나면서 더하지 않고 그냥 통과하게되는 효과를 보는 것이다
그래서 에러도 필요할때는 발생시키는 것이고
에러를 잘 다룰줄 알게되면 고수의 언덕이 보이게 된다
잘 되던 것이 안되요? | EnableEvents속성을 잘 살피실것!!
코드가 잘 실행되던것이 그냥 먹통이 되어 버렸다
특히 이벤트프로시져를 활용하던 것의 경우는 이것을 항상 유의하여야 한다
특히 남이 만든 코드를 살피기 위하여 디버깅을 걸어놓고 하다가
다시 하면 잘 안된다..
전혀 안된다
그럴때는 직접실행창에
Application.EnableEvents=True
를 실행하여 EnableEvents속성을 살려 놓고 하여야 할 것이다
지난주(612)와 다음주(613)회의 내용중 Event Procedure를 활용하는 것이 있는데
혹시나 코드를 실행하면서 한줄..한줄 살피다가..
어렵쇼...왜 안되지??!#라고 할 것같은 노파심에서 한줄 적었다