|
下記のコードを追加してください
Public Function A1Add(sa As String, nr As Long, nc As Long) As String
Dim i As Long
Dim j As Long
Dim srow As String
Dim scol As String
Dim lrow As Long
Dim lcol As Long
srow = ""
scol = ""
sa = UCase(sa)
'行列を分ける
For i = 1 To Len(sa)
If Mid(sa, i, 1) >= "A" And Mid(sa, i, 1) <= "Z" Then
srow = srow & Mid(sa, i, 1)
ElseIf Mid(sa, i, 1) <> "$" Then
scol = scol & Mid(sa, i, 1)
End If
Next
'A1形式の列を列番号にする
If Len(srow) = 1 Then
lrow = Asc(srow) - 64
Else
lrow = (Asc(Left(srow, 1)) - 64) * 26
lrow = lrow + (Asc(Right(srow, 1)) - 64)
End If
'加算
If nc > 0 Then
If lrow + nc <= 65536 Then
lrow = lrow + nc
End If
ElseIf nc < 0 Then
If lrow + nc >= 1 Then
lrow = lrow + nc
End If
End If
'列番号をA1形式に戻す
If lrow <= 26 Then
srow = Chr(64 + lrow)
Else
i = Int(lrow / 26)
j = lrow Mod 26
If j = 0 Then
srow = Chr(64 + i - 1) & "Z"
Else
srow = Chr(64 + i) & Chr(64 + j)
End If
End If
'行番号の加算
lcol = scol
If nr > 0 Then
If lcol + nr <= 65536 Then
lcol = lcol + nr
End If
ElseIf nr < 0 Then
If lcol + nr >= 1 Then
lcol = lcol + nr
End If
End If
scol = lcol
A1Add = srow & scol
End Function
'国民の祝日か判定
Public Function GetSaijituName(tSrcDate As Date) As String
Dim SaiName As String
Dim tdate As Date
SaiName = ""
Select Case CLng(Format(tSrcDate, "mmdd"))
Case 101 '1948年-元日制定
If tSrcDate >= #7/20/1948# Then
SaiName = "元日"
End If
Case 108 To 114 '2000年1月第2月曜-成人の日
If tSrcDate >= #1/1/2000# Then
If Weekday(tSrcDate, vbSunday) = vbMonday Then
SaiName = "成人の日"
End If
End If
Case 115 '1948年〜1999年-成人の日
If tSrcDate >= #7/20/1948# And tSrcDate < #1/1/2000# Then
SaiName = "成人の日"
End If
Case 116 '1948年〜1999年-成人の日 振替休日
If tSrcDate >= #7/20/1948# And tSrcDate < #1/1/2000# _
And Weekday(tSrcDate, vbSunday) = vbMonday Then
SaiName = "振替休日"
End If
Case 211 '1967年-建国記念の日
If tSrcDate >= #6/25/1966# Then
SaiName = "建国記念の日"
End If
Case 224 '1989/2/24-大喪の礼
If tSrcDate = #2/24/1989# Then
SaiName = "大喪の礼"
End If
Case 319 To 322 '1948年-春分の日 1973年-振替休日
If tSrcDate >= #7/20/1948# Then
tdate = HaruAkiDay(Year(tSrcDate), 0)
If tdate = tSrcDate Then
SaiName = "春分の日"
ElseIf tSrcDate >= #4/12/1973# And _
(Weekday(tSrcDate, vbSunday) = vbMonday) And _
(tdate + 1 = tSrcDate) Then
SaiName = "振替休日"
End If
End If
Case 429 '1948年-天皇誕生日,1989年-みどりの日
If tSrcDate >= #2/17/1989# Then
SaiName = "みどりの日"
ElseIf tSrcDate >= #7/20/1948# Then
SaiName = "天皇誕生日"
End If
Case 503 '1948年-憲法記念日
If tSrcDate >= #7/20/1948# Then
SaiName = "憲法記念日"
End If
Case 504 '1973年-振替休日,1985年-国民の休日
Select Case Weekday(tSrcDate, vbSunday)
Case vbSunday
Case vbMonday
If tSrcDate >= #4/12/1973# Then
SaiName = "振替休日"
End If
Case Else
If tSrcDate >= #12/27/1985# Then
SaiName = "国民の休日"
End If
End Select
Case 505 '1948年-憲法記念日
If tSrcDate >= #7/20/1948# Then
SaiName = "こどもの日"
End If
Case 715 To 721 '1996年-海の日,2003年第3月曜
If tSrcDate >= #1/1/2003# Then
If Weekday(tSrcDate, vbSunday) = vbMonday Then
SaiName = "海の日"
End If
ElseIf tSrcDate >= #1/1/1996# And Day(tSrcDate) = 20 Then
SaiName = "海の日"
End If
Case 915 To 921 '1996年-敬老の日,2003年第3月曜
If tSrcDate >= #6/25/1966# And tSrcDate < #1/1/2003# Then
SaiName = "敬老の日"
Else
If Weekday(tSrcDate, vbSunday) = vbMonday Then
SaiName = "敬老の日"
End If
End If
Case 921 To 924 '1948年-秋分の日,1973年-振替休日
If tSrcDate >= #7/20/1948# Then
tdate = HaruAkiDay(Year(tSrcDate), 1)
If tdate = tSrcDate Then
SaiName = "秋分の日"
ElseIf tSrcDate >= #4/12/1973# And _
(Weekday(tSrcDate, vbSunday) = vbMonday) And _
(tdate + 1 = tSrcDate) Then
SaiName = "振替休日"
End If
End If
Case 1008 To 1014 '1966年-体育の日,2000年第2月曜
If tSrcDate >= #1/1/2000# Then
If Weekday(tSrcDate, vbSunday) = vbMonday Then
SaiName = "体育の日"
End If
'平成10年法律141号-[体育の日]10月10日→10月第2月曜:平成12年1月1日より
ElseIf tSrcDate >= #6/5/1966# Then
If Day(tSrcDate) = 10 Then
SaiName = "体育の日"
ElseIf Day(tSrcDate) = 11 Then '1973年-振替休日
If tSrcDate >= #4/12/1973# And _
Weekday(tSrcDate, vbMonday) = vbMonday Then
SaiName = "体育の日"
End If
End If
End If
Case 1103 '1948年-文化の日
If tSrcDate >= #7/20/1948# Then
SaiName = "文化の日"
End If
Case 1123 '1948年-勤労感謝の日
If tSrcDate >= #7/20/1948# Then
SaiName = "勤労感謝の日"
End If
Case 1223 '1989年-天皇誕生日
If tSrcDate >= #2/17/1989# Then
SaiName = "天皇誕生日"
End If
Case 102, 212, 430, 506, 721, 916, 1104, 1124, 1224 '1973年-振替休日
If tSrcDate >= #4/12/1973# And Weekday(tSrcDate, vbSunday) = vbMonday Then
SaiName = "振替休日"
End If
Case Else
SaiName = ""
End Select
GetSaijituName = SaiName
End Function
'春分の日,秋分の日
Public Function HaruAkiDay(ByVal yy As Integer, haru As Integer) As Date
Dim vv As Variant
Dim yr As Variant
Dim mo As Integer
Dim dy As Integer
If 1980 <= yy And yy <= 2099 Then
yr = CDec(yy - 1980)
If haru = 0 Then '春分の日
mo = 3
vv = CDec("20.8431")
Else '秋分の日
mo = 9
vv = CDec("23.2488")
End If
dy = CLng(Int(vv + CDec(0.242194) * yr - Int(yr / CDec(4))))
HaruAkiDay = DateSerial(yy, mo, dy)
Else
HaruAkiDay = CDate(0)
End If
End Function
'月の末日を取得
Public Function MonthLastDay(yy As Integer, mm As Integer) As Integer
Dim i As Integer
Dim tdate As Date
tdate = Format(yy & "/" & mm & "/1", "yyyy/mm/dd")
i = 28
Do
i = i + 1
Loop Until Day(tdate + i - 1) = 1
MonthLastDay = i - 1
End Function
|
|
|
|
|
|
|
|