VBA：日付を配列に入れ別セルに転記するとデータ型が変わる

データを別シートに転記するVBAコードを書いていて気付きました。

日付データをバリアント型の配列に入れて、再度書き出すと 21/2/2005 のように表示され、さらに日付ではなく、文字列になってしまいました。

具体的には下記のような内容です。Sheet1 の A1:A10 に 2005/2/21 のような日付が入力されています。

1.　セル範囲のデータをバリアント型の配列に格納
2.　1.を別のセルへ一括転記を行う

Sub TestMacro()

　　Dim Buf As Variant
　　With Sheets("Sheet1").Range("A1:A10")
　　　　Buf = .Value　　　　　'・・・(1)
　　　　.Offset(0, 3) = Buf 　'・・・(2)
　　End With

End Sub

ウォッチ式で変数を確認すると(1)および(2)の時点では #05/02/21# Variant/Date型 と正しく日付として扱われているようです。しかし、転記されたセルを見てみると、「文字列で 21/2/2005 」となっているのです。

2005/2/21 が返されるものと思うのですが、なぜ、このような現象が起こるのでしょうか？何かとんでもない勘違いをしているのでしょうか？

テスト環境は Win98SE ＋ EXCEL2002 です。よろしくお願いします。

No.5 ベストアンサー 回答者： papayuka 回答日時： 2005/02/23 07:59

#3です。

#4の補足は、Win95 + Excel97 の環境では下記の通りとなりました。

１．新規シートでそのまま実行する。

　　全て 2005/2/25（セル書式：日付）

２．新規シートで .NumberFormat = "yyyy/m/d" を全てコメントアウトして実行

　　直接　2005/2/25（セル書式：日付）
　　Variant型変数　2005/2/25（セル書式：日付）
　　DATE型配列　38408（セル書式：標準）
　　Variant型配列　2/25/05（セル書式：日付）

最初のレス（#3）は WinXP Home + Excel2000 の環境で書き、問題なかったので
Excel2002というのがポイントなのかも。

この回答への補足

お礼欄が2つ無いので、補足欄ですが、お礼申し上げます。

会社の Windows98SE + EXCEL2000 の環境では、本質問の現象は発生しませんでした。したがって、papayuka さんのご指摘どおり、EXCEL2002の問題のようです。

【Microsoft サポートオンライン】より抜粋
XL2002: 1 つの範囲からプログラムでほかにデータを移動すると、日付書式が失われます。
http://support.microsoft.com/default.aspx?scid=k …

上記のMSサイトで、EXCEL2002の問題であることを確認しました。OFFICEのサービスパック3で修正されているとのことでしたので、本質問は解決です。

日付が文字列にデータ型が変わるのではなく、「日付書式が失われる」トラブルでした。余談ですが、EXCEL97でも同様のトラブルがあるみたいです。#5のご回答で、

＞２．新規シートで .NumberFormat = "yyyy/m/d" を全てコメントアウトして実行
＞　　Variant型配列　2/25/05（セル書式：日付）

というコードの実行結果をいただきましたが、本来は、転記先のセル表示形式が「標準」であっても、2005/2/1と日本語表記の日付となるはずです。papayuka さんの結果を見ると、これが原因みたいですね。

ありがとうございました。

補足日時：2005/02/24 14:50

この回答へのお礼

papayuka さま

何度もご回答いただき、ありがとうございます。また、貴重な情報のご提供感謝いたします。

＞最初のレス（#3）は WinXP Home + Excel2000 の環境で書き、問題なかったので
Excel2002というのがポイントなのかも。

早速、会社のPCで試してみます。

お礼日時：2005/02/24 01:00

No.4 回答者： papayuka 回答日時： 2005/02/22 18:39

#3です。

参考ですが、 Win95 + Excel97 ではＯＫでしたよ。

この回答への補足

自宅PC(WinXPpro+Excel2002)で以下のコードを実行すると、やはりダメですね。バリアント型の配列に代入し、一括転記すると日付が英語表記になり、文字列となります。バリアント型の変数へ代入した場合は、大丈夫でした。混乱してます。

Sub TestMacro2()

　　Dim myDate As Date
　　Dim VarDate As Variant
　　Dim datBuf(0 To 9) As Date
　　Dim varBuf(0 To 9) As Variant

　　myDate = #2/25/2005#

　　'直接転記
　　Range("A1").Value = "直接"
　　With Range("A2:A10")
　　　　.NumberFormat = "yyyy/m/d"
　　　　.Value = myDate
　　End With

　　'Variant型変数経由で転記
　　Range("B1").Value = "Variant型 変数"
　　VarDate = myDate
　　With Range("B2:B10")
　　　　.NumberFormat = "yyyy/m/d"
　　　　.Value = myDate
　　End With

