1. 엑셀 VBA 관련 Function(함수)에 대한 도움말
아래 URL에서 확인할 수 있습니다.
| https://docs.microsoft.com/en-us/office/vba/language/reference/functions-visual-basic-for-applications |
2. 도움말 화면
Functions 아래에 Date, DateAdd, DateDiff 등이 나열되어 있는 것을 볼 수 있습니다.
3. 엑셀과 VBA의 날짜 관련 함수 비교
함수명이 같은 것도 있고, 비슷한 것도 있고, 다른 것도 있는데, VBA의 함수(Function)가 더 많고 복잡함을 알 수 있습니다.
| 엑셀 | VBA | 비고 |
| today() | Date | 오늘. 괄호 표시 안함 |
| now() | Now | 오늘 날짜 및 시각. 괄호 표시 안하는 것이 원칙인데, 해도 에러는 안남 |
| 없음 | Time | 현재 시각 |
| date(year,month,day) | DateSerial(year,month,day) | 함수명만 다름 |
| edate(날짜, 월수) | DateAdd(interval, number, date) | 월뿐만 아니라 q, d, ww 등 Interval 유형이 다양하며, 순서가 다름 |
| datedif(시작일,종료일,단위) | DateDiff(interval, date1, date2, [ firstdayofweek, [ firstweekofyear ]] ) | 계산 단위로 y, m, d뿐만 아니라 q, ww 등 Interval 유형이 다양하며, 순서가 다름 |
| 없음 | DatePart(interval, date, [ firstdayofweek, [ firstweekofyear ]]) | 날짜가 속한 년,월,일 등을 반환. interval이 y,m,d 등으로 다양 |
| 없음 | DateValue(문자 형식 날짜) | 예) datevalue(#2019-09-10#) 문자형식 날짜를 날짜형식으로 바꿔줌 |
| 없음 | WeekdayName(요일에 해당하는 숫자) | 요일을 일요일,월요일 등 한글로 표시해줌 |
| year,month,day(날짜) | 동일 | |
| hour,minute,second(시간) | 동일 | |
| weekday(날짜) | 동일 | 일요일은 0, 월요일은 1,... |
4. DateAdd, DateDiff, DatePart 함수에 적용되는 Interval 의 종류
| Interval | Description |
| yyyy | Year |
| q | Quarter |
| m | Month |
| y | Day of year |
| d | Day |
| w | Weekday |
| ww | Week |
| h | Hour |
| n | Minute |
| s | Second |
※ d 는 날짜를 의미하며 DateAdd, DateDiff, DatePart 함수 모두 차이가 없는데, y는 DatePart함수에서는 1월1일부터의 총 일수를 나타내는데(1월1일 포함), DateAdd나 DateDiff함수에서는 해당 월의 날짜를 의미합니다. 그리고, 분(Minute)이 m이 아니라 n인 점 주의하세요.
5. VBA로 구현하는 날짜 관련 기능
위 파일을 열 때 '매크로 포함 또는 제외' 를 물어보면 '매크로 포함'을 선택하고, '콘텐츠 사용'버튼이 있으면 '콘텐츠 사용'버튼을 클릭해야 합니다.
① 날짜 계산1(DateAdd)
날짜 계산 1 버튼을 클릭하면 숫자 입력을 요구하는 창이 나오고,
숫자를 입력하면 +,- 를 한 날짜를 구해 줍니다.
아래 프로시져문은 개발도구 - Visual Basic을 클릭한 후 날짜가감(vba).xlsm 아래 모듈 - Module1을 더블 클릭하면 열립니다.
Sub disp_dateadd()
Dim add_substract As Integer
Application.DisplayAlerts = False '셀 병합시 왼쪽 셀값만 남는다는 경고창이 나오는데, 안나오도록 하는 것입니다.
Range("a2") = "오늘"
Range("a3") = Date
Range("a4") = Now
add_substract = Application.InputBox("가감할 숫자를 입력하세요.", Type:=2)
Range("b1") = add_substract
Range("b2") = Range("b1") & "일 후"
Range("c2") = Range("b1") & "일 전"
Range("b3").Value = DateAdd("d", add_substract, Date)
Range("c3").Value = DateAdd("d", -add_substract, Date)
Range("b1:c1").Merge
Range("b1:c3").HorizontalAlignment = xlCenter
Application.DisplayAlerts = True
End Sub
㈀ dim add_substract As Integer
add_substract 란 변수를 정수형으로 선언합니다.
㈁ Application.DisplayAlerts = False
셀 병합시 "왼쪽 셀값만 남는다"는 경고창이 나오는데, 이 경고창을 나오지 않도록 하는 것입니다.
프로시져 끝날 때
Application.DisplayAlerts = True
라고 선언해서 원래대로 돌려 놓습니다.
㈂ range("a2") = "오늘"
Range("a3") = Date
Range("a4") = Now
a2셀에 "오늘"이란 글자를 입력하고,
a3셀에는 오늘 날짜,
a4셀에는 지금 현재 날짜 및 시간을 입력하는 것입니다.
range("a2").value 라고 입력하는 것이 정석이지만,
value인 경우는 생략 가능합니다.
VBA에서 셀을 지정할 때는 Range("셀 주소") 또는 Cells(행 수,열 수) 로 지정합니다.
㈂ add_substract = Application.InputBox("가감할 숫자를 입력하세요.", Type:=2)
"가감할 숫자"를 입력받아 add_substract란 변수에 저장합니다.
㈃ Range("b3").Value = DateAdd("d", add_substract, Date)
오늘 날짜에 add_substract 를 더한 날짜를 b3셀에 입력합니다.
dateadd라고 입력해도 자동으로 DateAdd라고 수정되어 입력됩니다.
② 날짜 계산2(DateDiff)
날짜 계산2 버튼을 누르면 날짜를 입력해달라는 입력창이 나타나며,
날짜를 입력하면 지금 시점의 날짜 및 시간(Now)과의 차이를 계산해 줍니다.
Sub disp_datediff()
Dim TheDate As Date
Dim Msg as String
Range("a6") = ""
TheDate = InputBox("날짜를 날짜형식(yyyy/mm/dd)으로 입력하세요.")
Msg = TheDate & "의 오늘부터 날수는 : " & DateDiff("d", Now, TheDate) & " 일 입니다.'"
Range("a6") = Msg
End Sub
㈀ TheDate와 Msg란 변수를 각각 날짜와 문자로 선언합니다.
Dim TheDate As Date
Dim Msg as String
㈁ Range("a6") = ""
a6셀을 빈칸으로 만들어 입력된 값이 있으면 지웁니다.
㈂ TheDate = InputBox("날짜를 날짜형식(yyyy/mm/dd)으로 입력하세요.")
날짜를 입력받아 TheDate라는 변수에 저장합니다.
㈃ Msg = TheDate & "의 오늘부터 날수는 : " & DateDiff("d", Now, TheDate) & " 일 입니다.'"
&는 문자 결합연산자이므로, 위 문장은
DateDiff 함수로 지금(Now)과 입력받은 날짜(TheDate)를 비교해서 날짜 차이를 구한 후
"입력 날짜의 오늘부터 날수는 :" 과 결합하고, 다시 " 일 입니다"와 결합한 문자열을 Msg란 변수에 저장하는 것입니다.
예를 들어, 2019/09/15의 오늘부터의 날수는 : 5 일입니다"란 문장이 됩니다.
㈄ Range("a6") = Msg
a6셀에 Msg란 문자열을 입력합니다.
③ 콤보박스를 이용하여 유형을 기준으로 한 일정 시점이후의 날짜 등 표시(DateAdd, DatePart)
DateAdd Interval 유형을 선택하면 B1셀의 값을 더한 날짜 및 시간을 A9셀에 표시해 줍니다.
콤보박스에 Interval 유형을 넣으려면 콤보박스를 누르기 전에 아무 셀이나 한 번 클릭해야 합니다.
㈀ 아래 프로시져는 개발도구 - Visual Basic을 누른 다음 sheet1을 더블 클릭하면 나옵니다.
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheet1.ComboBox1.Clear
For Each c In Sheet1.Range("g3:g12")
Sheet1.ComboBox1.AddItem c.Value
Next
End Sub
㈀ Option Explicit
변수를 명시적으로 선언하라는 문장입니다.
㈁ Private Sub Worksheet_SelectionChange(ByVal Target As Range)
워크시트의 셀 선택이 달라졌을 때 이루어지는 프로시져입니다.
㈂ Sheet1.ComboBox1.Clear
ComboBox1의 목록을 초기화하는 것입니다.
이렇게 안하면 계속 목록이 누적되기 때문에 초기화하는 것입니다.
㈃ For Each c In Sheet1.Range("g3:g12") ~ Next
g3셀에서 g12셀을 하나씩 이동하면서
㈄ Sheet1.ComboBox1.AddItem c.Value
shett1의 ComboBox1의 목록에 g3:g12셀에 있는 값을 하나씩 대입하는 것입니다.
Private Sub ComboBox1_Change()
If Len(ComboBox1.Value) > 0 Then
Range("a9") = DateAdd(ComboBox1.Value, Range("b1"), Now)
Range("a12") = DatePart(ComboBox1.Value, Range("a9"))
If ComboBox1.Value <> "y" Then
Range("a8") = "지금부터 " & Range("b1") & " " & _
Application.WorksheetFunction.VLookup(ComboBox1.Value, Range("g3:i12"), 3, 0) & " 후는?"
Range("a11") = "A9 셀의 " & _
Application.WorksheetFunction.VLookup(ComboBox1.Value, Range("g3:i12"), 3, 0) & " 은(는)?"
Else
Range("a8") = "지금부터 " & Range("b1") & " 일 후는?"
Range("a11") = "A9 셀의 1/1부터의 총 일수는?"
End If
Select Case ComboBox1.Value
Case "h", "n", "s" 'minute이 month와 겹쳐 n을 사용하고 있음을 주의하세요.
Range("a9").NumberFormat = "yyyy/mm/dd hh:mm:ss"
Range("a12").NumberFormat = "##"
Case Else
Range("a9").NumberFormat = "yyyy/mm/dd"
Range("a12").NumberFormat = "####"
End Select
End If
End Sub
㈅ Private Sub ComboBox1_Change()
ComboBox1의 값이 바뀌었을 때 이루어지는 프로시져입니다.
㈆ If Len(ComboBox1.Value) > 0 Then ~ End if
ComboBox1의 값(Interval 유형)이 있을 때만 실행합니다.
㈇ ⓐ Range("a9") = DateAdd(ComboBox1.Value, Range("b1"), Now)
a9셀에 ComboBox1의 유형을 기준으로 지금으로 부터 b1셀의 값만큼 더한 날짜를 입력합니다.
ⓑ Range("a12") = DatePart(ComboBox1.Value, Range("a9"))
a9셀의 날짜에서 ComboBox1.Value에 해당하는 유형(y,m,d 등) 값을 구해서 a12셀에 입력합니다.
㈈ If ComboBox1.Value <> "y" Then ~ End If
ComboBox1.Value가 y가 아니라면 DateAdd나 DatePart나 모두 날을 의미하는데, y 인 경우는 DateAdd인지 DatePart인지에 따라 의미가 다르므로 구분 처리를 위한 조건문으로, <>를 사용했으므로 "ComboBox1.Value가 y가 아니라면"이 됩니다.
㈉ Interfval 표(g3:i12)에서 '한글 뜻'을 찾아 처리합니다.
ⓐ Range("a8") = "지금부터 " & Range("b1") & " " &
Application.WorksheetFunction.VLookup(ComboBox1.Value, Range("g3:i12"), 3, 0) & " 후는?"
- vlookup은 VBA 자체 함수가 아니기 때문에 Application.WorksheetFunction 또는 WorksheetFunction을 앞에 붙여야 합니다.
- "지금부터"와 B1셀의 값, ComboBox1의 값과 일치하는 값을 g3:g12에서 찾은 후 세번째 열의 값(한글 뜻), 그리고 "후는?"을 연결한 값을 a8셀에 입력합니다. 다시 말해 "지금부터 5 day 후는?" 식이 됩니다.
ⓑ Range("a11") = "A9 셀의 " & _
Application.WorksheetFunction.VLookup(ComboBox1.Value, Range("g3:i12"), 3, 0) & " 은(는)?"
ComboBox1의 유형에 해당하는 설명(한글 뜻)을 찾아 A9셀의 다음에 놓고, 그 다음에 "는?"을 결합해 a11셀에 입력합니다. 다시 말해 "A9셀의 Day of Year는?" 식이 됩니다.
㈊ ComboBox1.Value가 y 라면 DateAdd 인 경우는 '일'이 되고, DatePart의 경우는 '총 일수'가 되므로 구분 처리합니다.
Else
Range("a8") = "지금부터 " & Range("b1") & " 일 후는?"
Range("a11") = "A9 셀의 1/1부터의 총 일수는?"
ⓐ Range("a8") = "지금부터 " & Range("b1") & " 일 후는?"
"지금부터 "와 b1셀의 값, "일 후는?"을 결합해서 a8셀에 입력합니다. 예를 들어 "지금부터 5일 후는?"이 됩니
다.
ⓑ Range("a11") = "A9 셀의 1/1부터의 총 일수는?"
"A9 셀의 1/1부터의 총 일수는?" 을 a11셀에 입력합니다.
㈋ case 문은 값, 여기서는 ComboBox1.Value 에 따른 처리를 해주는 조건문으로,
Select Case ComboBox1.Value
Case "h", "n", "s" 'minute이 month와 겹쳐 n을 사용하고 있음을 주의하세요.
Range("a9").NumberFormat = "yyyy/mm/dd hh:mm:ss"
Range("a12").NumberFormat = "##"
Case Else
Range("a9").NumberFormat = "yyyy/mm/dd"
Range("a12").NumberFormat = "#####"
End Select
ⓐ a9셀의 표시형식을 ComboBox1의 값이 case "h", "n", "s", 다시 말해 h(시),n(분),s(초) 면 yyyy/mm/dd hh:mm:ss 으로 하고, case else 다시 말해 아니면, yyyy/mm/dd 로 하는 것입니다.
ⓑ a12셀의 표시형식을 ComboBox1의 값이 case "h", "n", "s", 다시 말해 h(시),n(분),s(초) 면 ## 으로 하고, case else 다시 말해 아니면, #### 로 하는 것입니다. DatePart는 숫자를 반환하므로 시간은 2자리면 되고, 날짜는 최대 4자리로 해놓는데, ####이므로 숫자가 있는만큼만, 예를 들어 날짜는 두 자리, 연도는 네 자리로 표시됩니다.
㈌ 역삼각형으로 된 콤보박스 버튼을 누르고, 아래 목록에서 빨간 선으로 표시한 Interval 유형을 선택하면(빨간 석 아래에도 유형이 더 있으므로 아래로 내려 가려면 오른쪽 스크롤바를 내려야 합니다),
㈍ Interval 유형에 따라 B1셀의 값을 더해서 A8,A9셀에 더한 값을 표시하고, A9셀에 입력한 날짜중 유형에 해당하는 값(연,월,일 등)을 A11,A12에 표시합니다.
Interval 유형이 m이므로 월이 되고, 5와 결합하면 5개월후 날짜, 다시 말해 2019-09-11에서 5개월후인 2010-02-11과 해당 월인 2월을 구해주는 것입니다.
유형을 바꿔가면 해보시기 바랍니다.
6. 참고사항 및 VBA 에러 발생시 조치방법
① excel 함수의 경우는 엑셀 파일을 열면 자동으로 Today() 등의 값이 바뀌는데, VBA는 실행하지 않으면, 여기서는 '날짜 계산1'버튼을 누르지 않으면 현재 날짜인 Date함수 등의 결과가 바뀌지 않습니다. 물론 now()함수도 실시간으로 변하지는 않습니다.
② 아래와 같은 에러가 발생하면 '종료' 버튼을 누르세요. '디버그'버튼은 프로그램이 잘못 됐을 경우 수정하기 위한 기능인데, 프로그램에 특별한 문제는 없습니다. 물론 에러가 발생하지 않도록 더 정교하게 프로그램을 만들어야 하겠지만요.
③ 에러 발생시 종료하도록 Sheet1의 ComboBox1_Change 에 아래 루틴을 추가했습니다.
| On Error GoTo ErrRtn ErrRtn: End |
On Error GoTo ErrRtn : 에러가 발생하면 ErrRtn으로 가고,
ErrRtn에 End라고, 실행을 종료하도록 했습니다.
그러나, Module1에도 추가하려고 했더니 제대로 작동을 안해서 지웠습니다.
'EXCEL - VBA' 카테고리의 다른 글
| 엑셀의 유용함, 사용자 정의함수(2) (0) | 2020.07.28 |
|---|---|
| 엑셀의 유용함, 사용자 정의함수(1) (0) | 2020.07.27 |
| 개인용 매크로 통합문서, personal.xlsb (0) | 2020.07.10 |
| 윗셀과 같은 값 지우기(2) - while 문 (0) | 2020.07.10 |
| 윗셀과 같은 값 지우기(1) - for 문 (0) | 2020.07.09 |