728x90

중간 중간의 셀이 빈 공백으로 되어 있는 경우 끝까지 번호를 매기려면 마우스로 Drag 하여 죽 끌고가거나 약간의 편법을 써야 하는 불편함이 있다.

이짓도 하다보니 좀 귀찮아서 간단하게 VBA 로

For Each Next 구문을 이용하여 만들었다.

rngC 는 반복하는 셀이고, rngC.Offset(0,2) 는 현재셀로부터 Offset(행,열) 즉, 행과 열만큼 이동하라는 의미다.

Offset(0,2) 는 행은 이동하지 말고, 열만 오른쪽으로 두번 이동하라는 의미이므로

rngC 셀이 A열에서 변동되므로 기록될 값은 C열에 기록된다.


Sub 번호매기기()
    Dim rngC As Range
    Dim i As Long
    For Each rngC In Range([A2], Cells(Rows.Count, "A").End(3))
        i = i + 1
        rngC.Offset(0, 2) = i
    Next rngC
End Sub


여기서 한가지 더 알아두면 좋은 사항은

rngC.Offset(0,2) 라는 것이 얼른 눈에 들어오지 않을 수도 있다.

이럴 경우에는 Cells(rngC.Row, "F") 와 같은 식으로 바꿔서 쓸수도 있다.

F열에 값을 기록한다는 것이므로 눈에 쉽게 들어온다.


하다보니 이것도 귀찮아서 더 편한 방법을 찾아서 적어둔다.

Sub 번호매기기()
    Dim rngC As Range
    Dim i As Long
    Dim sRow As Long
    Dim Col As String
   
    sRow =   '// 시작할 행
    Col = "A"   '// 지정할 열
    For Each rngC In Range(Cells(sRow, Col), Cells(Rows.Count, Col).End(3))
        i = i + 1
        Cells(rngC.Row, "O") = i
    Next rngC
End Sub

블로그 이미지

Link2Me

,
728x90

그동안 개발도구 탭 표시도 하지 않고 매크로 버튼은 [삽입] - [도형]을 이용해서 작업을 했는데

개발도구 탭을 이용하여 작업하고 싶어서 개발도구 탭을 화면에 표시하는 방법을 알아봤다.





매크로버튼을 만드는데 도형에 있는 그림으로 만들면 셀을 삭제하면 매크로 버튼이 같이 날라가는 경우가 생긴다.

하지만 개발도구 - [삽입] 에서 선택한 아이콘으로 만들면 행을 삭제해도 버튼은 삭제되지 않는다.







셀을 삭제해도 매크로 실행버튼은 그대로 유지되게 하는 방법이다.

숫자를 일부러 적어봤다.




행삭제를 했는데도 불구하고 버튼은 그대로 유지된다.


블로그 이미지

Link2Me

,
728x90

버그를 없애는 방법


1. 'Option Explicit'문을 사용
VB Editor 에서 '변수 선언 요구' 항목에 체크 표시를 해 두면 모듈 시트를 삽입할 때마다 맨 위에 'Option Explicit'이라는 문장이 자동 삽입됩니다.

모든 변수를 사용할 때 미리 선언(Dim)을 하고 사용해야 하므로 불편하다고 생각할 수 있지만 변수 이름과 관련된 입력 오류를 상당부분 줄일 수 있습니다.



자동 변수 선언하는 방법은

[도구] - [옵션] - [편집기] 에서 '변수 선언 요구'를 체크해주고 저장합니다.



모듈을 추가할 때마다 자동으로 아래와 같이 추가됩니다.




2. 주석(Comments)을 가급적 많이 사용
프로그래밍 세계에서는 '6개월만 지나면 자기 자신도 남'이라는 말이 있습니다.

코딩 시에는 조금 귀찮을 수 있지만 주석을 충실히 달아 놓으면 나중에 다시 살펴볼 일이 있을 때 큰 도움을 받을 수 있습니다.




3. 도움말과 전문가 사이트 활용
엑셀의 도움말은 문제가 발생하였을 때 가장 믿을만한 지원군 중 하나입니다.

뭔가를 하긴 해야겠는데 어떻게 해야할 지 생각나지 않는 경우,

전문가 사이트에서 검색어로 검색을 해서 원하는 참조하여 이용하고 주석처리를 잘 해두는 것도 방법입니다.

간단한 명령어가 생각나지 않는다면, 매크로 기록기를 통해 생성된 코드를 살펴보고 프로퍼티, 메서드를 도움말에서 찾아보는 것도 도움이 됩니다.



4. 들여쓰기(Indentation) 규칙을 적용
코딩 시 들여쓰기 규칙을 사용하면 가독성이 높아져서 전체적인 구조를 파악하기 쉬워집니다.

들여쓰기는 탭키를 이용하면 보기 좋게 정렬됩니다.



