VBAの書式で表示形式を日付からシリアル値(標準)に変換する

exce-vba文字列

日付の表示変換をVBAでやろうと思ってエラーになったり、変換されなかったりして困った経験ありませんか?

エクセルの文字列や数値・通過・日付などの表示形式をVBAで変換するには、一般的にはFormat関数を利用するように案内されているからです。

実際にこの方法で西暦を和暦に変更したり日付表示を取り扱う時にはとても便利なのですが、日付表示(2021/2/9)をシリアル値(標準表示形式)44236に変換するには使えませんので、Format関数以外を使う事になります。

スポンサーリンク

日付表示をシリアル値(標準)に変換する

先ほどお話しした通り、Format関数が使えないどころか日付表示をシリアル値にするには、セルの値が日付の時とユーザーフォームに配置した文字(キャプション)でも扱いが異なるので、それぞれに合ったVBAコードを使い分けましょう。

NumberFormatLocalで指定したセルの値の日付をシリアル値にする

NumberFormatLocalプロパティを使う事により、ワークシート上で選択したセルの値を【セルの書式設定】➡【表示形式】へ進み、【日付】となっている表示形式を【標準】にすればシリアル値に変換できるようにする工程をVBAで表しています。

セルの書式設定
画像はユーザー定義ですが、この画面の事です

使い方としては、例えば「セルA1の書式を標準にする」となるのでこのようになります。

日付をシリアル値にする

Private Sub CommandButton1_Click()
Range(“A1”).NumberFormatLocal = “G/標準”
End Sub

これは、ユーザーフォーム1に配置したコマンドボタン1をクリックしたら、セルA1の値の表示形式を標準(シリアル値)にするというVBAコードです。

もちろんRangeの代わりにCellsも使えますし、シート名を付け加えてSheets(“シート名”).Range(“A1”)とすることも可能です。

さらに範囲指定してRange(“A1:C10”)というのも問題なく動きます。

これで日付をシリアル値にできたと喜んでいたのですが、このNumberFormatLocalプロパティを使う方法では日付が入力されているセルの書式変更なので、別のセルへコピーしてシリアル値にしたり、ユーザーフォームに配置したコマンドボタンやラベルの文字(キャプション)を変更することができません。

CDbl関数とDate関数を組み合わせてシリアル値で表示する

上記のようにセルの書式自体を変更したくない時、ユーザーフォームなどのキャプションを指示したい時には、CDbl関数の引数としてDate関数を当てると、日付をシリアル値に変換して目的のセルへ記入したりフォームのキャプションを書き換えることができます。

日付をシリアル値にしてデータをして扱う

Private Sub CommandButton1_Click()
Range(“A1”).Value = CDbl(Date)
End Sub

VBA解説
  1. コマンドボタン1がクリックされたら
  2. セルA1に現在の日付をシリアル値にして記載する
  3. マクロ記録終了

この方法では、Date関数を扱う時と同じルールが使えるので、(Date+1)とすれば、明日の日付のシリアル値が記載され、月末や1日も扱う事ができます。

さらに、(Date+変数)を使ってオリジナルカレンダーの日付を自動取得して、祝日まで扱えるようになりますので、個人的には便利な関数だと思って使っています。

エクセルでは日付を扱う事が多くなるのでぜひ活用してみてはいかがですか?

スポンサーリンク


文字列日付・時刻
スポンサーリンク
参考になりましたらシェア宜しくお願い致します

山頂テラスデッキからの三段紅葉(2019年11月)

フリーパスって助成金つぎ込んでいいの?

コロナ禍のGo To トラベル以外にも助成金だらけでうらやましいね。

安くなったフリーパスで遊びに行きたいけど、給与も減って余裕ないよね。

Excel VBA