VBAで日付のシリアル値を「yyyy/mm/dd」形式で表示する方法

VBAで日付のシリアル値を特定のフォーマット、例えば「yyyy/mm/dd」に変換して表示する方法について解説します。特に、シリアル値を読み込み、Excelのセルに「yyyy/mm/dd」形式で表示させる方法や、他のExcelファイルに値を貼り付けた際にそのフォーマットを保持する方法について触れます。

1. シリアル値を「yyyy/mm/dd」で表示する方法

VBAで日付のシリアル値を「yyyy/mm/dd」形式で表示するためには、Format関数を使用します。以下のコード例では、セルA1に格納されたシリアル値を「yyyy/mm/dd」形式でセルB1に表示する方法を紹介します。

Sub FormatDate() 
    ' セルA1にシリアル値をセット
    Cells(1, 1).Value = CDate(45839) 
    ' セルB1に「yyyy/mm/dd」形式で日付を表示
    Cells(1, 2).Value = Format(Cells(1, 1).Value, "yyyy/mm/dd") 
End Sub

このコードは、セルA1にシリアル値を格納し、セルB1にそのシリアル値を「yyyy/mm/dd」形式で表示します。ここで注意すべきポイントは、Format関数を使って日付を希望の形式に変換している点です。

2. 他のExcelファイルに値を貼り付ける際の問題点

質問者のように、シリアル値を他のExcelファイルに値として貼り付けた場合、貼り付け先のセルにシリアル値そのものが表示され、期待する「yyyy/mm/dd」の形式にはなりません。これは、貼り付け先のセルが標準の表示形式のままであるためです。

この問題を回避するためには、貼り付け時にセルの書式設定を調整するか、コピーする前に値としてのみ貼り付ける必要があります。具体的には、以下の方法を使用することで、正しい形式で値を貼り付けることができます。

Sub CopyWithFormat() 
    Dim ws As Worksheet 
    Set ws = ThisWorkbook.Sheets("Sheet1") 
    ws.Range("B1").Copy 
    Workbooks("OtherWorkbook.xlsx").Sheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues 
End Sub

このコードでは、セルB1の値を他のワークブックに値として貼り付け、フォーマットを維持するために適切な設定を使用しています。

3. VBAコードでのフォーマットの保持

もし、コピー後も希望の「yyyy/mm/dd」形式を維持したい場合、貼り付け先のワークブックでセルの書式を手動で設定することができます。もしくは、VBAで貼り付け後にセルの書式設定を行うことができます。以下のコードは、値を貼り付けた後に書式を指定して、希望する形式を確実に適用する方法です。

Sub CopyAndFormat() 
    Dim ws As Worksheet 
    Set ws = ThisWorkbook.Sheets("Sheet1") 
    ws.Range("B1").Copy 
    Workbooks("OtherWorkbook.xlsx").Sheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues 
    ' 貼り付け後に書式設定
    Workbooks("OtherWorkbook.xlsx").Sheets("Sheet1").Range("A1").NumberFormat = "yyyy/mm/dd" 
End Sub

この方法では、データを正しい形式で貼り付けた後、指定した書式「yyyy/mm/dd」をセルA1に適用しています。

4. まとめ

シリアル値の日付をVBAで「yyyy/mm/dd」形式で表示するためには、Format関数を使用することが重要です。また、他のExcelファイルに値を貼り付ける際には、貼り付けの方法や書式設定に注意を払い、適切に日付形式を維持することが必要です。これにより、希望通りの表示形式を保ちながら作業を進めることができます。