5. 엑셀의 디버깅 툴을 적극 사용
엑셀에는 여러 가지 종류의 디버깅 툴이 있습니다.

처음 사용할 때에는 귀찮고 복잡해 보일 수 있습니다만, 다소의 시간을 투자하여 이들 도구의 사용법을 잘 익혀 놓으면 실전에서 많은 도움이 됩니다.



직접 실행창 (Ctrl + G)를 누르면 하단에 직접 실행창이 보입니다.



이제 VBA Code 안에서 디버깅창(직접실행창)에 보일 내용을 Debug.Print 를 앞에 붙이고 실행을 합니다.


           
    FindText = InputBox("찾을 문자열 입력") '//찾을 문자열을 변수에 넣음
    If FindText = "" Then Exit Sub
   
    replace_Text = InputBox("[" & FindText & "] 을 바꿀 문자열을 입력하세요")
   
    Debug.Print "찾는문자열 : " & FindText
    Debug.Print "변경문자열 : " & replace_Text


이렇게 하면 디버깅창에 표시되지만 화면에서는 변경된 내역은 안보입니다.

Msgbox 를 이용하면 사용자가 일일이 '확인'이나 '취소'를 눌러줘야 하지만 Debug.Print 는 중단없이 다음 라인을 계속 실행합니다.

Debug.Print 변수이름  이렇게 하며 정상적으로 값을 뿌리는데

Debug.Print 배열  이렇게 하면 에러가 발생합니다. 이유를 알고 봤더니, ReDim Preserve 로 선언한 후에는 문제가 생기지 않았습니다.



직접 실행창에 뿌려지는 내용이 너무 많다면, Ctrl + A를 눌러서 전체 선택한 다음에 Delete 키로 지우면 전부 지워집니다.


코드를 한줄 한줄 실행하면서 제대로 로직이 맞게 된 것인지 확인하고 싶다면

F8키를 눌러서 한줄 한줄 실행을 합니다. 그러나, F8키만 누르는 경우에는 값이 어떻게 변경되는지 알기가 어렵습니다.

[보기] - [지역 창] 메뉴를 선택하면 '지역' 창이 표시됩니다. 이 상태에서 <F8> 키를 계속 눌러보면 각 변수에 값들이 어떻게 변하는지 알 수 있습니다.




위 그림을 보면 노란색 부분에 대한 식, 값, 형식이 하단 지역창에 보입니다.


전체가 아니라 중간점을 지정해두고 결과를 확인하고 싶다면



을 하고 F5키를 눌러서 실행하거나


를 눌러줍니다.

중단점 설정을 키보드로 하는 경우에는 F9 키를 누르면 됩니다.

중단점은 여러 곳에 설정할 수 있으며 설정을 해제하려면 중단점을 클릭하면 됩니다. 설정된 중단점들을 한꺼번에 모두 제거하려면 <Ctrl+Shift+F9> 키를 사용합니다.



코딩을 하다가 버그가 생겼을 때 빠르게 조치하기 위해서는 Debugging 하는 방법을 알아야 합니다.

그냥 단순한 방법으로 Msgbox 에 내용을 출력하도록 하여 결과를 볼 수도 있습니다만

디버깅 하는 방법을 배워두면 훨씬 편하고 좋습니다.


블로그 이미지

Link2Me

,
728x90

VBA 편집기 사용법


VBA 편집기로 VBA 코드를 입력하거나 다른 사람의 코드를 붙여넣기 해 볼때 알아두면 유용한 것을 적어봅니다.


엑셀에서 Alt + F11 키를 누르면 VBA 편집창이 열린다.

1번은 프로젝트 탐색기 창으로,
VBA코드를 안보이게 설정하면 Microsoft Excel 개체, 모듈, 폼 등이 어떻게 구성된 것인지 보이지 않는다.

엑셀의 시트명이 어떻게 보이는지 보면 Sheet1(Sheet1), Sheet2(VBA), Sheet3(DATA) 로 보이는 걸 알 수 있는데 사용자가 직접 입력한 Sheet Name 은 괄호안에 표기되는 걸 알 수 있다.

VBA 에서 엑셀 시트를 보이게 하거나 보이지 않게 할 수도 있고, 완전히 감출 수도 있는데 이때 프로젝트탐색기 창에 보이는 이름을 알면 편하다.



2. 속성창은 프로젝트탐색기 창에서 선택한 개체의 주요 속성을 표시한다.

3. 코드창은 코드를 직접 입력하거나, 다른 사람의 코드를 복사해서 붙여넣기를 하면서 VBA 코드를 짜는 창이다.

    VBA 코드창의 글씨가 10으로 되어 있어 보기가 좀 불편하다면 ....

     


         변수선언요구
       VBA 코드를 짤 때 변수를 제대로 선언하지 않고 짜도 실행되기도 하는데 오류가 발생하지 않도록 하려면

       변수를 선언하고 사용하도록 요구하는 이 옵션을 체크하고 사용하는 것이 좋다.

       

       

       엑셀을 종료했다가 다시 실행하면 모든 코드 창의 맨위에 Option Explicit 문이 나타난다.


