【VBA】数値の表示形式を取得し別セルへ設定する

こんにちは、Ryoです。
VBAで処理する際にデータを配列で取り込み、別のセルなどにデータを書き込むことがあると思いますが、その際に数値の表示形式（例えば0.00）は書き込み先のセルに設定された様式に従う形になりますから意図した表示にはならないこともあります。

書き込む際に指定すれば済むのですが、データを取り込む際に併せて表示形式（書式）も取得しておけばデータが入力されている状態を再現出来ますので、今回はその内容について書いてみたいと思います。

1.サンプルコード
2.まとめ

1.サンプルコード

◆配列で読み込んだデータをそのまま別セルに出力すると？

先ずはセルA1～A5に入力された数値を配列で読み込み、別セルのC1～C5に書き込みます。
ちなみにA1～A5のデータは「0.12345」の数値に対し表示桁数を変えています。

Sub Sample1()

 Dim Num(1 To 5) As Double
 Dim i As Integer
 
 For i = 1 To 5
  'セルA1～A5読み込み
  Num(i) = Cells(i, 1)
  'セルC1～C5に書き込み
  Cells(i, 3) = Num(i) 
 Next i
 
End Sub

≪実行前≫

≪実行後≫

表示上は桁数を変えていますが、データとしては同一なので配列に読み込んで別セルに書き込むと、そのセルの表示形式に従う為にこのように表示されます。

◆セルをコピーして貼り付けると？

コピーしてそのまま貼り付ける場合についても実施してみます。

Sub Sample2()

 Dim i As Integer
 
  Range(Cells(1, 1), Cells(5, 1)).Copy _
  Destination:=Range(Cells(1, 3), Cells(5, 3))

End Sub

実行前は同一なので、実行後画像としては以下になります。

コピーする場合は元の表示形式も引き継がれるので、表示形式も再現されます。
その為、限られた使用範囲であればこの形でも問題ないです。

ただし、VBAで処理する場合はデータを読み込んで計算などを行ってから該当するセルに書き込んでいったり、検査成績書などにデータを書き込んだりすることもありますから、なかなかコピーして貼り付けて完了！という訳にはいかないのが現実ですね。

◆入力元の表示形式を読み、書き込み先に反映させる

私の場合ですが、特性検査装置などで連続的に取り込んだデータを客先提出用の検査成績書へ反映する際に、品種によって表示桁数が変わることがある為、その書き込まれたデータの表示形式を読み、書き込み先に設定することで臨機応変に対処するような形で使ってます。

Sub Sample3()

  Dim Num(1 To 5) As Double
  Dim i As Integer
  Dim FormD As Variant
  
  For i = 1 To 5
    Num(i) = Cells(i, 1)
　　'読み込むセルの表示形式を取得
    FormD = Cells(i, 1).NumberFormatLocal
    
    Cells(i, 3) = Num(i)
　　'書き込んだセルへ表示形式を反映
    Cells(i, 3).NumberFormatLocal = FormD   
 Next i

End Sub

サンプルコード内に記述していますが、フォーマット取得には

Rangeオブジェクト.NumberFormatLocal

を使います。

RangeオブジェクトとはRange、Cells、Rows、Columnsなどが該当します。

≪実行後≫

この様に表示形式も反映されます。
今回は数値の表示形式に絞って紹介していますが、当然ながら数値のみならず文字列なども対応できますので、後々に別記事で書いてみようと思います。

難しいこともないですから、覚えておくと書き込む際に状況に応じて表示形式を指定する手間が省けますのでお薦めです。