VBAで「Sum関数」を使用して足し算したけど、計算結果が間違っているなぁ。
使い方は合っているのに、どこがダメなんだろう？
セルの表示形式が「数値」になっていないのでは？
あー、はい、その点は考慮してまして。
元の表示形式が「文字列」だったので、VBAから「NumberFormatLocal」を使って「数値」に変更して、その後で足し算するようにしたんですよね。
なので「数値」として計算できて、正しい結果になると思ったのですが。
とすると、変更したけど「反映」はされていないのかもね。
どういうことですか？？？
理由はわからないけど、表示形式を変更しても反映されない場合があるみたい。
エクセルで言うところの「区切り位置」をVBAから実行すれば反映できると思うよ。
はじめに
おそらく多くの方が、エクセルを使っていて以下のような事象に一度は遭遇したことがあるのではないでしょうか。
たとえば、表示形式が「文字列」の数字を「数値」に変更した場合、エクセルの書式設定から確認すると表示形式は「数値」に変更されていますが、見た目上は左寄りになっていて、なぜか実際には反映されていません。
このままでは、数値として認識されていないため、計算式などで正しい結果が得られません。
そのため、セルを選択して「F2＋Enter」したり、ダブルクリックしたり、あるいはエクセルの「区切り位置」を実施して、表示形式が「数値」として認識されるようにします。
これはVBAから表示形式を変更した場合も同じです。
エクセルと同じように「区切り位置」をVBAから実施すれば反映されるので、その方法を紹介します。
ソースコード
セルA1～A3に文字列の数字が設定されている状態で、表示形式を数値に変更し、「区切り位置」の実施前と実施後で足し算の結果を確認してみます。
Sub Test()
'「TextToColumns（区切り位置）」で対象にできるセル範囲は「1列のみ」
Dim targetRng As Range
Set targetRng = ThisWorkbook.Worksheets("Sheet1").Range("A1:A3")
'表示形式を「数値」に変更
targetRng.NumberFormatLocal = "0_ "
MsgBox "反映前に足し算：" & WorksheetFunction.Sum(targetRng)
'エクセルの「区切り位置」を実施（これで表示形式が反映される）
targetRng.TextToColumns
MsgBox "反映後に足し算：" & WorksheetFunction.Sum(targetRng)
End Sub実行結果
セルに「文字列」の数字を設定しておきます。
まずは、「NumberFormatLocal = “0_ “」により、表示形式を「数値」に変更した時点の状態を見てみます。
表示形式は「数値」に変更されています。
ただし、見た目上は左寄りのままで、実際に反映はされていません。
そのため、この時点での計算結果は、正しくありません。
続いて、「TextToColumns」メソッドを実行後の時点の状態です。
表示形式が反映されて、右寄りになっています。表示形式は「数値」のままです。
反映後は計算結果も正しくなっています。
説明
ソースコードの11行目「TextToColumns」メソッドにより、エクセルでいうところの「区切り位置」を実行して、表示形式を反映させています。
「TextToColumns」メソッドにはパラメーターがあり、マクロの記録で確認するといくつかのパラメーターが設定されるのですが、パラメーターはすべて省略可能なので、上記ソースコードのように何も設定しないでも問題はなさそうです。
パラメーターの詳細については、マイクロソフトのサイトを参照ください。
注意点として、エクセルの「区切り位置」と同じことをしているので、対象にできるセル範囲は「1列だけ」です。
一度に数列とか、行指定とかはできません。
また、ソースコードでは「Range(“A1:A3”)」のように列の一部分を指定していますが、「Range(“A:A”)」や「Columns(1)」のように、列全体を指定してもOKです。
ちなみに、表示形式を数値にする「0_ 」ですが、分かりづらいですが、アンダーバーの後ろに「半角空白」が付いています。
「半角空白」がないと実行時にエラーになるので、この記事の内容とは関係ありませんが、ご注意ください。
複数列で実施したい場合は、対象とする列を「1列ずつ」For文でループさせるなどして「区切り位置」を実施する必要があります。
以下のコードは、その一例です。
'--------------------------------------------------
'1、2、5、10列目の表示形式を「数値」に変更する場合の例
'--------------------------------------------------
Sub Test_2()
Dim targetColumnArr As Variant
targetColumnArr = Array(1, 2, 5, 10) '対象とする列番号を配列に詰める
Dim i As Long
For i = 0 To UBound(targetColumnArr)
With ThisWorkbook.Worksheets("Sheet1").Columns(targetColumnArr(i))
.NumberFormatLocal = "0_ " '表示形式を「数値」に変更
.TextToColumns 'エクセルの「区切り位置」を実施
End With
Next i
End Subまとめ
以上、表示形式が反映されない場合の解決方法の紹介でした。
表示形式を変更しているのにそれが反映されないとは。。
解決方法はコード1行で済む簡単なものですが、知らないとドツボにはまる「罠」ではないでしょうか。
この事象は、エクセルを使っているときに遭遇することが多いのではないかと思います。
ですので、対象とする列がたくさんある場合や、頻繁に実施する必要がある場合など、手動で「区切り位置」を実施するのに手間を掛けているのであれば、VBAを組んで実施することで、手間を省くことができると思います。
以上、ご覧いただきありがとうございました。