배워서 남주네) 엑셀과 vba를 이용해 공정관리 시트 구현하기 - 0
https://cdn.pgr21.com/?b=8&n=82011
*엑셀 파일은 윗 링크에 있습니다.
배워서 남주네) 엑셀과 vba를 이용해 공정관리 시트 구현하기 - 1
https://cdn.pgr21.com/?b=8&n=82067
안녕하세요. 시간당 생산성을 높여 생산량을 늘려보고 싶은 (전)학생산양입니다.
이번 편에서는
[2. 시작일/종료일/진도율에서 vba 요일/소요일/(현재일기준)남은일자/차트에 적용할 데이터 가공하기]에 대해 다뤄보겠습니다.
1. 그래서 전체 코드는 뭔데요
아래는 해당 기능을 구현하기 위한 모듈의 코드입니다.
best free photo hosting site
네.. 길죠. 그래서 이해를 돕기 위해 1) 순서도, 2)엑셀 내에서 어떻게 구현되는지를 간단히 표시해봤습니다.
1) startDate
D열에 입력한 일자를 기반으로 해당일의 요일을 출력합니다.
2) endDate
F열에 입력한 일자를 기반으로 해당일의 요일을 출력합니다.
3) durationandHowLast
F열의 종료일 - D열의 시작일을 계산해 소요일수를 구합니다
4) makeChartSource
공정관리 시트와 데이터를 연동하기 위해 시작일, 종료일, 진도율의 수치를 다시 계산하여 출력합니다.
5) monthLately
시작일이 입력된 가장 마지막 행을 찾아 해당 시작일이 있는 월로 이동합니다.
2. 각각 코드의 의미를 알려주세요
* 코드 리뷰시 1) 사진, 2) 복붙을 위한 전체코드, 3) 코드 한 줄씩 설명의 3단 구성으로 진행하겠습니다.
1) 가장먼저 dateUpdate 서브루틴입니다.
hdfc bank business hours
Public Sub dateUpdate()
ActiveSheet.Unprotect
' 시트보호를 걸어둔 상태에선 수정이 불가능하므로 잠시 해제 후 각 서브루틴을 불러옴
Call startDate
Call endDate
Call DurationandHowLast
Call makeChartSource
Call monthLately
' 시작일 요일 추가 / 종료일 요일 추가 / 소요일(종료일-시작일)
' 현재일 기준 남은 일자 / 진도율
' 현재 월 찾아서 활성화
Call highLightItem
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True
' 시트보호 활성화
ActiveWindow.Zoom = 90
' 엑셀 배율 조정
Call UpdateChart
End Sub
...
Public Sub dateUpdate()
=> 코드 덩어리(=서브루틴)를 쓰겠다고 선언할 때 Public Sub
[코드명*]()을 입력하면 됩니다.
이 경우 자동으로 아래에
[End Sub]가 써지니, 사용하고 싶은 코드를 Public Sub와 End Sub 사이에 입력하면 됩니다.
* 코드명의 경우 몇가지 규칙이 있습니다.
1) 반드시 알파벳으로 시작할 것
2) 특수문자는 _
[언더바] 외에는 사용하지 말 것
따라서, 다음과 같은 코드명은 사용할 수 없습니다.
ex) 2019codeStudy (X, 알파벳으로 시작해야 합니다)
2019-codeStudy(X, 특수문자는 언더바만 사용가능합니다.)
codeStudy_2019 (O) / Code_Study (O)
위의 두가지 규칙만 지키면 자유롭게 코드명을 지어도 되지만, 보통은 관례 혹은 편의를 위해서 크게 다음과 같은 세가지 방식으로 통일을 합니다.
1) 소문자 코드 중간에 첫 글자에 대문자를 삽입하기(camelCase) - ex) excelVba
2) 각 첫글자에 모두 대문자를 삽입하기(PascalCase) - ex) ExcelVba
3) 글자 사이에 _(언더바)를 삽입하기(snake_case) - ex) excel_vba
전문적으로 프로그래밍을 하는 입장에선 이런 건 협업을 위해 매우 중요하지만, 업무자동화의 본디 특성상(-_-) 이런 걸 협업할 가능성은 매우 낮기에 맘에 드는 걸 쓰시되, 통일성만 유지해주면 되리라 생각합니다.
저 같은 경우에는 1) camelCase를 선호하는데, vba에서 대문자를 집어넣어서 변수를 선언하면 오타없이 제대로 썼을 경우 소문자로 써도 자동으로 대문자로 변환해줘서 오타 여부를 체크하기 편하기 때문입니다. 하지만 이는 강제사항이 아니니 마음에 드는 방식 하나를 골라서 그 스타일만 유지해주면 되겠습니다.
ActiveSheet.Unprotect
=> 시트 보호를 비활성화하는 코드입니다. 이런걸 외워야하냐구요? 아뇨. 그렇게 하지 않아도 됩니다. 방법은 아래에서 시트 보호를 활성화할 때와 엮어 후술하겠습니다.
=> vba에서는 tab키를 눌러서 간격을 조정할 수 있습니다. 코드가 짧을 때는 티가 잘 나지 않으나, if문을 중첩해서 사용하거나 하는 식으로 논리구조의 구분이 필요할 경우 이를 활용해 들여쓰기를 하는 습관을 가지는 것이 좋습니다. 덧붙여 파이썬에서는 들여쓰기를 제대로 하지 않으면 오류가 나니, 추후 파이썬 학습때를 위해서라도 이 버릇을 익혀둔다면 더 좋겠지요.
' 시트보호를 걸어둔 상태에선 수정이 불가능하므로 잠시 해제 후 각 서브루틴을 불러옴
=> 각 줄의 맨 앞에 '를 쓰면, 컴퓨터는 그 뒤의 모든 텍스트를 코드로 인식하지 않습니다. 이를 주석처리를 한다고 하는데, 코드의 간단한 구조나 설명을 적어두는 용도로 사용합니다.
* 앞으로 코드 리뷰는 이 주석처리 방식을 이용해 코멘트를 하겠습니다.
Call startDate
Call endDate
Call DurationandHowLast
Call makeChartSource
Call monthLately
Call highLightItem
' 이미 만들어둔 다른 서브루틴을 불러올 때 call
[서브루틴명]을 쓰면 됩니다. 여기서는 총 6개의 서브루틴을 각각 호출해서 실행하는 코드가 되겠네요.
' 어느정도 코딩을 하다보면 여러개의 중첩된 기능을 사용하게 되는 경우가 있는데, 그럴 경우 가급적 위의 방식처럼 서브루틴을 쪼개서 호출해오는 식으로 진행하는 것이 좋습니다. 이렇게 하면, 1) 코딩 후 오류 발생시 어디서 버그가 발생하는지 파악하기 좋고, 2) 나중에 해당 부분만 따와서 다른 곳에 이용하기 좋고, 3) 코드의 기능을 변경할 때 재조립하기 좋습니다. 이 역시 습관을 들이길 추천합니다.
' * 서브루틴은 Public Sub 서브루틴명() ~ End Sub로 묶인 코드 덩어리를 의미합니다. 쉽게 말하면 코드뭉치 정도지요.
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True
' 시트보호를 활성화하는 코드입니다.
' 이걸 다 외워서 손으로 쳐야하냐구요? 아뇨. 외우지 않아도 됩니다. 우리에겐 매크로 기능이 있으니까요. 매크로를 기록하는 방법은
https://jaykim361.tistory.com/632?category=623102
를 참고해주시면 됩니다.
ActiveWindow.Zoom = 90
' 엑셀 프로그램에서 컨트롤 휠 위아래를 이용해 확대 비율을 조정하는 기능을 구현한 코드입니다. 이 역시도 외울 필요 없이, 매크로를 이용해 코드를 따낸 뒤에, 뒤에 숫자만 간단히 바꿔주면 되겠지요.
Call UpdateChart
' 차트에 데이터를 연동하는 코드를 호출하는 코드입니다. 지금은 다루지 않고, 나중에 차트를 건드릴 때 다시 끌어와 이야기를 해보겠습니다.
End Sub
' Public Sub로 시작한 코드를 끝내는 코드입니다. 보통은 Public Sub 서브루틴명()까지 치고 엔터를 누르면 End Sub도 자동으로 떠서 따로 작성할 일은 없을 겁니다.
2) startDate
Option Explicit
Dim lastRow As Long
Private Function findDate(Value As Range) As String
' 요일을 반환하는 내부함수 -> 시작일, 종료일, 차트 요일 세군데에 사용
findDate = Format(Value, "aaa")
End Function
Private Sub startDate()
' D열의 시작일 기반으로 E열에 요일 뿌려주기
Dim i As Long
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 7 To lastRow
If Cells(i, "D") = "" Then
MsgBox "시작일을 지정하세요"
Exit Sub
Else
Cells(i, "D").Offset(0, 1) = findDate(Cells(i, "D"))
End If
Next i
End Sub
...
Option Explicit
' 변수를 반드시 선언해야 코드를 사용할 수 있도록 하는 선언입니다. vba에 익숙하지 않다면 지금은 무시해도 됩니다.
Dim lastRow As Long
' 변수를 선언하는 코드입니다.
' Dim
[변수명] as
[데이터타입]으로 선언할 수 있습니다.
' as
[데이터타입]은 생략하고 바로 'Dim
[변수명]'만 쓸 수도 있습니다.
' 이 경우 vba는 해당 변수에 할당하는 메모리를 가장 큰 유형으로 잡습니다.(as Variant)
' 구체적으로 들어가자면 정말 많은 이야기를 할 수 있지만, 예상 독자가 초심자인 것을 고려해
[데이터타입]은 딱 세가지만 알고 가면 됩니다. As Date, As Long, As String
' As Date는 연/월/일/요일을 사용하고 싶을 때 쓰면 됩니다.
' As Long은 숫자를 처리하는 모든 경우에 쓰면 됩니다.(소수점도 처리하고 싶다면 as double도 가능)
' As String은 문자열을 처리하는 모든 경우에 쓰면 됩니다.
' 따라서, Dim lastRow As Long은 모르긴 몰라도 숫자를 처리하기 위해 선언한 변수임을 미루어 짐작할 수 있겠지요.
Private Function findDate(Value As Range) As String
' 요일을 반환하는 내부함수 -> 시작일, 종료일, 차트 요일 세군데에 사용
findDate = Format(Value, "aaa")
End Function
' 함수는 어렵다기보다는 서브루틴에 비해 좀... 덜 익숙할 거라 이해가 어렵다면 일단 넘어가는 것을 권합니다.
' Private : 위에서 Public Sub dateUpdate()를 보셨을 겁니다. Private은 Public의 반대되는 개념입니다. 매크로를 지정할 때 코드가 보이지 않는 것 외에는 Public과 동일합니다. 초심자때는 따로 Private을 쓰기보다는 Public으로 통일해서 써도 되겠습니다.
' Function : Public Sub dateUpdate()에서 코드뭉치를 만들기 위해 사용하는 Sub(서브루틴)과 달리 입력값을 받아 결과값을 뽑아내는 함수로 쓰겠다고 선언하는 코드입니다. 비유컨대, '나는 이 vba에서 이 function을 계산기로 쓸래' 정도의 의미로 받아들이면 됩니다.
' findDate : 함수 이름입니다
' (Value As Range) : 엑셀에서도 if문을 쓸때 =if(조건, 참일때 값, 거짓일때 값)으로 if문 안에서 ,(콤마)로 구분되는 세가지 변수가 있듯이, vba 내부에서 쓰기 위해 선언한 함수도 이와 같이 지정할 수 있습니다. 다시 말해, 함수이름 뒤 괄호 안에 있는 value는 함수에 입력할 값을 의미하며, 그 형식을 셀의 주소로 받겠다는 뜻입니다.
' As Range는 개체변수인지라 초심자들이 이해하기 조금 어려운 부분이 있어 깊게는 설명하지 않고, 간단히만 이야기해보겠습니다.
' 셀 A1에 "아 vba너무 어렵다ㅠㅠ"라는 내용이 있다고 가정했을 때, "아 vba너무 어렵다ㅠㅠ"라는 텍스트 자체를 가져오고 싶다면 value as string이라 써서 가져올 수 있습니다. 하지만, 셀 A1에 입력된 "텍스트가 아니라 셀 A1 그 자체"를 가져오고 싶다면 value as range로 특정 영역을 가져오겠다고 선언하여 사용할 수 있습니다. ... 이해가 안되더라도 그런가보다 하면 됩니다. 여기에 시간 쏟느니 다른 걸 먼저 익히는 게 좋습니다.
' As String : 해당 findDate라는 함수의 출력값을 문자열로 출력하겠다는 뜻입니다.
Private Sub startDate()
' D열의 시작일 기반으로 E열에 요일 뿌려주기
Dim i As Long
' i라는 변수를 숫자 형태로 선언합니다.
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
' Dim lastRow as Long이 원래는 Private Sub 안에 있어야하지만, 맨 위에 따로 선언을 해놓았기 때문에 바로 변수를 다룰 수 있습니다.
' 해당 코드는 마지막 행의 숫자를 찾는 변수입니다. 이해가 안되면 그냥 이대로 긁어서 쓰면 됩니다.
' Cells(숫자1, 숫자2) : vba가 숫자1행, 숫자2열의 셀을 찾습니다
' Cells(숫자1, "열 알파벳") : vba가 숫자1행, "알파벳"열의 셀을 찾습니다
' Rows.Count : 엑셀에서 가장 마지막 행의 번호를 가져옵니다.
' 따라서, Cells(Rows.Count, "B")는 B열의 가장 마지막 행의 번호를 가져오는 코드입니다. 엑셀로 치면 B1에서 Ctrl 아래 화살표로 가장 아래 내려온 셈이구요.
' .End(xlUp) : 데이터가 있는 행이 나올때까지 위로 이동. 엑셀로 치면 Ctrl 위 화살표로 올라가는 것과 같은 기능입니다.
' .Row : 해당 행의 번호를 가져옵니다.
' 동적 연계의 핵심입니다. 이 글에서 이거 제대로 쓸 수 있는 구조만 이해해도 vba 활용도가 엄청나게 높아집니다.
For i = 7 To lastRow
' for 반복구문입니다. for
[변수] =
[시작할 숫자] to
[마지막 숫자]
* for i = 7 to 10 (step 1)이라면 7부터 10까지 1의 간격으로(step 1) 숫자가 증가하며 반복합니다.
' 저는 기존 엑셀 파일에서 7행부터 데이터 입력이 가능하게 설계했으므로 7행부터 마지막행까지 반복하도록 선언합니다.
' 보다 자세한 내용은
https://jaykim361.tistory.com/651?category=623102 를 참고해주시기 바랍니다.
If Cells(i, "D") = "" Then
' 조건문 if입니다. D7셀부터 시작해서 해당 셀의 내용이 없다면을 조건으로 겁니다.
MsgBox "시작일을 지정하세요"
Exit Sub
' 메시지 박스를 출력하는 vba 자체명령어 Msgbox를 이용해 오류 메시지를 출력하고,
' Exit Sub를 이용해 현재 실행한 이 서브모듈을 빠져나옵니다.
Else
' 그렇지 않다면(=해당 셀에 내용이 있다면)
Cells(i, "D").Offset(0, 1) = findDate(Cells(i, "D"))
' D7셀부터 1열 떨어진
[.Offset(0, 1)] 셀의 값은(=E7셀의 값은)
' = : 오른쪽의 값을 왼쪽에 입력합니다.
' findDate(Cells(i, "D")) : 위에서 코드를 입력한 findDate 함수에 D7셀의 시작일을 넣어 얻은 해당일의 요일
' 따라서, 위 코드는 D7셀의 시작일에 해당하는 요일을 D7셀부터 1열 떨어진 E7셀에 넣어라는 뜻입니다.
End If
' 조건문 if를 끝낼 때는 반드시 end if를 넣어줘야 합니다. 안그러면 오류가 나요.
Next i
' 반복문 for가 끝날 때에는 반드시 next
[변수] 꼴을 써야합니다.
End Sub
-------------
다 뜯어보면서 하려니 분량이 너무 길어지네요. 오늘은 일단 여기까지만 자르겠습니다.