VBAでセルの書式変更を反映させる｜ Excel作業をVBAで効率化

セルの書式設定を変えたのに反映されない

セルの書式を文字列にしてデータを入力している場合に、数値や数式を使っているセルの場合は標準の書式設定にして利用することがあります。

その場合、書式を文字列から標準などに変更するのですが、なぜか元の表示のままで数値や数式として認識されません。昔からこうなんで調べてませんがおそらくMicrosoftは「仕様です」と言い切っちゃってるんだと思います。

これを解決するにはセルの中にマウスカーソルを置いてEnterキーを押して、再度入力しなおしたような操作をする必要があります。ただ、これを１セルずつやるのはとても面倒です。

他の方法としては「区切り位置」機能を使う方法があります。１列ずつ選択して「データ」タブ→「データツール」→「区切り位置」をクリックし、表示されたダイアログの「完了」ボタンを押すと、変更後の書式で再表示されます。

ただ、この方法も列数が多いと面倒です。以下ではこれを自動化するマクロを紹介します。

コード

以下のコードは入力セル範囲の全ての列に対して「区切り位置」機能を使ったセルの再表示を行います。セルの書式を変えたところがなければ実行しても何も変わりません。

処理内容ですが、「区切り位置」機能をExcelで行うのと同じように実行しています。具体的には、入力セル範囲の列数だけ、列の選択を行って区切り位置機能の実行しています。

注意点として、「区切り位置」機能のTextToColumnsメソッドのDestinationプロパティの設定があります。マクロの記録機能を使うとDestinationプロパティにはActiveCellが指定されますが、それだと問題が起きます。

このプロパティは変更後の内容をどのセルに反映させるかを指定するのですが、行位置と列位置を入力セル範囲に合わせて指定しないと、実行後に違うところに値が設定されてしまいます。

例えば、C5セルからG17セルまで間で値を入力しているような場合、上4行と左2列が未入力の状態になります。上4行が未入力のため、DestinationプロパティにActiceCellを指定すると、実行後にセルが４つ上にずれることになります。以下の関数はそれを考慮して行位置と列位置をDestinationプロパティに指定するようにしています。

Sub ReloadFormat()
    Dim iColStart       As Integer      '// 開始列位置
    Dim iColEnd         As Integer      '// 終了列位置
    Dim i               As Integer      '// ループカウンタ
    Dim iCol            As Integer      '// 列位置
    Dim iRow            As Long         '// 行位置
    Dim rInit           As Range        '// 開始時選択セル
    
    Application.ScreenUpdating = False
    
    Set rInit = Selection
    
    iRow = ActiveSheet.UsedRange.Row
    iColStart = ActiveSheet.UsedRange.Column
    iColEnd = ActiveSheet.UsedRange.Columns.Count + iColStart - 1
    
    '// 開始列から終了列までループ
    For i = iColStart To iColEnd
        '// 列選択
        Cells(iRow, i).Columns("A:A").EntireColumn.Select
        
        '// データ区切りを指定列に対して実行
        Call Selection.TextToColumns( _
            Destination:=Cells(iRow, i), _
            DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, _
            ConsecutiveDelimiter:=False, _
            Tab:=True, _
            Semicolon:=False, _
            Comma:=False, _
            Space:=False, _
            Other:=False, _
            FieldInfo:=Array(1, 1), _
            TrailingMinusNumbers:=True)
    Next
    
    '// 開始時セルを再選択
    rInit.Select

    Application.ScreenUpdating = True
End Sub

Sub ReloadFormat()

Dim iColStart As Integer '// 開始列位置

Dim iColEnd As Integer '// 終了列位置

Dim i As Integer '// ループカウンタ

Dim iCol As Integer '// 列位置

Dim iRow As Long '// 行位置

Dim rInit As Range '// 開始時選択セル

Application.ScreenUpdating = False

Set rInit = Selection

iRow = ActiveSheet.UsedRange.Row

iColStart = ActiveSheet.UsedRange.Column

iColEnd = ActiveSheet.UsedRange.Columns.Count + iColStart - 1

'// 開始列から終了列までループ

For i = iColStart To iColEnd

'// 列選択

Cells(iRow, i).Columns("A:A").EntireColumn.Select

'// データ区切りを指定列に対して実行

Call Selection.TextToColumns( _

Destination:=Cells(iRow, i), _

DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, _

ConsecutiveDelimiter:=False, _

Tab:=True, _

Semicolon:=False, _

Comma:=False, _

Space:=False, _

Other:=False, _

FieldInfo:=Array(1, 1), _

TrailingMinusNumbers:=True)

'// 開始時セルを再選択

rInit.Select

Application.ScreenUpdating = True

End Sub

使い方

セルの書式を文字列から標準に変更して、上の関数を実行するだけです。

例えば、「123」と入力された文字列書式のセルの場合、左寄せで表示されていますが、数値書式に変えて上の関数を実行すると右寄せになり数値として認識できるようになります。

セルの書式設定を変えたのに反映されない

コード

使い方

関連記事