4. 직접 실행창은
    VBA 코드의 결과를 확인하는 창으로 디버깅할 때 유용하다.
    


Sheet1 의 이름을 직접 실행창에서 직접 변경해 본 것이다. 보통은 엑셀시트에서 직접 이름을 변경하지만, 어떻게 변경되는지 확인해보고자 입력을 해봤다.


VBA 코드를 짜면서 결과를 확인해보고 싶을 때 직접 실행창에 ? 실행할 명령어 를 입력한다.

? Range("A2").Value 라고 입력하고 엔터키를 치면 A2 셀의 값을 직접 실행창에 결과로 보여준다.

? 는 Print 의 단축명령어다. 직접실행창에 입력한 글자를 전부 지우려면 Ctrl + A 를 눌러서 전체를 선택한 다음에 Delete 키를 눌러준다. 한줄만 삭제하려면 Ctrl +Y 를 누르면 커서가 있는 해당 Line만 삭제된다.










블로그 이미지

Link2Me

,
728x90

[VBA기초] DO LOOP 구문



오늘 알아볼 엑셀 기본구문은 DO LOOP 문입니다.

DO LOOP 문은 처리할 횟수가 지정되어 있지 않으므로 조건을 잘못 설정하면 무한 반복처리될 수 있습니다.

무한 루프에 빠지지 않도록 주의해야 하며, 만약 무한 루프에 빠지면 CTRL + BREAK 키로 중단시킬 수 있습니다.






그럼 예제 하나를 살펴볼까요?



변수 i 는 1부터 시작하여 최대 10이 되기전에 끝나겠군요.

그런데 i 가 7이면 Do Loop 문을 빠져나가라고 되어 있으니 i 는 9까지는 가보지도 못하고 끝나겠는데요.


지금까지 나온 기본구문 For Next 구문, For Each Next 구문, Do Loop 구문 등을 상황에 맞게 적절하게 사용하면서 조건 IF문을 잘 활용하면 원하는 결과를 얻는데 쉽게 얻을 수 있을 겁니다.


기본적인 엑셀함수 기능에 대해서 좀 더 알면 자유자재로 활용할 날이 올거라 믿으며 오늘은 이만 ^^









블로그 이미지

Link2Me

,
728x90

For Each ~ Next 구문



For Each ~ Next 구문은 For ~ Next 구문과 거의 동일한데 약간 다른 점은 ...

For ~ Next 구문은 전체중에서 원하는 것만큼만 실행이 가능한데

For Each ~ Next 구문은 무조건 전체를 실행하며, 거꾸로 순서를 지정해서 할 수가 없습니다.



이거 무슨 뜬구름 잡는건지 감이 잘 안잡히시죠?



Selection 한 구간 전체는 배열이구요. 각각의 Cell은 배열의 요소 입니다.

Sub Selection_VBA()

Dim rngC As Range

For Each rngC in Selection

if rngC.Value >0 Then 실행문

Next rngC

End Sub


셀의 범위 구간을 지정하여 구간내에서 실행문을 실행하여 원하는 결과를 얻을 수 있습니다.

위의 반복문은 셀의 총 갯수가 11개 이므로 11번 반복해서 돌아가고 IF문 조건을 만족하는 결과를 산출합니다.

Range는 구간을 직접 지정할 수도 있고 Selection 으로 구간을 그때 그때 정할 수도 있습니다.


보통은 구간범위를 지정하여 반복수행하여 결과를 얻어냅니다.

Sub test()
    Dim rngC, rngAll As Range
   
    Application.ScreenUpdating = False
    Set rngAll = Range([A2], Cells(Rows.Count, "A").End(3))
    For Each rngC In rngAll
        '// 처리하고자 하는 내용을 IF문과 함께 처리
    Next rngC
    Set rngAll = Nothing    '// 메모리 비우기
    Application.ScreenUpdating = True
    MsgBox "처리완료"
End Sub


요소 rngC를 지정하는 것은 rngC 라고 하고, 어떤 사람은 C 라고 하고, rng 라고 하기도 하는 등 사람의 취향에 따라 다릅니다.


위 그림처럼 Worksheets 를 대상으로 처리하는 실행문을 구현하여 원하는 결과를 얻을 수도 있습니다.


Sub ShowName()
    Dim sht As Worksheet
    For Each sht In Worksheets
        MsgBox sht.Name, , "워크시트 이름"
    Next ws
End Sub



대략 어떻게 돌아가는 구나 정도 이해가 되었다면 예제 등을 통해서 자주 접하는 것이 원하는 걸 얻을 수 있는 지름길 입니다.

VBA 입문 과정에서는 고수들이 만든 VBA 코드를 복사하여 붙여넣기 한 다음에

