エクセルへのデータの貼付時に書式が反映されない！

質問

お世話になります。

VBでエクセルにデータを貼り付ける処理があるのですが、
エクセルの書式設定(数値で小数点第3位まで表示)が反映されず、そのままの値が文字列として表示されてしまいます。

そのセルをダブルクリックすると書式が反映されます。

貼り付ける時はパフォーマンスを考慮して、下記のように一括で貼り付
けるようにしています。
「m_xlSheet.Range("A" + CStr(lngRows) + ":U" + CStr(lngRows)).Value = strValue」
何かよい方法をご存知でしたら、教えて下さい。

環境：VB6(SP6)、エクセル2000

回答No.2

KenKen_SP · ベストアンサー

こんにちは。KenKen_SP です。

> m_xlSheet.Range("A" + CStr(lngRows) + ":U" + CStr(lngRows)).Value = strValue

変数 strValue が String型配列になっているからですね。恐らく、VB の自動
キャスト機能が原因です。

オートメーションのコードを書くのは面倒なので、Excel VBA でサンプルを
書いておきます。変数の型の違いでセルへ転記がどのように違うかを確認して
下さい。

Sub Sample1()

　　' 文字列として数字が転記される例

　　Dim Buf() As String '<-- 文字列型変数が原因

　　ReDim Buf(1 To 5, 1 To 5)
　　For i = 1 To 5
　　　　For j = 1 To 5
　　　　　　' 数値が文字列にキャストされる
　　　　　　Buf(i, j) = 12345
　　　　Next j
　　Next i
　　Range("A1:E5").Value = Buf

End Sub

ではどうするかといえば、配列の型を Variant にしてしまうのが一番
簡単です。

Sub Sample2()

　　' 対応策

　　Dim Buf() As Variant '<-- バリアント型にする

　　ReDim Buf(1 To 5, 1 To 5)
　　For i = 1 To 5
　　　　For j = 1 To 5
　　　　　　Buf(i, j) = 12345
　　　　Next j
　　Next i
　　Range("A1:E5").Value = Buf

End Sub

お礼

配列の型を Variant型にすることで、適切に書式が適用されました。
お忙しい中、ありがとうございました。

回答No.3

KenKen_SP

#2 です。もう少しサンプルを提示しておきます。

Sub Sample3()

　　Dim i As Long
　　Dim j As Long

　　'Dim Buf() As Variant '<-- バリアント型にする
　　'または数値しか代入されないなら Double 型等でも OK
　　Dim Buf() As Double

　　ReDim Buf(1 To 5, 1 To 5)
　　For i = 1 To 5
　　　　For j = 1 To 5
　　　　　　Buf(i, j) = 1.333
　　　　Next j
　　Next i
　　With Range("A1:E5")
　　　　.NumberFormat = "0.000"
　　　　.Value = Buf
　　End With

End Sub

Sub Sample4()

　　Dim strTmp As String

　　' VB では数値が String 型にキャストされて代入される
　　' strTmp = Cstr(1.333) と同意. つまりこの段階では、
　　' strTmp = "1.333" となっている
　　strTmp = 1.333
　　' しかし、配列でない「通常変数の場合」はセルへの代入時に
　　' Excel が「型の判断を再度行う」ため、再び数値として評価
　　' できるものは数値となってしまう....
　　Range("A1").Value = strTmp

　　' 配列の一括転記時にはこの機能が働かない

End Sub

お礼

回答して頂きありがとうございます。
なるほど、配列の一括転記時はExcelで自動変換されないんですね。
いろいろ試してみます。

回答No.1

tetnon2000

私の環境（2003)ではできるんですが。
例えば、strValueが1.333ならセルに1.333と表示されれば問題ないわけですよね。
ツールー＞オプションー＞設定の編集等でなにか特別な設定を行っていないか確認されは同でしょうか。
最終手段は新たしいブックを作成し、そちらに、必要なワークシートの内容をコピー貼り付け、マクロもコピー貼り付けしてみたらどうでしょう。
まあバージョンが異なるのでなんともいえないですが。ご参考になれば幸いです。

補足

「ツールー＞オプションー　・・・」はデフォルトのままで、特にいじっておりません。

また、新しいブックに形式を選択して貼り付けで、値のみにしても同じように表示されてしまいます。