書式設定ダイアログを出すのが面倒
セルに入力する際に、必要に応じてセルの表示形式を「標準」から「文字列」などに変更することがあります。
以下のセルの書式設定ダイアログを開いて、表示形式タブの分類欄から選ぶことになりますが、何度もこれをやるのであれば実に面倒です。
セルの書式設定ダイアログはホームタブの数値から選択したり、セルを右クリックしてセルの書式設定を選択したり、Ctrl + 1のショートカットキーを押したりすることで開きますが、いずれの場合もダイアログが開いたあとの操作はマウス操作やキーボード操作が必要になります。
以下のマクロは選択セル範囲のセルの表示形式の分類を「標準」と「文字列」を交互に切り替えることができます。単に「標準」または「文字列」を設定するマクロも紹介します。
テキストファイルから貼り付けた場合に文字列から標準に設定しても左寄せになったままへの対応も入れています。
セルの表示形式切替マクロ
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
Sub ChangeNumberFormatLocal() Dim iRow As Long '// 行位置 Dim iCol As Long '// 列位置 Dim iRowMax As Long '// 行数 Dim iColMax As Long '// 列数 Dim sFormatBefore As String '// 現在表示形式 Dim sFormatAfter As String '// 変換後表示形式 '// 現在の表示形式を取得 sFormatBefore = ActiveCell.NumberFormatLocal '// 標準の場合は文字列に変換 If (sFormatBefore = "G/標準") Then sFormatAfter = "@" '// 文字列の場合は標準に変換 ElseIf (sFormatBefore = "@") Then sFormatAfter = "G/標準" End If '// 変換後の表示形式を設定 Selection.NumberFormatLocal = sFormatAfter '// 表示形式が文字列以外はここで処理を終了する If (sFormatAfter <> "@") Then Exit Sub End If '// 行位置、列位置、行数、列数を取得 iRow = Selection.Row iCol = Selection.Column iRowMax = iRow + Selection.Rows.Count - 1 iColMax = iCol + Selection.Columns.Count - 1 '// 選択セル範囲を列ごとにループ For iCol = iCol To iColMax If (Range(Cells(iRow, iCol), Cells(iRowMax, iCol)).Text <> "") Then '// 区切り位置設定 Call Range(Cells(iRow, iCol), Cells(iRowMax, iCol)).TextToColumns End If Next End Sub |
コード説明（前半部分）
このマクロは21行目までの表示形式を設定する前半部分と、それ以降の区切り位置の設定を行う後半部分に分かれています。
前半部分は表示形式が標準だったら文字列に変換し、文字列だったら標準に変換する、という入れ替え処理です。
10行目でアクティブセルの表示形式を取得していますが、選択セル範囲に複数の表示形式が存在することはありえるため、アクティブセルの表示形式を代表させています。
他の表示形式の切り替えを行いたい場合はIf文を修正してください。参考として以下に最初から用意されている表示形式を載せておきます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
'// 数値 Selection.NumberFormatLocal = "0_ " '// 通貨 Selection.NumberFormatLocal = "\#,##0;\-#,##0" '// 会計 Selection.NumberFormatLocal = "_ \* #,##0_ ;_ \* -#,##0_ ;_ \* ""-""_ ;_ @_ " '// 日付 Selection.NumberFormatLocal = "yyyy/m/d" '// 時刻 Selection.NumberFormatLocal = "[$-F400]h:mm:ss AM/PM" '// パーセンテージ Selection.NumberFormatLocal = "0%" '// 分数 Selection.NumberFormatLocal = "# ?/?" '// 指数 Selection.NumberFormatLocal = "0.E+00" |
コード説明（後半部分）
後半部分の処理はセルの表示形式を最初から文字列にしている場合の対応です。
セルの表示形式を常に標準で使っている場合は関係ないのですが、表示形式が文字列のセルに対してテキストファイルの内容をコピペすると、その後表示形式を「標準」や「数値」に変更しても右寄せにならないExcelのバグがあります。
表示形式を「標準」にして右寄せにしたい場合は、セル内の文字列を一度選択してEnterを押すか、区切り位置の設定を行うことで対応が可能です。
マクロの後半部分はそのための対応で、TextToColumnsメソッドは列単位にしか実行できないため、選択セル範囲を列ごとに処理するループを行っています。
使い方
上のマクロの使い方は、表示形式を標準から文字列、または、文字列から標準に切り替えたいセル範囲を選択して上で紹介しているChangeNumberFormatLocal関数を実行するだけです。
以下は使い方の例です。
1. セルに入力やテキストファイルからコピペします。A列の表示形式は標準で、B列とC列は文字列です。C列にテキストファイルからコピペしています。
2. 切り替えたいセル範囲を選択します。
3. ChangeNumberFormatLocal関数を実行します。アクティブセルのA1が標準のため文字列に切り替わります。このときに、カンマが外れてしまいます。これは標準の表示形式が実質は「#,##0」の通貨形式になっているためです。
4. 再度ChangeNumberFormatLocal関数を実行します。今度は文字列から標準に切り替わります。
通貨形式などの切り替えを行う場合
通貨形式に限りませんが、表示形式が複雑な場合はそれに応じたコーディングをする必要が出てきます。
コード前半部分のIf文に判定処理を加えることで対応が可能になりますので必要な場合は適宜ElseIf条件などを追加してください。
標準または文字列に設定するマクロ
上のマクロは標準と文字列を切り替えるマクロですが、切り替える必要がなく、単に標準や文字列に設定したい場合もあります。
内容はほとんど上で紹介したものと同じですが、切替処理がありません。
選択セル範囲の表示分類を標準に設定するマクロ
|
1 2 3 |
Sub ChangeFormatRegular() Selection.NumberFormatLocal = "G/標準" End Sub |
選択セル範囲の表示分類を文字列に設定するマクロ
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Sub ChangeFormatString() Dim iRow As Long '// 行位置 Dim iCol As Long '// 列位置 Dim iRowMax As Long '// 行数 Dim iColMax As Long '// 列数 '// 変換後の表示形式を設定 Selection.NumberFormatLocal = "@" '// 行位置、列位置、行数、列数を取得 iRow = Selection.Row iCol = Selection.Column iRowMax = iRow + Selection.Rows.Count - 1 iColMax = iCol + Selection.Columns.Count - 1 '// 選択セル範囲を列ごとにループ For iCol = iCol To iColMax If (Range(Cells(iRow, iCol), Cells(iRowMax, iCol)).Text <> "") Then '// 区切り位置設定 Call Range(Cells(iRow, iCol), Cells(iRowMax, iCol)).TextToColumns End If Next End Sub |