F8키를 눌러가면서 의미를 파악하면서 하나 하나 내것으로 만들어가는 겁니다.




블로그 이미지

Link2Me

,
728x90

[VBA 기초] FOR NEXT 구문



IF문과 함께 가장 많이 사용하는게 FOR NEXT 구문입니다.

단순 반복해야 하는 상황을 아주 간단하게 해결해주는 게 바로 FOR NEXT 구문입니다.




FOR NEXT 구문은 위 2가지 입니다. STEP 증가값을 생략하면 1씩 증가됩니다.

시작값부터 종료값까지 증가되고 나면 FOR 제어문을 빠져나오게 됩니다.

1000개의 셀에다가 값을 기록해야 하는 경우라면

변수는 통상적으로 I 를 많이 사용하니까


Sub for_next_basic()

Dim r As Integer

For r = 1 to 1000

Cells(r, 1).Value = 3 * r

Next r

Msgbox "처리완료"

End Sub


이런식으로 원하는 셀을 고려하고 값을 계산하도록 만들거나 원하는 셀에다가 반복적으로 값을 기록하게 합니다.

셀을 중앙정렬하고 싶은데 중앙정렬 시키는 값을 잘 모르겠다??

저같은 경우에는 엑셀 자동매크로를 실행해보고 중앙정렬 하도록 하는 값이 뭔지 찾아낸 다음에 그걸 찾아서 적용합니다.

F1 도움말 키를 누른다음에 '중앙정렬 VBA' 라고 입력을 해보는 것도 방법입니다.



블로그 이미지

Link2Me

,
728x90

기본 함수 IF문


VBA 에서 다루는 기본적인 개념을 이해하고 나서 간단한 함수 다루는 개념을 배우면 됩니다.

엑셀에는 기본적으로 비주얼베이직 6.0이 들어있다고 합니다.

그래서 간단하게 생각하는 로직을 구현하면 엑셀과 연동하여 원하는 결과를 얻을 수 있게 됩니다.





VBA 를 다루기 위한 VB편집기 창을 띄우는 방법이었구요.


엑셀함수에서 다루는 IF문을 VBA에서도 다루고, 반복처리를 위한 FOR 문, DO WHILE 문에 대한 기본적인 개념을 알고 처리를 합니다. 엑셀함수에서는 직접 셀에 대한 걸 비교하여 원하는 결과를 얻는데 VBA 에서는 변수선언과 더불어 약간 더 로직 다루는 부분이 더 들어간다고 이해하시면 될 거 같습니다. 저도 아직은 초보라서 아는게 부족합니다.

초보자 입장에서 최대한 쉽게 설명하고픈 마음만 있는 셈이죠 ^^


아래는 조건식을 비교할 경우에 사용하는 비교 연산자와 논리 연산자의 의미입니다.



엑셀 함수에서 가장 많이 다루는 IF 조건문이 아래 그림처럼 되어 있죠.

VBA 에서는 IF 조건식 THEN 형식으로 되어 있습니다.






간단한 예제를 하나 살펴보겠습니다





VBA 에서 가장 기본적으로 다루는 IF 문(조건문)에 대해서 알아봤습니다.

이 IF문과 셀을 다루는 개념이 결합되어 원하는 결과를 얻어내는 겁니다.

반복적으로 처리해야 할 것은 FOR문이나 DO WHILE 문과 결합되어 원하는 결과를 도출하구요.

다음에는 FOR 문에 대해서 알아보겠습니다. ^^

블로그 이미지

Link2Me

,
728x90

[VBA 기초] Selection 의 개념잡기

 

 

엑셀은 셀을 다루는 것이므로 셀에 대한 범위 지정 등을 잘 알아야 합니다.

 

 

 

마우스로 선택한 구간에서 구하고자 하는 값을 알기 위한 사항입니다.

 

Sub CountRows()
    Dim rngCnt As Integer
    Dim sRow As Integer
    Dim eRow As Integer
       
    Dim rngEnd As Range
   
    rngCnt = Selection.Rows.Count  '// 선택된 영역의 총 Row 수
    sRow = Selection.Row  '// 선택된 영역의 첫번째 행의 위치 반환
    eRow = Selection.Row + Selection.Rows.Count - 1  '// 선택된 영역의 마지막 행 위치 반환
   
    MsgBox "첫번째 행은 " & sRow
    MsgBox "마지막 행은 " & eRow
    MsgBox "전체 선택한 행의 수는 " & rngCnt
   
End Sub

 

그러면 이제 아래 내용이 무슨 의미를 알아봅시다

Dim h As Long, r As Long
r = Cells(Rows.Count, "B").End(xlUp).Row

값이 들어 있는 B열의 마지막셀을 찾아서 Row(행)의 값을 r 에 넣어라