　　'DATE型配列に代入し転記
　　Range("C1").Value = "DATE型 配列"
　　For i = 0 To 9
　　　　datBuf(i) = myDate
　　Next i
　　With Range("C2:C10")
　　　　.NumberFormat = "yyyy/m/d"
　　　　.Value = datBuf
　　End With

　　'Variant型配列に代入し転記
　　Range("D1").Value = "Variant型 配列"
　　For i = 0 To 9
　　　　varBuf(i) = myDate
　　Next i
　　With Range("D2:D10")
　　　　.NumberFormat = "yyyy/m/d"
　　　　.Value = varBuf
　　End With

　　Columns("A:D").AutoFit
　　
End Sub

補足日時：2005/02/23 00:32

この回答へのお礼

(??) そうですか、、。ということは、OSの違いではなく、PCの違いなんでしょうか？

お礼日時：2005/02/22 19:59

No.3 回答者： papayuka 回答日時： 2005/02/21 20:27

#1さんの回答で良いと思うのですが、、、

元のセルと転記するセルのフォーマットを調べましたか？

Sub TestMacro()
　　Dim Buf As Variant
　　With Sheets("Sheet1").Range("A1:A10")
　　　　Debug.Print .NumberFormat
　　　　Buf = .Value
'　　　　.Offset(0, 3).NumberFormat = .NumberFormat　'(※)
　　　　Debug.Print .Offset(0, 3).NumberFormat
　　　　.Offset(0, 3) = Buf
　　End With
End Sub

C列を文字列にして実行した場合、イミディエイトウィンドウには

　m/d/yyyy
　@

と返され、セルには 2/1/2005 のように文字列で返されました。
※を有効にした場合は

　m/d/yyyy
　m/d/yyyy

となり、セルも 2005/2/1 となりましたよ。

この回答へのお礼

ご回答ありがとうございます。

＞元のセルと転記するセルのフォーマットを調べましたか？

はい、調べました。試した内容は

1.　セルの表示形式を標準または日付に手動設定してからマクロを実行してみる
2.　マクロで、転記先セルにNumberFormatでyyyy/m/dに設定してから転記する

です。結果はともにダメでした。ご教示いただいたコードも結果変わらずで、表示形式はyyyy/m/d になっていましたが、値はやはり2/1/2005 文字列でした。英語圏の日付表示形式ですから、日本語版EXCELで文字列として認識されてしまうのは止むを得ないとしても、なぜd/m/yyyy形式で転記されるのか？が疑問です。

No.2の方の補足欄でご報告いたしたましたが、別のPC（Win2000 + Excel2000）では、ちゃんと日付（yyyy/m/d）でセルに転記されました。当方で調べた範囲では、

Windows2K、XP ---＞ OK
Windows9X、ME ---＞ NG

でしたので、OSの問題なのかなと思うのですが、これといった確証がありません。

お礼日時：2005/02/22 17:49

No.2 回答者： hana-hana3 回答日時： 2005/02/21 19:03

コントロールパネルの地域設定の中の「日付」の書式に影響される場合があります。

設定はどのようになっていますか？

参考URL：http://www.bsl-jp.com/support/rakuda/kyoutuu/sup …

この回答への補足

ご回答ありがとうございます。

コントロールパネルの[地域]-[日付]タブで

短い形式： yy/MM/dd

となっていました。参考URL先に従い、

短い形式： yyyy/MM/dd

としてみましたが、結果変わらずでした。これから、OSを再起動してみます(TT)

補足日時：2005/02/21 19:10

この回答へのお礼

OSを再起動してみましたが、結果変わらずでした。

ところがです。Win2000 + Excel2000 の別のPCで試したら、ちゃんと日付で返されました。

ということは、、Windows98の問題なのでしょうか？
Windows Updateはちゃんと行っています。

お礼日時：2005/02/21 19:22

No.1 回答者： popesyu 回答日時： 2005/02/21 18:38

Buf = .Value '・・・(1)

.Offset(0, 3).NumberFormatLocal = "yyyy/mm/dd"
.Offset(0, 3) = Buf '・・・(2)

書式が文字列に先に定義されているからでしょう。データ転記の前に書式を日付にしてしまえばよいのではないかと。

この回答へのお礼

早速ありがとうございます。

アドバイス頂いたとおり、やってみましたが、結果は変わらずでした。

結果：日付型 2005/2/21 --＞ 文字列型 21/2/2005

現在作成しているデータ転記プロシージャは、データ型を問わず対応できる汎用的なものにしたいと考えています。つまり、どのフィールドにどの型のデータが代入されるかは不明ですから、転記された文字列 21/2/2005 を後から日付データに変換するような方法をとれません。

EXCEL（VBA）の仕様なのでしょうか？

お礼日時：2005/02/21 18:58