Excelファイルは、ビジネスや学術の場面で最も一般的に使用されるツールの一つです。提出する際の「体裁」が整っているかどうかは、相手に与える印象を大きく左右します。
本記事では、A1にカーソルを揃えたり、ズーム倍率を統一したり、見た目を整えた状態で提出するためのVBAマクロをご紹介します。
目次
基本的な体裁を整える
カーソル位置をA1にして倍率を100%に設定する
共有資料としてExcelファイルを提出する際、カーソルが中途半端な位置にあったり、ズーム倍率が不揃いだと、相手に雑な印象を与えてしまう場合があります。
このマクロでは、以下の処理を自動化することで、ファイルの体制を整えます。
まず、各ワークシートのカーソル位置を「A1」にそろえて、スクロールの位置を左上に戻します。
次に、ズーム倍率をすべてのシート（ワークシートとグラフシート）で「100%」に統一します。
最後に、最初のシートが表示されている場合には、そのシートをアクティブにします。最初のシートが非表示のシートの場合には、次に表示されているシートを調べて、アクティブにします。
Sub ResetCursorAndZoom()
Dim sheet As Object ' シート全般を扱うための変数（ワークシートやグラフシート）
Dim ws As Worksheet ' ワークシート専用の変数
' 画面更新を一時停止（ちらつきを防ぎ）
Application.ScreenUpdating = False
' 1. ワークシートごとにカーソル位置をA1にリセット
For Each ws In Worksheets
' 非表示シートはスキップ
If ws.Visible = xlSheetVisible Then
ws.Select ' ワークシートを選択
ws.Range("A1").Select ' A1セルを選択
ActiveWindow.ScrollRow = 1 ' スクロール位置を一番上にリセット
ActiveWindow.ScrollColumn = 1 ' スクロール位置を一番左にリセット
End If
Next
' 2. 全シート（ワークシートとグラフシート）に対してズーム倍率を100%にリセット
For Each sheet In Sheets
' 非表示シートはスキップ
If sheet.Visible = xlSheetVisible Then
sheet.Activate ' シートをアクティブ化
ActiveWindow.Zoom = 100 ' ズーム倍率を100%に設定
End If
Next sheet
' 3. 最初のシートに移動
If Sheets(1).Visible = xlSheetVisible Then
' 最初のシートが表示されている場合、それを選択
Sheets(1).Select
Else
' 最初のシートが非表示の場合、次に表示されているシートを探して選択
For Each sheet In Sheets
If sheet.Visible = xlSheetVisible Then
sheet.Select
Exit For ' 最初に見つけた表示シートを選択後ループを終了
End If
Next
End If
' 画面更新を再開
Application.ScreenUpdating = True
End Sub
このコードは、Excelファイルのすべての編集が終わった後に実行する機会の多いマクロです。やるかやらないかで、印象が大分異なるので、よければご活用ください。
フォントとサイズの統一
Excelファイルにデータを入力しているとき、特に日本語と英数字を混在させると、フォントの統一感が失われることがあります。
このマクロでは、各ワークシートについて、セル内の文字が日本語か英数字かを判定し、それぞれに適切なフォントとフォントサイズを設定します。フォントとサイズを修正した後は、列幅を自動調整します。
ここでは、例として、日本語を含む場合は「MS コジック」のサイズ12に設定し、英数字のみの場合は「Calibri」のサイズ11に設定しています。
Sub AdjustFontByLanguage()
Dim ws As Worksheet
Dim cell As Range
Dim text As String
' 各ワークシートをループ
For Each ws In ThisWorkbook.Worksheets
' 使用されているセル範囲をループ
For Each cell In ws.UsedRange
' セルが空でない場合に処理を実行
If Not IsEmpty(cell.Value) Then
text = cell.Value
' 日本語を含む場合
If ContainsJapanese(text) Then
cell.Font.Name = "ＭＳ ゴシック"
cell.Font.Size = 12
Else
' 英数字のみの場合
cell.Font.Name = "Calibri"
cell.Font.Size = 11
End If
End If
Next cell
' 列幅を自動調整
ws.Columns.AutoFit
Next ws
End Sub
' 日本語を含むか判定する関数
Function ContainsJapanese(ByVal text As String) As Boolean
Dim i As Integer
' 各文字を調べ、Unicodeコードポイントが255を超える場合は日本語と判定
For i = 1 To Len(text)
If AscW(Mid(text, i, 1)) > 255 Then
ContainsJapanese = True
Exit Function
End If
Next i
ContainsJapanese = False
End Function使用する際には、フォントとサイズを使用したいものに変更して使います。日本語と英数字が混在しているセルについては、日本語のフォントとサイズが使用されるようにしています。
シートの枠線の表示・非表示を切り替える
Excelで作業していると、時々シートの枠線を非表示にして、デザインをスッキリさせたいことがあります。このマクロは、すべてのワークシートに対して枠線の表示・非表示を一括で切り替えます。
Sub HideGridlinesOnAllSheets()
Dim ws As Worksheet
Dim wasSheetVisible As Boolean
' すべてのワークシートに対して処理を行う
For Each ws In ThisWorkbook.Worksheets
' ワークシートが非表示の場合は、一時的に表示に変更
wasSheetVisible = ws.Visible = xlSheetVisible ' 現在の表示状態を保存
If Not wasSheetVisible Then
ws.Visible = xlSheetVisible ' シートを一時的に表示する
End If
ws.Activate ' シートをアクティブにする
' 枠線の表示・非表示を切り替え
If ActiveWindow.DisplayGridlines Then
ActiveWindow.DisplayGridlines = False ' 枠線を非表示にする
Else
ActiveWindow.DisplayGridlines = True ' 枠線を表示する
End If
' 非表示だったシートを再び非表示に戻す
If Not wasSheetVisible Then
ws.Visible = xlSheetHidden ' シートを再度非表示にする
End If
Next ws
End Subシートが非表示の場合には、一旦そのシートを表示させて、シートの枠線の表示・非表示を切り替えたあとに、再びそのシートを非表示に戻しています。
全角数字を半角数字に変換する
Excelでは、データ入力時に全角数字が使われることがあります。特に日本語の入力では、全角数字がデフォルトで使われることが多いため、後から半角数字に変換したい場面もあるかと思います。このマクロは、すべてのワークシート内のセルについて、全角数字を半角数字に簡単に変換します。
Sub ConvertToHalfWidthDigits()
Dim ws As Worksheet
Dim cell As Range
Dim text As String
Dim i As Integer
Dim result As String
' すべてのワークシートに対して処理を行う
For Each ws In ThisWorkbook.Worksheets
' 非表示シートはスキップ
If ws.Visible = xlSheetVisible Then
' ワークシート内のすべてのセルを処理
For Each cell In ws.UsedRange
If Not IsEmpty(cell.Value) Then
text = cell.Value
result = ""
' 各文字をチェックして全角数字のみ半角に変換
For i = 1 To Len(text)
If Mid(text, i, 1) >= "０" And Mid(text, i, 1) <= "９" Then
' 全角数字を半角に変換
result = result & ChrW(AscW(Mid(text, i, 1)) - &HFEE0)
Else
result = result & Mid(text, i, 1)
End If
Next i
cell.Value = result ' 新しい値をセルにセット
End If
Next cell
End If
Next ws
End Subセルの中の文字列を1文字ずつ扱うことで、ひらがなや漢字、全角カタカナや半角カタカナ、全角数字や半角数字が混在している場合でも、全角数字のみを半角数字に変換します。
半角カタカナを全角カタカナに変換する
Excelを使っていると半角カタカナと全角カタカナが混在しているデータを扱う場面があります。このマクロでは、すべてのワークシートについて、半角カタカナを全角カタカナに変換します。
Sub ConvertToZenkakuKatakana()
Dim ws As Worksheet
Dim cell As Range
Dim cellValue As String
Dim convertedValue As String
Dim i As Integer
Dim c As String
Dim next_c As String
' 各ワークシートをループ
For Each ws In ThisWorkbook.Worksheets
' 各シートのセル範囲をループ
For Each cell In ws.UsedRange
' セルが空でない場合に処理
If Not IsEmpty(cell.Value) Then
cellValue = cell.Value
convertedValue = ""
i = 1
' 各文字をチェック
While i <= Len(cellValue)
c = Mid(cellValue, i, 1)
' 半角カタカナの場合
If AscW(c) >= &HFF61 And AscW(c) <= &HFF9F Then
' 次の文字をチェック（濁点・半濁点の確認）
If i < Len(cellValue) Then
next_c = Mid(cellValue, i + 1, 1)
If AscW(next_c) = &HFF9E Or AscW(next_c) = &HFF9F Then
' 濁点・半濁点がある場合は次の文字と結合
c = c & next_c
i = i + 1 ' 濁点・半濁点を処理したのでインデックスを1つ進める
End If
End If
' 半角カタカナを全角カタカナに変換
convertedValue = convertedValue & StrConv(c, vbWide)
Else
' その他の文字はそのまま
convertedValue = convertedValue & c
End If
i = i + 1 ' 次の文字へ
Wend
' セルに変換結果をセット
cell.Value = convertedValue
End If
Next cell
Next ws
End Sub半角カタカナでは、濁点・半濁点がそれぞれ1文字で表されるため、濁点・半濁点がある場合は、半角カタカナ2文字を全角カタカナ1文字に変換しています。
また、セル内の文字列を1文字ずつ扱うことで、セル内に半角英数字が含まれる場合でも、半角カタカナのみを全角カタカナに変換します。
シートやデータの整理
不要なシートの削除
Excelで作業していると、不要なシートが残ってしまうことがあります。特に未使用のシートが増えると、管理が煩雑になりファイルサイズも無駄になります。こちらのマクロでは、未使用の「表示シート」を一括で削除します。非表示のシートに関してはスキップします。
Sub DeleteUnusedVisibleSheets()
Dim ws As Worksheet ' ワークシートを格納する変数を宣言
Dim shapeObj As Object ' 図形オブジェクトを格納する変数を宣言
' 現在のブック内のすべてのワークシートを順番に処理
For Each ws In ThisWorkbook.Worksheets
' シートが表示状態で、使用範囲がA1のみ、かつA1が空白で、かつシートにオブジェクトがない場合
If ws.Visible = xlSheetVisible And ws.UsedRange.Address = "$A$1" And IsEmpty(ws.Cells(1, 1)) Then
' シート内に存在するオブジェクト（グラフ、図形、画像など）の数をチェック
If ws.Shapes.Count = 0 Then
' オブジェクトがない場合にシートを削除
Application.DisplayAlerts = False ' シート削除の確認ダイアログを無効化
ws.Delete ' 未使用の表示シートを削除
Application.DisplayAlerts = True ' 確認ダイアログを再び有効化
End If
End If
Next ws ' 次のワークシートに移動
End Sub使用されているセルの範囲と、シートに配置されているオブジェクトの数を調べることで、使用されているかどうかを判定しています。
処理を戻すことができないため、必要に応じてバックアップをお取りください。
シート名に「非表示」を含むシートを非表示にする
Excelのワークブックには、時々シートを非表示にして管理したい場合があります。例えば、特定の名前を持つシート（例えば「非表示」が名前に含まれるシート）をまとめて非表示にしたい場合があります。こちらのプログラムは、シート名に「非表示」が含まれる場合にシートを非表示にします。ワークシート（通常のシート）だけでなく、グラフシート（ChartSheet）にも適用されます。
Sub HideSheets()
Dim ws As Object ' ワークシートおよびグラフシート両方に対応させるためObject型を使用
' ワークブック内のすべてのシートをループ
For Each ws In ThisWorkbook.Sheets
' シートの名前に「非表示」が含まれている場合
If InStr(ws.Name, "非表示") > 0 Then
' シートを非表示にする
ws.Visible = xlSheetHidden
End If
Next ws
End Sub日付のフォーマットの統一
Excelを使っていると、異なるシートに入力されている日付のフォーマットがばらばらになってしまうことがあります。特に、大量のデータを扱う場合、日付フォーマットの不一致が作業を煩雑にする原因になります。
このマクロは、日付を統一したフォーマットに整えます。
具体的には、Excelのワークブック内にあるすべてのワークシートの使用範囲をチェックし、日付形式のセルに「yyyy-mm-dd」という標準的な日付フォーマットを適用します。
このコードを実行すれば、フォーマットが異なる日付が一括で統一されます。
Sub StandardizeDateFormat()
Dim ws As Worksheet ' ワークシートを格納する変数 ws を宣言
Dim cell As Range ' セルを格納する変数 cell を宣言
' ワークブック内のすべてのワークシートをループ
For Each ws In ThisWorkbook.Worksheets
' シートが非表示でない場合に処理を実行
If ws.Visible = xlSheetVisible Then
' 各ワークシートの使用されている範囲をループ
For Each cell In ws.UsedRange
' セルの値が日付であるか確認
If IsDate(cell.Value) Then
' 日付が確認できた場合、日付フォーマットを「yyyy-mm-dd」に設定
cell.NumberFormat = "yyyy-mm-dd"
' 他にも様々な表現が可能
' cell.NumberFormat = "mm/dd/yyyy"
' cell.NumberFormat = "yyyy/mm/dd"
' cell.NumberFormat = "yyyy年mm月dd日"
' cell.NumberFormat = "mm月dd日"
End If
Next cell
End If
Next ws
End Sub
このマクロはセルが日付のみのデータに対して有効です。文章の中に日付が入っている場合は、マクロの適用範囲外【当該箇所を手動で変更する必要がある】なので、ご注意ください。
"yyyy-mm-dd"の部分を修正することで、どのフォーマットに統一するかを変更することができます。
ハイパーリンクとコメントの管理
ハイパーリンクのチェック
大規模なExcelファイルを扱う場合、ハイパーリンクが正しく機能しているかを手動で確認するのは時間のかかる作業になります。
このマクロはリンク切れを自動で検出し、その数をカウントします。
このコードを実行すると、ワークブック内のすべてのワークシートをスキャンし、リンク切れのパイパーリンクを検出します。そして、リンク切れの数をメッセージボックスで表示し、リンク切れの場合には[リンク切れ]のテキストをハイパーリンクに追加します。
リンク切れの場合にテキストが修正されることにより、Excelファイルで[リンク切れ]という文字を検索すると、リンク切れのハイパーリンクを簡単に見つけることができるようになります。
Sub CheckHyperlinks()
Dim ws As Worksheet ' ワークシートを格納する変数 ws を宣言
Dim hl As Hyperlink ' ハイパーリンクを格納する変数 hl を宣言
Dim brokenLinks As Long ' リンク切れの数をカウントする変数を宣言
brokenLinks = 0 ' リンク切れのカウントを初期化
' ワークブック内のすべてのワークシートをループ
For Each ws In ThisWorkbook.Worksheets
' ワークシート内のすべてのハイパーリンクをループ
For Each hl In ws.Hyperlinks
' ハイパーリンクのアドレスが無効（存在しない場合）かどうかをチェック
If Dir(hl.Address) = "" Then
brokenLinks = brokenLinks + 1 ' リンク切れのカウントを1増やす
' リンク切れであることを示すテキストをハイパーリンクに追加
hl.TextToDisplay = "[リンク切れ]" & hl.TextToDisplay
End If
Next hl
Next ws
' 結果をメッセージボックスで表示
MsgBox "リンク切れのハイパーリンク数: " & brokenLinks
End Sub
リンク切れのテキストを検索するには、メニューの 「編集」から「検索」を選択するか、「Ctrl」キーと「F」キーを同時に押して表示される 検索窓を使用します。
すべてのコメントを削除
Excelファイルを整理していると、不要なコメントが大量に残っている場合があります。
例えば、部下が作ったファイルに上司が修正のためにコメントをつけたものを消さずに、そのファイルが客先から送られてきたことがあります。
このマクロを使用すると、コメントをすべて削除することができますので、コメントが残る可能性を減らすことができます。
Sub RemoveAllCommentsVisibleSheetsOnly()
Dim ws As Worksheet ' ワークシートを格納する変数を宣言
' 現在のブック内のすべてのワークシートを順番に処理
For Each ws In ThisWorkbook.Worksheets
' シートが表示されている場合のみ処理を実行
If ws.Visible = xlSheetVisible Then
' ワークシート内のすべてのセルからコメントを削除
ws.Cells.ClearComments
End If
Next ws ' 次のワークシートに移動
End Subこのマクロは、非表示のシートに関してはスキップしています。全てのシートに適用する場合は、ws.Cells.ClearCommentsをif文の外に出して、if文は削除します。
処理を戻すことができないため、必要に応じてバックアップをお取りください。
セキュリティとプライバシーの確保
個人情報を削除する
Excelファイルには、作成者名やタイトル、キーワード、最終編集者などの情報が埋め込まれています。
しかし、ファイルを他の人と共有する際に、不必要な個人情報や履歴を削除したい場合があります。このマクロを実行することで、Excelファイルのプロパティに保存された作成者情報などを簡単に削除できます。
Sub RemoveMetadata()
' Excelファイルのビルトインドキュメントプロパティにアクセスするためのもの
Dim docProps As Object
' ThisWorkbookは現在開いているワークブックを指します。
' BuiltinDocumentPropertiesでワークブックのメタデータ（作成者情報、タイトルなど）を取得
Set docProps = ThisWorkbook.BuiltinDocumentProperties
' ワークブックの各プロパティを空に設定することで、メタデータを削除
docProps("Last Author") = "" ' 最終編集者の名前を削除
docProps("Author") = "" ' 作成者の名前を削除
docProps("Title") = "" ' ドキュメントのタイトルを削除
docProps("Subject") = "" ' 件名を削除
docProps("Keywords") = "" ' キーワードを削除
docProps("Comments") = "" ' コメントを削除
docProps("Category") = "" ' カテゴリを削除
docProps("Manager") = "" ' 管理者情報を削除
docProps("Company") = "" ' 会社情報を削除
docProps("Content Type") = "" ' コンテンツタイプを削除
End Sub
補足
ご紹介したものは個別のマクロになりますが、これらのマクロから必要なマクロだけをまとめて呼び出すマクロを作っておけば、一回そのマクロを実行するだけで、多くの処理を自動化できます。便利なので、よければご活用ください。
まとめ
本記事では、Excelファイルを提出する際に便利なVBA処理を紹介しました。これらのVBAマクロを使うことで、手作業では時間がかかる体裁整えを自動化し、効率的に作業を進めることができます。参考になりましたら幸いです。