'// Cells(Rows.Count, "B") 는 B열의 마지막 셀, 엑셀이 제공하는 마지막셀을 의미

'// Cells(Rows.Count, "B").End(xlUp) 은 Cells(Rows.Count, "B").End(3) 으로도 표기하며

'// B열의 마지막셀로부터 위(xlUp) 으로 올라와서 마지막 데이터가 있는 셀을 의미

 

 

n = Cells(Rows.Count, "B").End(xlUp).Column

값이 들어 있는 B열의 마지막셀을 찾아서 Column(열)의 값을 n 에 넣어라

 

Range("A10:B" & r) 의 의미는

A10 셀부터 B열의 마지막 값이 들어있는 셀까지의 범위

Range("A10:B" & Cells(Rows.Count,"B").End(3).Row)

또는 Range([A10], Cells(Rows.Count, "B").End(3)) 으로도 많이 사용합니다.

 

 

 

Selection.Borders().LineStyle=xlNone  '//선택된 셀의 안쪽선을 없앰
Selection.Borders(xlEdgeBottom).LineStyle=xlNone  '//선택된 셀의 아래쪽 테두리선을 없앰
Selection.Borders(xlEdgeHorizontal).LineStyle=xlNone  '//선택된 셀의 안쪽 가로테두리선을 없앰
Selection.Borders(xlEdgeLeft).LineStyle=xlNone  '//선택된 셀의 왼쪽 테두리선을 없앰
Selection.Borders(xlEdgeVertical).LineStyle=xlNone  '//선택된 셀의 안쪽세로테두리선을 없앰
Selection.Clear  '//선택된 영역의 내용과 서식을 모두 지움
Selection.ClearContents  '//선택된 영역의 내용만 지움
Selection.ClearFormats  '//선택된 영역의 서식만 지움
Selection.Columns  '//선택된 열
Selection.Copy  '//선택된 영역을 복사
Selection.EntireRow.Delete '//선택된 행을 삭제
Selection.Merge  '//선택된영역을 병합
Selection.NumberFormat="0.00"  '//선택된 셀을 소수점 둘째자리까지 표시
Selection.Rows  '//선택된행
Selection.UnMerge  '//선택된 영역을 병합해제

 

현재 커서가 있는 셀은 ActiveCell 입니다.

현재 커서가 있는 행 전체를 선택하는 것은 ActiveCell.EntireRow.Select 입니다.

앞에서부터 순차적으로 ActivCell 을 적어주고 전체 행인 EntireRow 를 적고 마지막으로 선택(Select)를 하면 됩니다.

 

어떤 의미인지 조금이나마 감이 잡히시나요?

 

댓글과 공감은 큰 힘이 됩니다

블로그 이미지

Link2Me

,
728x90

[VBA 기초] Range, Cell 의 개념 알아보자



제가 멋모르고 공개해준 VBA를 가져다가 응용하려고 할 때 가장 막힌 부분이 셀에 대한 개념이 부족해서였습니다

물론 좋은 강좌도 많지만 개념 이해하는데 턱턱 막히는 원인을 조금이라도 해소해보고자 부족한 실력이지만 적어봅니다.

먼저 참고할만한 게시물 참조 걸게요 권현욱 Range강좌

엑셀은 셀을 다루는 프로그램입니다.



현재 커서가 위치한 셀을 ActiveCell 이라고 합니다. 이 셀이 위치한 곳을 표현하는 방법에 대해 먼저 알아보겠습니다.

Cells(행,열) 로 표현하는 데

위의 ActiveCell 의 위치를 보면 A열이고 행으로 보면 2번째 행이죠?

그럼 Cells(2,"A") 라고 표현할 수 있습니다. 또 다르게 Cells(2,1) 이라고 표현할 수도 있습니다.

Range("A2") 라고도 표현하기도 합니다.

왜 이런 시시콜콜한 걸 얘기하나면 이런 시시콜콜한 표현방법을 모르면 응용할 때 막히더라구요.

A2 셀에다가 값을 100을 집어넣어라 라는 걸 VBA로 구현하라 라고 한다면....

엑셀 띄워진 상태에서 Alt + F11 키 누르고 Visual Basic for Application 창에서 삽입 -> 모듈 눌러주세요

Sub Value_Add()
    Range("A2").Value = 100
End Sub
를 적어주고 나서 F5키를 눌러보세요 ^^



그러면 위 그림처럼 값이 기록되는 걸 볼 수 있어요.

이번에는 셀 하나만 선택하는게 아니라 여러 셀을 선택해 봅시다.

Range("A2","C5").Value = 100 이라고 수정해서 넣어보세요


그림을 보니까 A2 에서부터 C5 영역까지 모두 100 으로 채워졌군요.

이번에는 Range("A2","C5").Value = 100 대신에 Range(Cells(2,"A"), Cells(5,"C")).Value = 200 이라고 바꿔보세요.

