Excel VBA 日付・時刻


Excel VBA 目次

待ちの設定
処理時間計測
日数計算
稼動日数だけさかのぼった日付を求める
前後の月を求める
文字列から日付けを求める
年月の文字列から前月を求める

索引

日付・時刻の関数

機能関数
システムの日付Date
日付の年の部分Year(日付)
日付の月の部分Month(日付)
日付の日の部分Day(日付)
システムの時刻Time
時刻の時の部分Hour(時刻)
時刻の分の部分Minute(時刻)
時刻の秒の部分Second(時刻)
システムの日付と時刻Now
指定した間隔を加算DateAdd(単位、追加時間、日付)
二つの日付の間隔DateDiff(単位、日付1、日付2)
日付の指定した部分の値DatePart(単位、日付)
日付のシリアル値DateSerial(日付)
文字列を日付型(日付)に変換DateValue(日付文字列)
文字列を日付型(日付時刻)に変換CDate(日付文字列)
文字列を日付型(時刻)に変換TimeValue(日付文字列)
日付の曜日の値Weekday(日付)
日付の曜日の文字列WeekdayName
日付を文字列に変換Format

この種類の目次に戻る↑ 索引へ↓ トップページに戻る

待ちの設定

 InputBoxの画面が消えるまで、次の処理に移らないように、など、「待ち」を入れたいことが有ります。
 下記は、Wait メソッドTimer 関数DateDiff 関数API の Sleep 関数 を用いて、指定秒間待たせます。

 注1:Bは、午前 0 時 (真夜中) から経過した秒数を表す Timer 関数 を使っています。このため、真夜中をまたぐ処理には使えません。(私はこれで はまり ました。(^^ゞ
    Cは、真夜中をまたいで、使えます。

 注2:Dは、下記の「API 技術関連」で教えていただきました。
  http://homepage1.nifty.com/MADIA/vb/VBKANREN.htm
     DがCPU負荷が最も少なく、推奨です。
   参考:
    Windows APIを初めて使う方たちへ
   http://www.winapi-database.com/Beginner/
    やりたい事から関数を探す
   http://www.winapi-database.com/purpose.html


Private Sub 待ち@()    'Excelの画面が表示されていると、CPUの負荷が高い
Dim newHour, newMinute, newSecond, waitTime
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 2
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
End Sub


Private Sub 待ちA()
    Application.Wait Time:=Now + TimeValue("00:00:02")  '2秒間停止します。
End Sub


'待ちB
Sub テスト()
   Call 指定秒待つ(0.5)                      ' 中断時間は引数で指定します。
   MsgBox "終了"
End Sub

Private Sub 指定秒待つ(待ち時間 As Single) ' 中断時間は引数で指定します。
   Dim 再開時刻 As Single
   
   再開時刻 = Timer + 待ち時間
   Do While Timer < 再開時刻
      DoEvents                               ' 他のプロセスに制御を渡します。
   Loop
End Sub


'待ちC
Sub テスト2()
   Call 指定秒待つ2(5)                               ' 中断時間は引数で指定します。
   MsgBox "終了"
End Sub

Private Sub 指定秒待つ2(待ち時間 As Integer)       ' 中断時間は引数で指定します。
   Dim 起点時刻 As Variant
   
   起点時刻 = Now
   Do While DateDiff("s", 起点時刻, Now) < 待ち時間
      DoEvents                                      ' 他のプロセスに制御を渡します。
   Loop
End Sub

'待ちD
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
'これで Sleepを使ってミリ秒単位で処理にウェイトを入れることができます。
Private Sub 指定秒待つ3(待ち時間 As Integer)   
   DoEvents       ' Sleep しているときに、画面を書き換えできるように、OS に制御を渡す。
   Sleep (待ち時間 * 1000)
End Sub

 解説:
 Now 関数は、コンピュータのシステムの日付と時刻の設定に基づいて、現在の日付と時刻を表すバリアント型 (内部処理形式 Date の Variant) の値を返します。

 TimeSerial 関数は、引数で指定した時、分、および秒に対応する時刻を含むバリアント型 (内部処理形式 Dateの Variant) の値を返します。各引数に指定する値が正しい範囲内にない場合、指定された値は引数に従って加算されます。たとえば、名前付き引数 minute に 75 を指定すると、1 時間と 15 分として扱われます。ただし、各引数の値が -32,768 〜 32,767 の範囲を超える場合、エラーが発生します。


 TimeValue 関数 は、時刻を表すバリアント型 (内部処理形式 Date の Variant) の値を返します。

構文: TimeValue(time)
 引数 time は必ず指定します。
引数 time には、通常 0:00:00 (12:00:00 AM) 〜 23:59:59 (11:59:59 PM) の範囲の時刻を表す文字列式を指定します。
また、この範囲で時刻を表す任意の式を指定することもできます。
引数 time に Null 値が含まれると、Null 値を返します。

 12 時間制または24 時間制のどちらを使って時刻を指定してもかまいません。
たとえば、"2:24PM" と "14:24" は、両方とも有効な引数となります。
 引数 time が日付の値を含む場合、TimeValue 関数はその日付を戻り値に含めません。ただし、引数 time に正しくない値を指定したときには、エラーが発生します。

 次の例は、TimeValue 関数を使って、文字列を時刻に変換します。
時刻リテラルを使って、バリアント型 (Variant) や日付型 (Date) の変数に時刻を直接代入することもできます。たとえば、MyTime = #4:35:17 PM# のように指定します。

Dim MyTime
MyTime = TimeValue("4:35:17 PM")            ' 時刻を返します。

 次の例は、FileSystemObject を使って、フォルダ内のすべてのサブ・フォルダ を再帰的に探索して、処理対象にして、Excel ファイルを開いて、データを抽出するプログラムの一部です。
 対象の Excel ファイル(検査レポート)の中には、ファイルが破損(corrupt)しているものもあり、Excel ファイルを開いたときに、Excel が異常終了してしまうものがありました。
 そのため、TimeValue 関数を使って、5分毎に、途中の状況のファイルを保存しています。
 破損したファイル名が分かるように、ファイル名をログ出力しています。

Private Sub フォルダを次々調べてデータを取得(フォルダ)

   For Each サブフォルダ In フォルダ.SubFolders

      If InStr(サブフォルダ.Name, "対象外") = 0 Then

         処理フォルダ = サブフォルダ.Path

         Set フォルダ_オブジェクト = ファイルシステムオブジェクト.getfolder(処理フォルダ)

         For Each ファイル In フォルダ_オブジェクト.Files
            ファイル名 = ファイル.Name
            ファイルフルパス = ファイル.Path
            拡張子 = ファイルシステムオブジェクト.GetExtensionName(ファイル名)

            If LCase(拡張子) = "xls" Or LCase(拡張子) = "xlsx" Then

               If 検査レポートブック索引.Exists(ファイル名) = False Then
                  '★集計済みブック索引に存在しない場合

                  '開いたファイル名を、テキスト出力する
                  出力テキストストリームオブジェクト.WriteLine ファイルフルパス         ' 改行(CrLf)付き

                  Call 検査レポートのデータを取得

               End If  '対象は、未集計ブックのみ

            End If  '対象は、エクセルブックのみ

            If Now >= ファイル保存時刻 + TimeValue("0:05:00") Then
               '検査レポートに不適切なファイルが有って中断しても再開が容易なように、5分おきに強制保存
               ファイル保存時刻 = Now

               Application.DisplayAlerts = False
               集計ブック.SaveAs Filename:= _
               ThisWorkbook.Path & "\" & Left(集計ブック名, InStrRev(集計ブック名, ".") - 1) _
               & "_" & Format(ファイル保存時刻, "yyyymmdd_hhmm") & ".xlsx"
               Application.DisplayAlerts = True
            End If

         Next '★サブフォルダーに登録された、すべてのファイル

         Call フォルダを次々調べてデータを取得(サブフォルダ) '再帰呼び出しで、下位のフォルダ全てを対象とする
      End If
   Next '★フォルダに登録された、「対象外」を含まないすべてのサブフォルダー

End Sub


 Wait メソッドは、実行中のマクロを指定の時刻まで停止します。指定の時間に達した場合、True を返します。
 Wait メソッドは、Excel のすべての動作を停止させますが、印刷や再計算などのバックグラウンド処理は続行されます。

expression.Wait(Time)
expression 必ず指定します。Application オブジェクトを返すオブジェクト式を指定します。
Time 必ず指定します。バリアント型 (Variant) の値を使用します。マクロを再開する時刻を Excel の日付の書式で指定します。

 この形式は、時間を待つのではなく、「時刻まで待つ」点に注意が必要です。
 例えば「10:55:56.9」のときに、この方法で 1秒待つと、実際は 0.1 秒しか待ってくれません。
 参考にさせていただいたサイト:あにきないきざま
http://a2ki.blog.so-net.ne.jp/2010-10-14

 次の使用例は、実行中のマクロを当日の午後 6 時 23 分まで停止します。

Application.Wait "18:23:00"

 次の使用例は、実行中のマクロを約 10 秒間停止します。

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

 次の使用例は、10 秒を過ぎるとメッセージを表示します。

If Application.Wait(Now + TimeValue("0:00:10")) Then
    MsgBox "時間が過ぎました。"
End If


 Timer 関数は、午前 0 時 (真夜中) から経過した秒数を表す単精度浮動小数点数型 (Single) の値を返します。
 Timer 関数は小数点以下の値も返します。
 処理が日をまたがらない場合は、この関数を使ったほうが、処理が軽く、シンプルです。


 DoEvents ステートメントは、他のアプリケーションが実行できるように Windows に制御を返します。

 Event ステートメントの使用例

 次の例では、イベント機能を使って、100 メートル走世界記録のデモンストレーションの経過秒数を計っています。
 コードには、Event ステートメントを含む、イベント機能に関連するメソッド、プロパティ、およびステートメントが、すべて示されています。

 イベントを発行するクラスがイベント ソースで、イベントをインプリメントするクラスがイベント シンクです。
 イベント ソースは、複数のイベント シンクに対して、イベントを通知できます。
 イベント ソースのクラスがイベントを生成すると、オブジェクトのそのインスタンスに対するイベント シンクとして選択されている各クラスに、そのイベントが通知されます。

 この例では、ボタン (Command1)、ラベル (Label1)、および 2 つのテキスト ボックス (Text1 と Text2) を備えたフォーム (Form1) も使われています。
 ボタンをクリックすると、最初のテキスト ボックスに "スタート" と表示されて、2 番目のテキスト ボックスでは秒数の計測が始まります。
 世界記録の秒数 (9.84 秒) が経過すると、最初のテキスト ボックスには "ゴール" と表示されて、2 番目のテキスト ボックスには "9.84" と表示されます。

 フォーム Form1 に対するコードには、フォームの開始状態と終了状態が記述されています。
 また、イベントが生成されたときに実行されるコードも含まれています。

Option Explicit

Private WithEvents mText As TimerState

Private Sub Command1_Click()
Text1.Text = "スタート"
    Text1.Refresh
    Text2.Text = "0"
    Text2.Refresh
Call mText.TimerTask(9.84)
End Sub

Private Sub Form_Load()
    Command1.Caption = "クリックすればタイマーがスタートします。"
    Text1.Text = ""
    Text2.Text = ""
    Label1.Caption = "これが、100 メートル走の世界記録のタイムです。"
    Set mText = New TimerState
    End Sub

Private Sub mText_ChangeText()
    Text1.Text = "ゴール"
    Text2.Text = "9.84"
End Sub

Private Sub mText_UpdateTime(ByVal dblJump As Double)
    Text2.Text = Str(Format(dblJump, "0"))
    DoEvents
End Sub

 以下は、クラス モジュール TimerState のコードです。Event ステートメントを使って、イベントが発生したときに実行されるプロシージャを宣言しています。

Option Explicit
Public Event UpdateTime(ByVal dblJump As Double)
Public Event ChangeText()

Public Sub TimerTask(ByVal Duration As Double)
    Dim dblStart As Double
    Dim dblSecond As Double
    Dim dblSoFar As Double
    dblStart = Timer
    dblSoFar = dblStart

    Do While Timer < dblStart + Duration
        If Timer - dblSoFar >= 1 Then
            dblSoFar = dblSoFar + 1
            RaiseEvent UpdateTime(Timer - dblStart)
        End If
    Loop

    RaiseEvent ChangeText

End Sub

この種類の目次に戻る↑ 索引へ↓ トップページに戻る


処理時間計測

 マクロを走らせて処理した経過時間を測定したいことが有ります。こんなとき、以下の sub を開始直前と直後に挿入します。ストップ・ウオッチのようになります。
 経過時間計測B を推奨します。


Option Explicit
Dim 開始秒 As Single
Dim 開始日時 As Variant

Private Sub 開始時刻取得@()
   開始秒 = Timer                ' 開始時刻を変数に格納します。
End Sub

Private Sub 経過時間計測@()
Dim 終了秒 As Single
Dim 処理秒 As Long

Dim 経過時間 As Integer
Dim 経過分数 As Integer
Dim 経過秒数 As Integer

   終了秒 = Timer                ' 終了時刻を変数に格納します。
   処理秒 = 終了秒 - 開始秒    ' 処理時間(秒)を計算します。
   経過時間 = 処理秒 \ 3600
   経過分数 = (処理秒 Mod 3600) \ 60
   経過秒数 = (処理秒 Mod 3600) Mod 60
   MsgBox "処理(所要)時間は、" & 経過時間 & " 時間 " _
   & 経過分数 & " 分 " & 経過秒数 & " 秒でした。"
End Sub



Private Sub 開始時刻取得A()
   開始日時 = Now                ' 開始時刻を変数に格納します。
End Sub

Private Sub 経過時間計測A()
Dim 終了日時 As Variant
Dim 所要時間 As Variant

   終了日時 = Now                '終了時刻を取り出す
   所要時間 = Abs(DateDiff("s", 開始日時, 終了日時))
   MsgBox "所要時間は、" & 所要時間 & " 秒でした。"
End Sub



Private Sub 経過時間計測B() '★推奨★
Dim 終了日時 As Variant

   終了日時 = Now
   MsgBox "処理時間は、" _
   & Format(終了日時 - 開始日時, "hh時間nn分ss秒") & " でした。"
End Sub

 解説:
 「経過時間計測@」の方は、日付けが変わらない範囲で有効な Timer 関数を使っています。

 長整数型 (Long) の変数は、32 ビット (4 バイト) の変数で、-2,147,483,648 〜 2,147,483,647 の範囲の値をとります。この例では、Timer 関数が小数点付き(Single)の値を出力するため、この型に変換して、整数にしています。1日は、60秒*60分*24時間=86,400秒なので、整数型 (Integer)(16 ビット (2 バイト) の、-32,768 〜 32,767)では、収まりません。

 注意:整数型 (Integer)と整数型 (Integer)を掛け算した結果が、その範囲より大きくなると、「実行時エラー '6':オーバーフローしました。」となります。

Option Explicit
Sub 計算()
Dim i As Integer
Dim j As Integer
   i = 1000
   j = 2000
   Range("A1") = i * j
End Sub

上記の場合は、一方を例えば、Dim i As Long としてやると、エラーになりません。

 ヘルプには、以下の記述が有ります。
 数値計算で数値が内部的に整数型として処理されている場合に、計算結果が整数型の範囲を超えています。次の例では、値がオーバーフローします。
 Dim x As Long
 x = 2000 * 365  ' エラー: 値がオーバーフローします。

このようなエラーでは、次のように対処してください。
 Dim x As Long
 x = CLng(2000) * 365

 \ 演算子は、2 つの数値の商を計算し、結果を整数で返します。この例では、秒を時間と分に換算しています。

 Mod 演算子は、2 つの数値の除算を行い、その剰余を返します。この例では、分に換算した残りの秒の部分を、求めています。
 整数の、奇数偶数判定にも使います。
 剰余 = 割られる数式 Mod 割る数式
 小数部分はすべて切り捨てられます。ただし、一方または両方の式が Null 値のときは、演算結果 result も Null 値になります。Empty 値を持つ式は、0 として扱われます。


 「経過時間計測A」の方は、Now 関数を使っているので、日付けをまたいだ処理でも、測定できます。

 Abs(number) 関数(absolute)は、引き渡した数値の絶対値を同じデータ型で返します。
 ここでは、date1, date2の前後の順番を書き間違えても、正数を表示させるために、使っています。


 「経過時間計測B」は、Format 関数を使って、時間表示しています。これが一番簡単ですね。

 Format 関数は、式を指定した書式に変換し、その文字列を示すバリアント型 (内部処理形式 String の Variant) の値を返します。
 例えば、Format(1, "000")は、001になります。
 対象形式が、日付と時刻の場合は、定義済み日付/時刻書式、または日付/時刻表示書式指定文字(上の例では、hh、nnやss)を使用して、書式を設定します。
 例えば、出力するファイル名に、生成日を使いたいとき、now だけだと、ファイル名としての禁止文字「/」が入ってしまいます。Format を使うと、下記のようにできます。

? now
2011/10/01 11:13:53 

? Format(Now, "yyyymmdd_hhnn")
20111001_1118

 次の表は、日付/時刻表示書式指定文字とその内容を示します。
文字内容
(:)時刻の区切り記号です。
オペレーティング システムの国別情報の設定によっては、時刻の区切り記号として他の記号が使用されることがあります。
時刻を時間、分、および秒で区切ることができます。
変換後の時刻の区切り記号は、コントロール パネルの設定によって決まります。
(/)日付の区切り記号です。
オペレーティング システムの国別情報の設定によっては、他の記号が使用されることがあります。
日付を年、月、および日で区切ることができます。
変換後の区切り記号は、コントロール パネルの設定によって決まります。
cddddd および t t t t t の書式で表した日付と時刻を、日付、時刻の順序で返します。
指定された値に小数部がない場合は日付のみ、整数部がない場合は時刻のみを表す文字列を返します。
d日付を返します。1 桁の場合、先頭に 0 が付きません (1 〜 31)。
dd日付を返します。1 桁の場合、先頭に 0 が付きます (01 〜 31)。
ddd曜日を英語 (省略形) で返します (Sun 〜 Sat)。
aaa曜日を日本語 (省略形) で返します (日〜土)。
dddd曜日を英語で返します (Sunday 〜 Saturday)。
aaaa曜日を日本語で返します (日曜日〜土曜日)。
ddddd年、月、日を含む短い形式 (コントロール パネルで設定) で表した日付を返します。
既定の短い日付形式は、m/d/yy です。
dddddd年、月、日を含む長い形式 (コントロール パネルで設定) で表した日付を返します。
既定の長い日付形式は mmmm dd, yyyy です。
w曜日を表す数値を返します (日曜日が 1、土曜日が 7 となります)。
wwその日が一年のうちで何週目に当たるかを表す数値を返します (1 〜 54)。
m月を表す数値を返します。
1 桁の場合、先頭に 0 が付きません (1 〜 12)。
ただし、h や hh の直後に m を指定した場合、月ではなく分と解釈されます。
mm月を表す数値を返します。
1 桁の場合、先頭に 0 が付きます (01 〜 12)。
ただし、h や hh の直後に mm を指定した場合、月ではなく分と解釈されます。
mmm月の名前を英語 (省略形) の文字列に変換して返します (Jan 〜 Dec)。
mmmm月の名前を英語で返します (January 〜 December)。
oooo月の名前を日本語で返します (1 月 〜 12 月)。
q1 年のうちで何番目の四半期に当たるかを表す数値を返します (1 〜 4)。
g年号の頭文字を返します (M、T、S、H)。
gg年号の先頭の 1 文字を漢字で返します (明、大、昭、平)。
ggg年号を返します (明治、大正、昭和、平成)。
e年号に基づく和暦の年を返します。
1 桁の場合、先頭に 0 が付きません。
ee年号に基づく和暦の年を 2 桁の数値を使って返します。
1 桁の場合、先頭に 0 が付きます。
y1 年のうちで何日目に当たるかを数値で返します (1 〜 366)。
yy西暦の年を下 2 桁の数値で返します (00 〜 99)。
yyyy西暦の年を 4 桁の数値で返します (100 〜 9999)。
h時間を返します。
1 桁の場合、先頭に 0 が付きません (0 〜 23)。
hh時間を返します。
1 桁の場合、先頭に 0 が付きます (00 〜 23)。
n分を返します。
1 桁の場合、先頭に 0 が付きません (0 〜 59)。
nn分を返します。
1 桁の場合、先頭に 0 が付きます (00 〜 59)。
s秒を返します。
1 桁の場合、先頭に 0 が付きません (0 〜 59)。
ss秒を返します。
1 桁の場合、先頭に 0 が付きます (00 〜 59)。
tt t t t コントロール パネルで設定されている形式で時刻を返します。
先頭に 0 を付けるオプションが選択されていて、時刻が午前または午後 10 時以前の場合、先頭に 0 が付きます。
既定の形式は、h:mm:ss です。
AM/PM時刻が正午以前の場合は大文字で AM を返し、
正午〜午後 11 時 59 分の間は大文字で PM を返します。
am/pm時刻が正午以前の場合は小文字で am を返し、
正午〜午後 11 時 59 分の間は小文字で pm を返します。
A/P時刻が正午以前の場合は大文字で A を返し、
正午〜午後 11 時 59 分の間は大文字で P を返します。
a/p時刻が正午以前の場合は小文字で a を返し、
正午〜午後 11 時 59 分の間は小文字で p を返します。
AMPM"12 時間制" が選択されていて、時刻が正午以前の場合は午前を表すリテラル文字列を、正午〜午後 11 時 59 分の間は午後を表すリテラル文字列を返します。
これらの文字列の設定および "12 時間制" の選択は、コントロール パネルで行います。
AMPM は大文字、小文字のどちらでも指定できます。
既定の形式は、AM/PM です。


数値表示書式指定文字 (Format 関数)

次の表は、数値表示書式指定文字とその内容を示します。
これらの文字を組み合わせて、ユーザー定義の書式を指定することができます。
文字 内容
なし 指定した数値をそのまま返します。
(0) 桁位置や桁数を指定するときに使います。
引数 Format に指定した書式文字列内の表示書式指定文字 "0" 1 つで、数値の 1 桁を表します。
変換対象の数値 (式) が、"0" で指定された桁位置を使っている場合は、その桁に該当する値が入ります。
変換対象の数値の桁数が少なく、指定された桁位置に該当する値がない場合は、その桁には 0 が入ります。
引数 expression に指定した数値の整数部または小数部の桁数が、指定書式内の "0" の桁位置に満たない場合は、その桁位置には 0 が付加されます。
また、数値の小数部の桁数が小数部に指定した "0" の桁位置を超える場合には、数値の小数部は指定の桁位置に合わせて四捨五入されます。
逆に、整数部の桁数が整数部に指定した "0" の桁位置を超える場合には、整数部は変更されることなく、すべて表示されます。
(#) 桁位置や桁数を指定するときに使います。
引数 Format に指定した書式文字列内の表示書式指定文字 "#" 1 つで、数値の 1 桁を表します。
変換対象の数値 (expression) が "#" で指定された桁位置を使っている場合は、その桁に該当する値が入ります。
変換対象の数値の桁数が少なく、指定された桁位置に該当する値がない場合は、その桁には何も入りません。
この記号は表示書式指定文字の "0" と同じような働きをしますが、数値の小数部や整数部の桁数が "#" で指定された桁位置に満たない場合に 0 は挿入されず、その桁には何も入りません。
(.) 表示書式指定文字 ("0" または "#") と組み合わせて、小数点の位置を指定するときに使います。
表示する桁数を指定するとき、この表示書式指定文字の位置によって、整数部と小数部が区別されます。指定書式内で "." の左側に "#" だけが指定されている場合は、1 未満の数値は小数点記号から始まります。
数値が 1 未満の場合に小数点記号の左側に常に 0 が付くようにするには、指定書式内で " " の左側に "#" ではなく "0" を指定します。
変換後の小数点記号は、オペレーティング システムの国別情報の設定によって決まります。
(%) 数値を 100 倍し、パーセント記号 (%) を付けるときに指定します。
(,) 1000 単位の区切り記号を挿入するときに指定します。
整数部が 4 桁以上ある数値については、1000 単位の区切り記号が付きます。
変換後の 1000 単位の区切り記号は、オペレーティング システムの国別情報の設定によって決まります。
通常、この表示書式指定文字 "," の前後に "0" または "#" を指定して使います。
3桁でカンマ区切りしたい場合は、? Format(10000, "#,##0")とします。

この表示書式指定文字 "," の右側に "0" も "#" も指定しない場合、つまり、整数部の右端にこの表示書式指定文字 "," を 1 つ、または 2 つ以上続けて指定した場合 (小数部の表示指定の有無は任意)、変換対象の数値は 1000 単位で割った値に変換されます。
このとき、値は桁位置の指定に応じて丸められます。
たとえば、書式指定文字列として "##0,," と指定すると、数値 100000000 (1 億) は、100 に変換されます。100 万未満の数値は 0 となります。
整数部の右端以外でこの表示書式指定文字 "," を 2 つ以上続けて指定した場合は、"," を 1 つ指定したときと同じになります。
(:) 時刻の区切り記号を挿入するときに指定します。
時刻を時間、分、秒で区切ることができます。変換後の時刻の区切り記号は、オペレーティング システムの国別情報の設定によって決まります。
(/) 日付の区切り記号を挿入するときに指定します。
日付を年、月、日で区切ることができます。
変換後の区切り記号は、オペレーティング システムの国別情報の設定によって決まります。
(E- E+ e- e+) 指数表記で表すときに指定します。
"E-"、"E+"、"e-"、"e+" のいずれかの右側に "0" または "#" を 1 つ以上指定すると、数値は指数表記で表され、整数部と指数部の間に e または E が挿入されます。
これらの表示書式指定文字の右側に指定する "0" または "#" の数は、指数部の桁数を示します。"E-" や "e-" を使うと、指数が負の場合にはマイナス記号が付きます。
"E+" や "e+" の場合は、指数の正負に合わせてプラス記号かマイナス記号が付きます。
- + $ ( ) スペース 指定する文字をそのまま挿入します。
これら以外の表示書式指定文字を挿入するには、その前に円記号 (\) を付けるか、ダブル クォーテーション (" ") で囲みます。
(\) すぐ後に続く 1 文字をそのまま表示します。
書式指定の中で、特別な意味を持っている "#" または "E" などの文字を文字としてそのまま表示するには、その文字の前に円記号 (\) を付けます。
この場合、前に付けた円記号 (\) は表示されません。文字をダブル クォーテーション (" ") で囲んでも、同じです。円記号 (\) を挿入するには、円記号 (\) を 2 つ続けて記述します (\\)。
そのままでは挿入できない文字としては、日付や時刻の表示書式指定文字 (a、c、d、h、m、n、p、q、s、t、w、y、/、:)、数値の表示書式指定文字 (#、0、%、E、e、カンマ、ピリオド)、文字列の表示書式指定文字 (@、&、<、>、!) などがあります。
("ABC") ダブル クォーテーション (" ") で囲まれた文字列は、そのまま挿入されます。
書式指定の引数 Format に文字列を含めるには、Chr(34) を使って文字列を囲みます。文字コードではダブル クォーテーション (" ") は 34 になります。

この種類の目次に戻る↑ 索引へ↓ トップページに戻る


日数計算

 下記は、当年年初から本日までの、通算日数を、表示させるものです。
 DatePart関数というのは、強力ですね。初め、「日数@」のコードを書いたのですが、引数 y の「年間通算日」を使うと、「日数A」のように、一発で答えが出ました。

注:1月1日を起点にすると、1月1日の「年間通算日」は「0」になる点に注意して下さい。


Option Explicit

Dim 今年 As String
Dim 元日 As Date
Dim 今年の年間通算日 As Integer

'★★★★★★

Sub 日数@()
   今年 = DatePart("yyyy", Date)
   元日 = DateValue(今年 & "/1/1")
   今年の年間通算日 = DateDiff("d", 元日, Date) + 1
   MsgBox "今日は今年になって " & 今年の年間通算日 & " 日目です"
End Sub

'★★★★★★

Sub 日数A()
   今年の年間通算日 = DatePart("y", Date)
   MsgBox "今日は今年になって " & 今年の年間通算日 & " 日目です"
End Sub


 解説:
 DatePart 関数は、日付の指定した部分を含むバリアント型 (内部処理形式 Integer の Variant) の値を返します。
 DatePart 関数を使うと、日付を評価し、特定の時間間隔部分を取得できます。たとえば、週日や現在の時刻などを計算できます。
 構文:
 DatePart(interval, date[,firstdayofweek[, firstweekofyear]])

名前付き引数指定項目 内容
interval 必ず指定します。時間間隔の単位を表す文字列式を指定します。
date 必ず指定します。評価するバリアント型 (内部処理形式 Date の Variant) の値を指定します。
firstdayofweek 省略可能です。週の始まりの曜日を表す定数を指定します。省略すると、日曜日を指定したものとみなされます。
firstweekofyear 省略可能です。年度の第 1 週を表す定数を指定します。省略すると、1 月 1 日を含む週が第 1 週とみなされます。

 名前付き引数 interval の設定値は次のとおりです。
設定値 内容
yyyy 年 (年は y を4つ)
q 四半期
m
y 年間通算日(年数ではなく日数になることに注意)
d
w 週日
ww
h
n
s

 次の例は、ユーザーに日付の入力を求めた後、DatePart 関数を使って、その日付が四半期のうちのどの期に当たるかを調べます。

Dim TheDate As Date            ' 変数を宣言します。
Dim Msg    
TheDate = InputBox("日付を入力してください。")
Msg = "第 " & DatePart("q", TheDate) & " 四半期"
MsgBox Msg

 名前付き引数 firstdayofweek は、名前付き引数 interval に週 ("ww") を指定した場合の計算値に影響します。
 名前付き引数 firstdayofweek の設定値は次のとおりです。
定数 内容
vbUseSystem 0 NLS API の設定値を使います。
vbSunday 1 (既定値) 日曜
vbMonday 2 月曜
vbTuesday 3 火曜
vbWednesday 4 水曜
vbThursday 5 木曜
vbFriday 6 金曜
vbSaturday 7 土曜

 名前付き引数 firstweekofyear の設定値は次のとおりです。
定数 内容
vbUseSystem 0 NLS API の設定値を使います。
vbFirstJan1 1 (既定値) 1 月 1 日を含む週を年度の第 1 週として扱います。
vbFirstFourDays 2 7 日のうち少なくとも 4 日が新年度に含まれる週を年度の第 1 週として扱います。
vbFirstFullWeek 3 全体が新年度に含まれる最初の週を年度の第 1 週として扱います。

 名前付き引数 date に日付リテラルを指定した場合、指定した年が日付の固定部分となります。ただし、名前付き引数 date をダブル クォーテーション (" ") で囲み、年を省略すると、名前付き引数 date の式が評価されるたびにコードに現在の年が挿入されます。これを利用すると、異なる年度で使用できるコードを記述することが可能になります。

メモ Calendar プロパティの設定がグレゴリオ暦の場合、引数 date にはグレゴリオ暦で表される日付を指定する必要があります。Calendar プロパティの設定が回教暦の場合、引数 date には回教暦で表される日付を指定する必要があります。
 返される日付の部分は、現在のアラビア暦の時間間隔の単位で返されます。たとえば、現在のカレンダーが回教暦で、返される日付の部分が年の場合、その年の値は回教暦の年になります。


 DateDiff 関数は、2 つの指定した日付の時間間隔を表すバリアント型 (内部処理形式 Date の Variant) の値を指定します。
 DateDiff 関数を使うと、指定した時間単位で 2 つの日付の時間間隔を調べることができます。たとえば、2 つの日付の間の日数や、現在から年末までの週の数などを求めることができます。
 構文:
DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

名前付き引数 指定項目 内容
interval 必ず指定します。名前付き引数 date1 と date2 の間隔を計算するための、時間単位を表す文字列式を指定します。
date1, date2 必ず指定します。この名前付き引数はバリアント型 (内部処理形式 Date の Variant) のデータ形式で指定します。
間隔を計算する 2 つの日付を指定します。
firstdayofweek 省略可能です。週の始まりの曜日を表す定数を指定します。省略すると、日曜日を指定したものとみなされます。
firstweekofyear 省略可能です。年度の第 1 週を表す定数を指定します。省略すると、1 月 1 日を含む週が第 1 週とみなされます。

 次の例は、DateDiff 関数を使って、指定された日付までの日数を取得します。

Dim TheDate As Date            ' 変数を宣言します。
Dim Msg
TheDate = InputBox("日付を入力してください。")
Msg = "今日からの日数: " & DateDiff("d", Now, TheDate)
MsgBox Msg

 名前付き引数 interval の設定値は、DatePart関数と同じです。
 処理時間計測Aの例では、s (秒)としています。

 名前付き引数 date1 と date2 の間の日数を求めるには、年間通算日 ("y") か、日 ("d") のいずれかを指定します。
 名前付き引数 interval に週日 ("w") を指定すると、2 つの日付の間の週の数が計算されます。
 名前付き引数 date1 が月曜日の場合は、名前付き引数 date2 までの間の月曜日の数が返されます。このとき名前付き引数 date2 の月曜日は計算に入りますが、名前付き引数 date1 の月曜日は計算に入りません。
 一方、名前付き引数 interval に週 ("ww") を指定した場合は、2 つの日付の間の暦上の週の数が計算されます。つまり、名前付き引数 date1 と date2 の間の日曜日の数が返されます。名前付き引数 date2 に指定した日付が日曜日であれば、名前付き引数 date2 は計算に入りますが、名前付き引数 date1 は日曜日であっても計算に入りません。

 名前付き引数 date2 よりも時間的に後の日付を名前付き引数 date1 に指定すると、DateDiff 関数は負の数を返します。
 処理時間計測Aの例では、Abs を使って、整数にしています。


 名前付き引数 firstdayofweek の設定値は、DatePart関数と同じです。
 名前付き引数 firstdayofweek は、名前付き引数 interval に週 ("ww") を指定した場合の計算値に影響します。

 名前付き引数 firstweekofyear の設定値は、DatePart関数と同じです。


 名前付き引数 date1 または date2 に日付リテラルを指定した場合、指定した年が日付の固定部分となります。ただし、名前付き引数 date1 または date2 をダブル クォーテーション (" ") で囲み、年を省略すると、名前付き引数 date1 または date2 の式が評価されるたびにコードに現在の年が挿入されます。これを利用すると、異なる年度で使用できるコードを記述することが可能になります。

 12 月 31 日とその次の年の 1 月 1 日を比較すると、DateDiff 関数の年 ("yyyy") は、1 日後であっても、1 を返します。

メモ Calendar プロパティの設定がグレゴリオ暦の場合、引数 date1 および date2 にはグレゴリオ暦で表される日付を指定する必要があります。Calendar プロパティの設定が回教暦の場合、引数 date1 および date2 には回教暦で表される日付を指定する必要があります。

この種類の目次に戻る↑ 索引へ↓ トップページに戻る


稼動日数だけさかのぼった日付を求める

 Excel の関数に、開始日より稼動日数だけ前後させた日付に対応する値を返す関数 WorkDay が有ります。
 Excel の関数 WorkDay は、専用の様式で休日を指定する必要があります。

 手元に、Excel で作られた稼働日カレンダーが有ったので、これをそのまま使って、日付をさかのぼるユーザ関数を作ってみました。

このマクロをダウンロードできます。
WorkDayVBA00.xls

Option Explicit
Option Base 1

Function さかのぼり稼働日(起点日 As Date, さかのぼり稼働日数 As Integer) As Date
'Sub 稼働日()
   Dim カレンダーシート As Worksheet
   Dim カレンダ配列() As Variant
   Dim 最終行 As Integer
   Dim 処理列 As Integer
   Dim 処理行 As Integer
   Dim 起点日行 As Integer
   Dim 起点日列 As Integer
   Dim 年 As Integer
   Dim さかのぼり日列 As Integer
   Dim さかのぼり日行 As Integer
   
'   Dim 起点日 As Date
'   Dim さかのぼり稼働日数 As Integer
'   Dim さかのぼり稼働日 As Date
'
'   起点日 = "2014/4/15"
'   さかのぼり稼働日数 = 3
'Stop

   Set カレンダーシート = ThisWorkbook.Worksheets("工場カレンダー")
   最終行 = カレンダーシート.Cells(ActiveSheet.Rows.Count, 2).End(xlUp).Row
   カレンダ配列 = カレンダーシート.Range("A4").Resize(最終行 - 3, 33)
   
   '起点日の配列上の位置を知る
   年 = Format(起点日, "YYYY")
   
   起点日行 = Format(起点日, "MM") * 3
   
   If 年 = 2015 Then
      起点日行 = 起点日行 + 36
   End If
   
   起点日列 = Format(起点日, "DD") + 2
   
   '起点日が休日なら、さかのぼる
   Do While カレンダ配列(起点日行, 起点日列) <> ""
      If 起点日列 > 2 Then '同一月内
            起点日列 = 起点日列 - 1
      Else '月またがり
         起点日行 = 起点日行 - 3
         起点日列 = 34
      End If
   Loop
   
   さかのぼり日行 = 起点日行
   さかのぼり日列 = 起点日列
   
   Do While さかのぼり稼働日数 > 0
      If さかのぼり日行 <= 3 And さかのぼり日列 < 3 Then
         MsgBox "カレンダー範囲の外になります!"
         さかのぼり稼働日 = "#VALUE!"
         Exit Function
      End If
      If さかのぼり日列 > 2 Then '同一月内
         If カレンダ配列(さかのぼり日行, さかのぼり日列 - 1) = "" Then
            さかのぼり日列 = さかのぼり日列 - 1
            さかのぼり稼働日数 = さかのぼり稼働日数 - 1
         Else
            さかのぼり日列 = さかのぼり日列 - 1
         End If
      Else '月またがり
         さかのぼり日行 = さかのぼり日行 - 3
         さかのぼり日列 = 34
      End If
   Loop
   
   If さかのぼり日行 > 36 Then
      さかのぼり日行 = さかのぼり日行 - 36
      年 = 2015
   Else
      年 = 2014
   End If
   
   さかのぼり稼働日 = CDate(CStr(年) & "/" & CStr(さかのぼり日行 / 3) & "/" & CStr(さかのぼり日列 - 2))

'End Sub
End Function


この種類の目次に戻る↑ 索引へ↓ トップページに戻る


前後の月を求める

 変数で、月を移したいことがあります。
生産管理では、こんな書き方をしますね。
N-6(月)、N-5(月)、N-4(月)、N-3(月)、N-2(月)、N-1(月)、N(月)


Sub 月()

   Dim 今月 As Date
   Dim 間隔 As Integer
   Dim 文字列 As String
   
   今月 = "2011/12/05" '日付として認識される書式の文字列
   文字列 = "過去  月間隔" & vbNewLine
   For 間隔 = -11 To 0
      文字列 = 文字列 & DateAdd("m", 間隔, 今月) & " " & 間隔 & vbNewLine
   Next 間隔
   文字列 = 文字列 & String(4, vbNewLine)
   MsgBox 文字列
   
   文字列 = "未来  月間隔" & vbNewLine
   For 間隔 = 0 To 11
      文字列 = 文字列 & DateAdd("m", 間隔, 今月) & " " & 間隔 & vbNewLine
   Next 間隔
   文字列 = 文字列 & String(4, vbNewLine)
   MsgBox 文字列
   
   文字列 = "未来  月間隔" & vbNewLine
   For 間隔 = 0 To 11
      文字列 = 文字列 & Format(DateAdd("m", 間隔, 今月), "yyyymm") & " " & 間隔 & vbNewLine
   Next 間隔
   文字列 = 文字列 & String(4, vbNewLine)
   MsgBox 文字列
   
End Sub

 解説:
DateAdd 関数 は、指定された時間間隔を加算した日付をバリアント型 (内部処理形式 String の Variant) の値で返します。
 構文
 DateAdd(interval, number, date)
 DateAdd 関数の構文は、次の名前付き引数から構成されます。
指定項目 内容
interval 必ず指定します。追加する時間間隔を表す文字列式を指定します。
number 必ず指定します。追加する時間間隔の数を表す数式を指定します。
将来の日時を取得するには正の数を指定します。
過去の日時を取得するには負の数を指定します。
date 必ず指定します。時間間隔を追加する日付を表すバリアント型 (内部処理形式 Date の Variant) の値またはリテラル文字列を指定します。


 DateAdd 関数を使うと、ある日付に対して指定した時間間隔を、加えたり引いたりすることができます。たとえば、現在から 30 日後の日付や、現在から 45 分後の時刻などを計算できます。
 名前付き引数 date に日数を加えるには、年間通算日 ("y")、日 ("d")、週日 ("w") のいずれかを指定します。
 DateAdd 関数が無効な日付を返すことはありません。次の例では、31-Jan-95 の日付に 1 か月を加えています。
 DateAdd("m", 1, "31-Jan-95")
 この場合、31-Feb-95 ではなく、28-Feb-95 (1995/02/28)が返されます。名前付き引数 date が 31-Jan-96 であれば、1996 年は閏年なので 29-Feb-96 が返されます。
 計算の結果、日付が西暦 100 年以前になる場合 (名前付き引数 date に指定した年よりも大きな年を引いた場合) は、エラーが発生します。
 名前付き引数 number に指定した値が長整数型 (Long) でない場合は、最も近い整数値に丸められてから評価されます。

 DateAdd の戻り値の書式は、引数 date に指定する日付の書式ではなく、[コントロール パネル] の「地域と言語のオプション」の、「短い形式」で指定した、日付の形式になります。

 Calendar プロパティの設定がグレゴリオ暦の場合、引数 date にはグレゴリオ暦で表される日付を指定する必要があります。
 Calendar プロパティの設定が回教暦の場合、引数 date には回教暦で表される日付を指定する必要があります。
 月の名前として数値以外の名称が使われている場合、この名前は現在の Calendar プロパティの設定と一致する必要があります。
 月の名前と Calendar プロパティの設定を競合しないようにするには、短い日付形式の数値を使って日付を表すようにします。

 DateAdd 関数の使用例
 次の例は、ユーザーに日付とそれに加算する月数の入力を求めた後、DateAdd 関数を使って加算後の日付を表示します。

Dim FirstDate As Date    ' 変数を宣言します。
Dim IntervalType As String
Dim Number As Integer
Dim Msg
IntervalType = "m"        ' "m" によって追加する時間間隔として、月を指定します。
FirstDate = InputBox("日付を入力してください。")
Number = InputBox("加算する月数を入力してください。")
Msg = "新しい日付: " & DateAdd(IntervalType, Number, FirstDate)
MsgBox Msg

 String 関数 は、バリアント型 (内部処理形式 String の Variant) の値を返します。指定した文字コード (ASCII またはシフト JIS コード) の示す文字、または文字列の先頭文字を、指定した文字数だけ並べた文字列を返す文字列処理関数です。
 構文
 String(number, character)
 String 関数の構文は、次の名前付き引数から構成されます。
指定項目 内容
number 必ず指定します。長整数型 (Long) の値を指定します。
文字をいくつ並べるのかを指定します。
名前付き引数 number に Null 値が含まれる場合は、Null 値を返します。
character 必ず指定します。バリアント型 (Variant) の値を指定します。
文字の文字コード、または文字列式を指定します。この文字列の先頭文字を number 回繰り返したものを返します。
名前付き引数 characterNull 値が含まれる場合は、Null 値を返します。

 名前付き引数 character に ASCII でもシフト JIS でもない無効な数値を指定すると、0 として処理されます。

 次の例は、String 関数を使って、指定した文字を指定した数だけ並べた文字列を返します。

Dim MyString
MyString = String(5, "*")            ' "*****" を返します。
MyString = String(5, 42)            ' "*****" を返します。
MyString = String(10, "ABC")        ' "AAAAAAAAAA" を返します。

この種類の目次に戻る↑ 索引へ↓ トップページに戻る


文字列から日付けを求める

 VBA には、日付型 (Date) の変数が有ります。日付型 (Date) の変数を使うと、日数計算などが、簡単にできます。
 しかし、私たちが、日付けに見える値でも、プログラムが日付と認識できないことがあります。
 ここでは、文字列型 (String) のデータを、日付型 (Date) に変換する関数(CDate)と、
日付型 (Date) のデータを、文字列型 (String) のデータに変換する関数(Format)の使い方を、紹介します。

Sub 文字列型変数と日付型変数()

   Dim 英字 As String
   Dim 数字 As String
   Dim 数字スラッシュ付 As String

   英字 = "JAN.2014"
   数字 = "20140101"

   'CDateの引数は、日付書式のみ。IsDate でチェックする
   MsgBox 英字 & "は、" & IsDate(英字) & vbNewLine _
         & 数字 & "は、" & IsDate(数字)

   数字スラッシュ付 = Left(数字, 4) & "/" & Mid(数字, 5, 2) & "/" & Right(数字, 2)

   MsgBox 英字 & " は、ピリオドを外して " _
      & "CDate すると " & CDate(Replace(英字, ".", " ")) & vbNewLine _
      & 数字 & " は、スラッシュを挿入して " _
      & "CDate すると " & CDate(数字スラッシュ付) & vbNewLine _
      & "になります。"

   MsgBox 数字 & " の 1日前は、スラッシュを挿入して " _
      & "CDate して、DateAdd で一日引いて、Format で文字列に戻すと" & vbNewLine _
      & Format(DateAdd("d", -1, CDate(数字スラッシュ付)), "yyyymmdd") & vbNewLine _
      & "に、できます。"

End Sub

 解説:
IsDate 関数 は、式を日付に変換できるかどうかを調べ、結果をブール型 (Boolean) で返します。
 構文   IsDate(expression)
 引数 expression は必ず指定します。引数 expression には、日付や時刻として認識できる 日付式 または 文字列式 を含む バリアント型 (Variant) の式を指定します。

 IsDate 関数は、指定した式が日付である場合、またはその式が有効な日付として認識できる場合は、真 (True) を返します。
変換できない場合は、偽 (False) を返します。
 Microsoft Windows で有効な日付の範囲は、西暦 100 年 1 月 1 日から西暦 9999 年 12 月 31 日までです。
この範囲は、オペレーティング システムによって異なります。

CDate 関数 は、任意の有効な日付式(expression)を、日付型 (Date)のデータに変換します。
 構文   CDate(expression)

 日付式とは、日付として解釈可能な式。
日付式には、日付リテラル、日付と解釈できる数値、日付と解釈できる文字列、関数によって返される日付が含まれます。

 フィロの村note の、
http://note.phyllo.net/?eid=1106086
で、どのような形式まで日付・時刻として扱われるか動作検証した結果を、公開いただいています。

 日付は、実数の一部として格納されます。整数部の値は日付を表し、小数部の値は時刻を表します。負の整数を指定すると、1899 年 12 月 30 日より前の日付を表します。

 日付型 (Date)は、実数で日付と時刻を格納するためのデータ型。
日付型 (Date) の変数は、64 ビット (8 バイト) の数値として格納されます。小数点の左側の値は日付を表し、右側の数値は時刻を表します。

この種類の目次に戻る↑ 索引へ↓ トップページに戻る


年月の文字列から前月を求める

 文字列で与えられた年月を数値にして引き算して、前月の文字列を生成します。

 文字列型から日付型に変換して、DateAdd を使うこともできます。



Sub テスト()
   Dim 年月 As String

   年月 = "201201"
   If Val(Right(年月, 2)) = 1 Then
      年月 = CStr(Val(Left(年月, 4) - 1)) & "12"
   Else
      年月 = Left(年月, 4) & Right("0" & CStr(Val(Right(年月, 2)) - 1), 2)
   End If
   MsgBox 年月
End Sub

この種類の目次に戻る↑ 索引へ↓ トップページに戻る

ホームページのトップに戻る