VBA100本ノック 7本目:日付データの扱い
日付データに関する問題です。
そもそも日付とはどのようなデータなのかも考えてみましょう。
ツイートでの見やすさを考慮して、ブック・シート指定等を適宜省略しています。
マクロVBAを初心者向けの基本から上級者向けの高度な内容までサンプルコードを掲載し解説しています。エクセル関数・機能・基本操作の入門解説からマクロVBAまでエクセル全般を網羅しています。
出題
出題ツイートへのリンク
#VBA100本ノック 7本目
A列は文字列データ(表示形式が文字列)で日付が入っています。
日付とみなされる場合はB列に月末日付をmmddの形式で出力してください。
日付け以外の場合は空欄にしてください。
例.B2は「0930」と出力する。
※何をもって日付とみなすかも含めて考えてください。
サンプルファイルです。
https://excel-ubara.com/vba100sample/VBA100_07.xlsm
https://excel-ubara.com/vba100sample/VBA100_07.zip
A列は文字列データ(表示形式が文字列)で日付が入っています。
日付とみなされる場合はB列に月末日付をmmddの形式で出力してください。
日付け以外の場合は空欄にしてください。
例.B2は「0930」と出力する。
※何をもって日付とみなすかも含めて考えてください。
https://excel-ubara.com/vba100sample/VBA100_07.xlsm
https://excel-ubara.com/vba100sample/VBA100_07.zip
VBA作成タイム
この下に頂いた回答へのリンクと解説を掲載しています。
途中まででも良いので、できるだけ自分でVBAを書いてみましょう。
他の人の回答および解説を見て、書いたVBAを見直してみましょう。
頂いた回答
解説
#VBA100本ノック 7本目 解答
日付の判定はIsDate関数を使います。
ただし人が日付と認識するものとは少しずれがあります。
どのような経緯で発生したデータかによって注意して使ってください。
添付ではIsDateでFalse判定される中から"."区切りだけを救っています。
シート指定は省略しています。
末日の算出はDateSerial関数を使うと簡単です。
問題文は「mmddの形式で出力」なので、表示形式ではなくデータとしてmmddで出力しました。
元号の合字はIsDateがFalseになります。
これを救うには1文字ずつ変換するしかありません。
※何をもって日付とするかは適宜変わってくると思います。
日付の判定及び末日算出の詳細については、記事の補足をお読みください。
元号の合字を変換するFunctionのVBAサンプルも掲載しておきました。
日付の判定はIsDate関数を使います。
ただし人が日付と認識するものとは少しずれがあります。
どのような経緯で発生したデータかによって注意して使ってください。
添付ではIsDateでFalse判定される中から"."区切りだけを救っています。
シート指定は省略しています。
Sub VBA100_07()
Dim i As Long
Dim d As Variant
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
d = Replace(Cells(i, 1).Value, ".", "/")
If IsDate(d) Then
d = CDate(d)
Cells(i, 2) = Format(DateSerial(Year(d), Month(d) + 1, 0), "'mmdd")
Else
Cells(i, 2) = ""
End If
Next
End Sub
問題文は「mmddの形式で出力」なので、表示形式ではなくデータとしてmmddで出力しました。
元号の合字はIsDateがFalseになります。
これを救うには1文字ずつ変換するしかありません。
※何をもって日付とするかは適宜変わってくると思います。
元号の合字を変換するFunctionのVBAサンプルも掲載しておきました。
補足
コンピューター処理において日付はかなり厄介なものです。
特に文字列として日付が入っている場合は、それが日付なのかどうか判断に困る場合も多々あります。
今回の主題としては、IsDate関数とCDate関数の挙動について把握することにあります。
今回の解答としては、IsDateで日付判定されるものはそのまま信用して、
それ以外に、人間が見た時に日付とみなされそうなものを救って見ようという主旨で作成しました。
エクセルだけで考えたら、日付を正しく入力しましょう。
という事になりますが、VBAが扱うデータはエクセルで入力したものとは限りません。
他システムからの出力データやWEBからのコピーデータを扱う事も多いので、日付けデータの形式については理解しておきましょう。
日付けの判定について
特に文字列として日付が入っている場合は、それが日付なのかどうか判断に困る場合も多々あります。
今回の主題としては、IsDate関数とCDate関数の挙動について把握することにあります。
それ以外に、人間が見た時に日付とみなされそうなものを救って見ようという主旨で作成しました。
という事になりますが、VBAが扱うデータはエクセルで入力したものとは限りません。
他システムからの出力データやWEBからのコピーデータを扱う事も多いので、日付けデータの形式については理解しておきましょう。
日付かどうかを判定する関数としてVBAにはIsDate関数があります。
これは、Date型(つまりシリアル値)に変換可能かの判定になります。
あくまでVBAがDate型に変換できるかどうかであり、人の見た目で日付とみなすかどうかとは関係ありません。
この点に注意して使ってください。
IsDate関数でTrueならCDateでシリアル値に変換できます。
これは、Date型(つまりシリアル値)に変換可能かの判定になります。
あくまでVBAがDate型に変換できるかどうかであり、人の見た目で日付とみなすかどうかとは関係ありません。
この点に注意して使ってください。
日付 | IsDate |
2020/9/1 | True |
2020/9/02 | True |
10/3 | True |
20 10 4 | True |
2020.10.5 | False |
2020-10-6 | True |
令和元年10月 | True |
令1年11月2日 | True |
令元年11月03日 | True |
㋿元年11月04日 | False |
IsDate関数でTrueならCDateでシリアル値に変換できます。
月末日の算出
DateSerial(Year, Month, Day)
DateSerial関数のDayに0を指定すると、前月末日になります。
末日より大きい数値を指定した場合は、超えた日数分の翌月の日付けになります。
Monthに0と指定すれば、前年の12月、13と指定すれば翌年の1月
DateSerial関数のDayに0を指定すると、前月末日になります。
末日より大きい数値を指定した場合は、超えた日数分の翌月の日付けになります。
Monthに0と指定すれば、前年の12月、13と指定すれば翌年の1月
Format関数
Format関数の書式指定文字は、シートとは若干の違いがあるもののほぼ同じなので基本的な指定については覚えおきましょう。
そこで、文字列にするにあたり、セルの表示形式を"@"で文字列にしておいてももちろん良いのですが、
上記のVBAでは、
"'mmdd"
これで先頭に"'"をつけて文字列としてセルに入れています。
・Format関数 ・表示書式指定文字 ・Format$関数 ・使用例
この関数の戻り値をそのままセルに出力すると、数値としてセルに入るので先頭の0が消えてしまいます。そこで、文字列にするにあたり、セルの表示形式を"@"で文字列にしておいてももちろん良いのですが、
上記のVBAでは、
"'mmdd"
これで先頭に"'"をつけて文字列としてセルに入れています。
合字について
これらの文字については、IsDateは元号として認識しません。
またVBAで固定文字として入れようとした時、
これは、?となってしまいます。
これは文字コードの問題です。
以下のFunctionは、この元号の合字をIsDateが認識できる元号に変換するものです。
Function EditDate(ByVal d As String) As String
Dim g1, g2
g1 = Array(&H337E, &H337D, &H337C, &H337B, &H32FF)
g2 = Array("明治", "大正", "昭和", "平成", "令和")
Dim i As Long
For i = LBound(g1) To UBound(g1)
If InStr(d, ChrW(g1(i))) > 0 Then
d = Replace(d, ChrW(g1(i)), g2(i))
Exit For
End If
Next
EditDate = d
End Function
令和以外はVBAに直接合字を記入できますが、令和に合わせて文字コードで記載しました。
サイト内関連ページ
IsDate関数|VBA関数
・IsDate関数 ・IsDate関数の使用例 ・Is○○関数一覧
CDate関数|VBA関数・CDate関数 ・CDate関数の使用例 ・日付型 (Date)について ・データ型変換関数一覧
DateSerial関数|VBA関数DateSerial関数は、引数に指定した年、月、日に対応する日付を返す関数です、バリアント型(内部処理形式DateのVariant)の値を返します。DateSerial関数 DateSerial(year,month,day) year 必ず指定します。
DateValue関数|VBA関数DateValue関数は、日付を表す文字列式を日付に変換します、バリアント型(内部処理形式DateのVariant)の値を返します。DateValue関数 DateValue(date) date 必ず指定します。引数dateには、通常、100年1月1日から9999年12月31日までの範囲の日付を表す文字列式を指定…
Day関数|VBA関数Day関数は、日付から何日かを表す日の値を返します、バリアント型(内部処理形式IntegerのVariant)の値を返します。Day関数 Day(date) date 必ず指定します。日付を表すバリアント型(Variant)の値、数式、文字列式、またはこれらを組み合わせた値を指定します。
Month関数|VBA関数Month関数は、日付から何月かを表す月の値を返します、バリアント型(内部処理形式IntegerのVariant)の値を返します。Month関数 Month(date) date 必ず指定します。日付を表すバリアント型(Variant)の値、数式、文字列式、またはこれらを組み合わせた値を指定します。
Year関数|VBA関数Year関数は、日付から年の値を返します、バリアント型(内部処理形式IntegerのVariant)の値を返します。Year関数 Year(date) date 必ず指定します。日付を表すバリアント型(Variant)の値、数式、文字列式、またはこれらを組み合わせた値を指定します。
同じテーマ「VBA100本ノック」の記事
・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
5本目:セルの計算・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
6本目:セルに計算式・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
7本目:日付データの扱い8本目:点数の合否判定
・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
9本目:フィルターコピー・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
10本目:行の削除・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
11本目:セル結合の警告・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
12本目:セル結合を解除・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
13本目:文字列の部分フォント・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
14本目:社外秘シート削除・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
新着記事NEW ・・・新着記事一覧を見る
エクセルが起動しない、Excelが立ち上がらない|エクセル雑感(2024-04-11)
ブール型(Boolean)のis変数・フラグについて|VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する|VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得(End,Rows.Count)|VBA入門
2.ひらがな⇔カタカナの変換|エクセル基本操作
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.変数宣言のDimとデータ型|VBA入門
5.繰り返し処理(For Next)|VBA入門
6.RangeとCellsの使い方|VBA入門
7.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
8.セルのクリア(Clear,ClearContents)|VBA入門
9.並べ替え(Sort)|VBA入門
10.メッセージボックス(MsgBox関数)|VBA入門
- ホーム
- マクロVBA入門編
- VBA100本ノック
- 7本目:日付データの扱い
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。