Sub Value_Add()
    'Range("A2", "C5").Value = 100
    Range(Cells(2, "A"), Cells(5, "C")).Value = 200
End Sub




이번에는 아래 그림을 보면 값이 좀 더 많이 들어 있죠?



현재 커서가 위치한 셀이 A2 이군요. 이번에는 Range("A2").CurrentRegion.Select 라고 입력합니다.

Sub Value_Add()
    Range("A2").CurrentRegion.Select
End Sub



A2와 인접해 있는 주변셀들이 모두 선택되었군요.

여기서 D6 셀에다가 값을 400 이라고 적어보고 나서 다시 F5로 실행을 해보겠습니다.



이번에는 인접영역이 훨씬 더 넓게 선택되었군요.


아래 그림을 보면 현재 커서가 있는 셀이 A2 이죠?

육안으로 보면 마지막 행과 마지막 열이 어디인지 알 수 있습니다.

VBA 다루는 건 이렇게 육안으로 보이는 거 처리하려고 하는게 아니라 행(Row)이 5000 행이고 열(Column)이 10 정도 되는 것일 수도 있습니다. 이런 영역안에서 값을 찾아서 처리하려고 한다면 행 하단까지 전부 찾아서 기록하고, 열도 찾아서 기록하고 이런 짓을 하면 정말 힘들겠죠... 선택영역 범위를 자동으로 선택(Select)하는 방법은 없을까요?



지금까지 죽 다룬 Range 와 Cells 의 개념을 잘 조합하면 됩니다.

Range(Cells(~,~),Cells(~,~)) 라는 거 앞에서 언급한거 보셨지요?

Range 안에 앞에는 Start 지점, 뒤에는 End 지점 이라는 거 감이 잡히신다면...

Cells(~,~) 의 Cells(행,열) 을 어떻게 구해서 어떻게 표현해줄 것인가를 알면 된다 이겁니다.

Range(ActiveCell, Cells(~,~)) 라고 적을 수 있습니다. 아니면 직접 Range("A2", Cells(~,~)) 라고 해도 되구요.

그럼 여기서 Cells(행,열) 에 들어갈 마지막 행을 표기하는 방법, 마지막 열을 표기하는 방법을 알면 됩니다

Sub Value_Add()
    Range(ActiveCell, Cells(Rows.Count, "G").End(3)).Select
End Sub


G열은 육안으로 보이니까 직접 적어주는게 쉽고 편합니다.

Cells(Rows.Count, "G").End(3) 의 의미가 뭔지 알아야겠죠?

제가 열심히 설명하는 것보다는 http://rosa0189.blog.me/60145634878 게시글을 보는게 더 이해가 잘 되실 겁니다.

Cells(Rows.Count, "A") 의 의미는 A열의 마지막 행의 위치입니다.

엑셀이 제공하는 마지막 행은 엑셀 2003은 65,536 행(Row)이 있고, 2007 버전 이후로는 1,048,576 개의 행(Row) 입니다.

엑셀이 제공하는 최대 행까지 내려가라. 그런 다음에 End(3)의 의미는 End(xlUp) 으로 위로 이동하여 값이 있는 마지막 행의 위치를 의미합니다.


Range(Range("A2").End(xlDown), Range("A2")).Select 을 실행해보세요

Range(Range("A2"), Range("A2").End(xlDown)).Select 범위 지정을 이렇게 해도 결과는 동일합니다.

보통은 Range([A2], Cells(Rows.Count, "A").End(3)).Select 라는 걸 많이 사용합니다.

Range([A2], [A2].End(4)).Select 도 표현도 가능합니다.



결과는 A11 까지 선택되지 않고 중간에 공백이 있는 곳에 멈춥니다. 그러므로 범위를 어떻게 지정하느냐에 따라 결과가 달라질 수 있다는 점 알아두셔야 합니다.

여기서 중요한 거 한가지 짚고 넘어가겠습니다.

엑셀은 마침표(.)를 기준으로 앞에서 부터 뒤로 의미를 부여하여 실행된다는 겁니다.

Range("A2").CurrentRegion.Select

이 의미는 먼저 Range("A2")를 지정하고 그 다음에 CurrentRegion 을 하고 그 다음에 Select 하라는 겁니다.


Range("A2:G11").Cells(3,2).Value = 7777

이라고 한다면....


그림에서 먼저 Range("A2:G11")을 선택하고 나서 이 영역내에서 Cells(3,2) 라는 의미는 3행, 2열을 선택하는 겁니다.

아래로 3행, 우측으로 2열 그리고 나서 Value 를 7777 을 기록하라는 겁니다. 이해되시죠?


이번에는 여기에 글씨도 굵게 표시하고 폰트 크기도 14로 표기하라고 하고 싶어서 아래처럼 했습니다.

Sub Value_Add()
    Range("A2:G11").Cells(3, 2).Value = 7777
    Range("A2:G11").Cells(3, 2).Font.Bold = True
    Range("A2:G11").Cells(3, 2).Font.Size = 14
End Sub



결과는 잘 나왔습니다.

그런데 보니까 Range("A2:G11")을 반복하면서 쓰고 있네요.

좀 깔끔하게 정리하면 좋겠는데 말이죠..

Sub Value_Add()
    With Range("A2:G11")
        .Cells(3, 2).Value = 7777
        .Cells(3, 2).Font.Bold = True
        .Cells(3, 2).Font.Size = 14
    End With
End Sub


이렇게 With 에 Range("A2:G11") 을 선언하고 처리해줘도 결과는 동일하게 나옵니다.

좀 더 정리를 하면


Sub Value_Add()
    With Range("A2:G11")
.Cells(3, 2)
        .Value = 7777
        .Font.Bold = True
        .Font.Size = 14
    End With
End Sub


여기서 왜 콤마(.)가 들어간 건지는 이제 이해되시나요?


부족한 실력에 제가 Cell 개념 설명을 한다고 했는데 도움이 좀 되셨는지 모르겠군요. 제가 몸소 겪은 애로사항을 정리한다고 정리한 거라 조금은 도움이 되셨을 거라 믿습니다.

  

메서드 설 명

Clear

내용과 서식을 모두 지웁니다.

ClearContents

내용만 지웁니다.

ClearFormats

서식만 지웁니다.


이런 메서드와 연결되어 어떻게 처리되는지 감이 잡히신다면 실력향상은 금방 되실 겁니다.


아래를 실행하면 어떤 결과가 나올까요?  각자 알아서 실행해보시기 바랍니다.

Sub Value_Add()
    Range("A2").CurrentRegion.ClearContents
End Sub


If Left(Cells(i, "a"), 2) = Left(Cells(ActiveCell.Row, "a"), 2) Then '// 셀의 앞 두자가 일치하면

셀을 비교하여 원하는 결과를 얻고자 할 때 앞 두셀만 비교해야 할 경우가 생기죠.


셀의 마지막열의 값을 구하고자 하는 경우에는

lastCol = Cells(1, Columns.Count).End(1).Column

으로 하면 숫자로 표기됩니다.

마지막 열은 요긴하게 사용될 수 있습니다.


Range("C4:M" & Cells(Rows.Count, 3).End(xlUp).Row)


Set rngDB = Worksheets("B").Range("B2", Worksheets("B").Cells(Rows.Count, "B").End(3))
Set rngAll = Worksheets("A").Range("A2", Worksheets("A").Cells(Rows.Count, "A").End(3))


도움이 되었다면 댓글이나 공감 꾸욱 부탁드립니다.

블로그 이미지

Link2Me

,
728x90

Worksheet Object 의 이해


자료출처 : http://blog.daum.net/kdy88/8403


우선 워크시트 오브젝트의 중요한 구성원(속성, 메서드 등)들을 살펴보겠습니다.

 


워크시트 오브젝트는 쉽게 말해 워크시트 그 자체를 의미하므로 워크시트의 이름을 바꾸거나 셀에 어떤 값을 입력하고자 할 때 위의 메서드 또는 속성을 이용하면 됩니다.

위의 내용은 출처를 밝힌 블로그에서 가져왔구요. 아래 내용은 간단하게 제가 별도로 설명을 하겠습니다.



현재 엑셀 작업을 하는 Sheet 명을 가져온다든가, 다른 Sheet 명을 가져오거나 다른 시트명에 기록을 해야 하는 등의 여러가지로 Sheet 를 다루는 일이 있을 겁니다.

이런 경우를 자유자재로 사용하기 위해서 알아야 할 Object(객체)라고 이해하시면 됩니다.


Sub ActiveSheetName()

Dim ActiveSht As String

ActiveSht = ActiveSheet.Name   '//활성화된 Sheet 이름

Msgbox "현재 작업하는 시트는 " & ActiveSht &" 입니다"

End Sub


실행 결과



워크시트를 추가


워크시트를 입력하고 점(.)을 찍으면 자동으로 뭘 선택할 것인지 나옵니다.

Sub Sht_Add()
    Worksheets.Add

End Sub
를 해주면 결과가



그림 보니까 Sheet1 앞에 추가되었군요.

그렇다면 Sheet1 다음에 추가를 하고 싶다면 ???
Sub Sht_Add()
    Worksheets.Add After:=Worksheets(1), Count:=2
End Sub

Sheet5 와 Sheet6 이 생성된 것은 Sheet4를 생성했다가 지웠는데도 이렇게 생성되었네요.

하지만 Sheet4를 생성한 적이 없다면 Sheet4 와 Sheet5 가 생성되었을 겁니다.


Sheet를 이동하고 싶을 때

Sub Sht_Move()
    Worksheets("Sheet6").Move After:=Worksheets("Sheet3")
End Sub

Sheet6 를 Sheet3 다음으로 이동하라는 건데 이동된거 그림에 보이죠?


다른 시트를 선택하고 싶은 경우

Sub Sht_Select()
    Worksheets("Sheet2").Select
End Sub

Sheet 명을 직접 적어주는게 헷갈리지 않고 좋습니다.

위에 나온 표를 보면서 그림들을 비교하면서 살펴보면 이해하는데 빠릅니다.


삭제에 대한 건 굳이 얘기 안해도 아시겠죠?

Sub DeleteSheet()

Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True

End Sub


블로그 이미지

Link2Me

,
728x90

엑셀 VBA 가져다 활용하는 방법 소개



엑셀 함수를 사용하여 처리하다보면 Line(행)이 작은 건 편하고 좋은데 처리할 행이 1만 행 정도 된다거나 그 이상 되는 건 처리하는데 정말 시간이 너무 많이 걸립니다.

그리고 함수를 적용하면 속도가 장난 아니게 느려집니다.

이럴 때 VBA(Visual Basic for Application) 를 잘 알아두면 업무가 엄청 편하고 좋습니다.


VBA는 거의 프로그램이나 마찬가지라서 프로그램에 대한 기본적인 이해가 동반되어야 합니다.

그러나 다른 사람들이 만들어 둔 걸 활용하거나, 그냥 가져다가 사용하는 방법만 알아도 업무에 도움이 많이 되실 겁니다...


먼저 주의사항 부터 말씀드리겠습니다.

엑셀은 실수를 하면 되돌리기 기능이 있어서 되돌리면 됩니다.

그러나 VBA는 되돌리기 기능을 제공하지 않습니다. 반드시 백업을 해두고 나서 작업을 하는게 좋습니다.


엑셀이 띄워진 상태에서 ALT + F11 키를 눌러주세요.

그러면 아래 그림처럼 나옵니다.






VBA 를 직접 작성하거나 다른 사람이 만든 VBA 코드를 복사하여 붙이기를 하고 나면

ALT + F11 키를 눌러주면 창이 닫혔다 열렸다 할 겁니다.

붙여넣기가 되었으면 sub 연습() End Sub 사이에 커서를 놓고 F5 키를 누르면 실행됩니다.

엑셀시트는 반드시 실행시킬 시트에 두고 하세요..


VBA 를 얼마나 잘 다루르냐는 배우는 정도에 따라 다르겠지만 간단한 것 정도 배우는 것도 정말 도움이 많이 됩니다.

VBA 어렵다고 생각하지 마시고 용기를 내서 배우는게 업무를 하면서 밤을 세워서 할 것인가 그냥 한두시간만에 뚝딱 끝내고 편하게 업무를 할 것인가가 결정됩니다.


블로그 이미지

Link2Me

,
728x90

[VBA 입문] 매크로 실행버튼 만들기



엑셀 함수만 이용하다가 VBA를 배우면서 알게 된 걸 짧은 지식이지만

저도 참조하고 저보다 더 초보자인 분들이 이해하기 쉽게 조금씩 포스팅을 하고자 합니다.


엑셀 VBA 함수 지정되어 있는지 여부는 Alt + F11 키를 눌러보면 알 수 있다.

매크로가 지정된 상태로 저장된 것은 확장자가 xlsm 으로 되어 있다.

엑셀 버전이 낮은 경우에는 xls 로 되어 있기도 한다.



이런 버튼을 만드는 방법은 아래 그림 순서대로 따라하면 만들 수 있다





이렇게 하시고 나면 아래 그림처럼 아이콘이 하나 더 추가된다.

매크로 버튼을 만들 일이 있을 때 이 아이콘을 눌러서 실행하면 된다.

모양이 별로 이쁘지 않으므로 보통은 [삽입] 에서 도형을 선택해서 만든다.




단추1 이라고 버튼이 하나 만들어졌네다. 눌러보면 에러만 나온다.

왜냐하면 매크로와 연결이 안되어서다.



매크로 버튼 만들어서 사용하면 여러모로 편리하다.

단축키 지정하는 것도 해봤지만 눈에 보이게 버튼 만들어서 사용하는게 훨씬더 편하다.

처음에는 VBA 정말 어려워서 엄두도 나지 않을 수 있다.


기본적인 이해가 매우 중요하다. 그러나 기본적인 이해라는게 쉬운 것은 아니므로 고수들이 만들어둔 VBA 에서 필요한 부분만 수정해서 이용하면서 배운다.


이런 버튼 만들지 않고 그냥



에서 추가해도 된다.

도형을 추가한 다음에 마우스 우클릭 누르면


매크로 지정을 눌러서 매크로 함수랑 연결해주면 다음부터는 버튼만 클릭하면 된다.

버튼을 만들지 않을 경우에는 F5 키를 누르면 실행된다.





블로그 이미지

Link2Me

,