Excelワークシートをシート名でソートするマクロ

今回は、ワークシートをシート名で並べ替える VBA マクロを作成しました。

エクセルさん、残念です・・・

前回の記事で、Excel の新機能である「ナビゲーション」について紹介しました。

www.shegolab.jp

ナビゲーションは Excel シートの操作性を各段に向上させるうれしい機能ですが、一点、物足りなかったことがあります。

それはシートの並べ替えをする機能がなかったことです。

ナビゲーションペインでシートをソートしたり、ドラッグで移動できたりしたらもっと便利だったと思うのは筆者だけでしょうか。

ただそれが ”ナビゲーション” でやるべき機能なのか、といえば、確かに微妙なところです。

ないものねだりをしても仕方ないので、本記事ではワークシートをシート名で昇順ソートする機能をマクロで自作してみようと思います。

ワークシートをソートするマクロ

ワークシートをシート名でソートするマクロを作成してみました。下記 VBA プログラムを標準モジュールにコピー＆ペーストするだけで使えます。

本マクロを実行すると、アクティブブックの複数のワークシートをシート名の昇順ソートで並べ替えます。

並べ替え対象となるワークシートは、シートの選択状態によって異なります。

選択されているシートが一つしかない（アクティブシートのみの）ときには、それ以降（後ろ）（選択シートを含む）にある全てのワークシートが並べ替えられます。
複数シートが選択状態になっているときは、選択されているシートのみを並べ替え対象とします。

デフォルトでアクティブシート以降を並べ替え対象にしたのは、よくある表紙や目次、変更履歴といった先頭で固定する定型シートがある文書形式での使い勝手を想定しているためです。

もし、ブックにあるすべてのワークシートを並べ替えたいときには、先頭のワークシートをのみを選択状態にしてから本マクロを実行してください。

また任意のワークシートのみを対象にソートしたいときには、あらかじめ対象シートを複数選択してからマクロを実行します。

Option Explicit
Option Compare Text ' 辞書順

Sub ワークシートの並べ替え_昇順()
    ' ソート対象のシートコレクション
    Dim sortSheets As Sheets
    Set sortSheets = ActiveWindow.SelectedSheets
    If sortSheets.Count = 1 Then
        ' アクティブシートのみならそれ以降を対象とする
        Set sortSheets = ActiveWorkbook.Worksheets(intRange(ActiveSheet.Index, Worksheets.Count))
    End If
    If sortSheets.Count = 1 Then Beep: Exit Sub
    
    ' ソート対象のシート名のソート済み配列を取得
    Dim sortedNames As Variant
    sortedNames = arraySort(getSheetNames(sortSheets))
    'sortedNames = WorksheetFunction.Sort(getSheetNames(sortSheets), 1, 1, True) ' Microsoft 365 Excel でサポート

    ' ソート対象のコレクションをシート名で並べ替え
    Set sortSheets = sortSheets(sortedNames)
        
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    ' コレクションの順序に従ってソート対象のワークシートを並べ替え
    Dim anch As Worksheet
    Dim sht As Worksheet
    Set anch = sortSheets(1)
    For Each sht In sortSheets
        sht.Move After:=anch ' 昇順
        'sht.Move Before:=anch ' 降順
        Set anch = sht
    Next
    
    sortSheets(1).Select
        
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Private Function intRange(a As Long, b As Long) As Long()
    Debug.Assert a <= b
    Dim arr() As Long
    ReDim arr(1 To b - a + 1)
    Dim i As Long
    For i = 1 To UBound(arr)
        arr(i) = a + i - 1
    Next
    intRange = arr
End Function

Private Function getSheetNames(shts As Sheets) As String()
    Dim sheetNames() As String
    ReDim sheetNames(1 To shts.Count)
    Dim i As Long
    For i = 1 To shts.Count
        sheetNames(i) = shts(i).Name
    Next
    getSheetNames = sheetNames
End Function

Private Function arraySort(ByVal arr As Variant) As Variant
    Debug.Assert IsArray(arr)
    If 0 < UBound(arr) Then
        qsort arr, LBound(arr), UBound(arr)
    End If
    arraySort = arr
End Function

Private Sub qsort(ByRef arr As Variant, ByVal lb As Long, ByVal ub As Long)
    Dim lh As Long
    Dim rh As Long
    Dim pv, tmp
    
    If lb >= ub Then Exit Sub
    lh = lb
    rh = ub
    pv = arr((lb + ub) \ 2)
    Do While True
        Do While arr(lh) < pv: lh = lh + 1: Loop
        Do While arr(rh) > pv: rh = rh - 1: Loop
        If lh >= rh Then Exit Do
        tmp = arr(lh): arr(lh) = arr(rh): arr(rh) = tmp
        lh = lh + 1
        rh = rh - 1
    Loop
    qsort arr, lb, lh - 1
    qsort arr, rh + 1, ub
End Sub

【使い方】

上記 VBA プログラムを新規の標準モジュールにコピー＆ペーストします
- マクロは対象のワークブックとは別の新規ワークブックに用意することをお勧めします
対象のワークブックを開き「ワークシートの並べ替え_昇順」マクロを実行します
- アクティブシート以降のワークシートが対象となります
- 一部のワークシートのみを並べ替えるには、対象シートを複数選択してからマクロを実行します。ワークシートを複数選択するには、Ctrl キーや Shift キーを押しながらシートの見出しタブをクリックしていきます
ワークシートがシート名で昇順に並べ替えられます

シート名の昇順ではなく、降順で並べ替えをしたいときには、上記 VBA プログラム内のコメントアウトされている該当行を差し替えてください。

本マクロでは古いバージョンの Excel でも使えるよう、自作のソート関数でシート名を並べ替えていますが、最新の Microsoft 365 版（サブスク版）Excel をご使用の方は、配列の並べ替えにWorksheetFunction.Sort 関数 が使えるようになっていますので、そちらに差し替えた方がいいでしょう。

【注意】マクロの実行結果は「元に戻す（Undo）」することができませんので、あらかじめバックアップをとるなど、十分に注意して使用してください。また本記事のマクロの使用に際して不具合や誤操作により発生したいかなる損失や損害について本ブログの筆者は責任を負いかねますので予めご了承ください。

【Note】不具合等ありましたらコメント欄か Twitter にてお知らせください。

Excel VBA メモ

今回の Excel VBA に関連していろいろ調べてしまった技術的な話題についてのメモをいくつか残しておきます。レガシー過ぎて今更役立つこともなさそうな知識ですが、興味のある方のご参考まで。

Workbook.Sheets と Workbook.WorkSheets の違い
Sheets オブジェクトの操作
VBA で配列をソートする
VBA の文字列の照合順序について

Workbook.Sheets と Workbook.WorkSheets の違い

Workbook オブジェクトにはシートのコレクションとして Sheets と Worksheets の２つが用意されています。

これらはどのような違いがあり、どのように使い分ければいいのかよく分かりません。

まず、Excel のシートと呼ばれるものには４種類あるようです。シートタブを右クリック⇒「挿入」で開くダイアログで、その４種類のシートを確認できます。

これらのうち、私たちがふだん使用しているのは「ワークシート」という表計算用のシートのみです。

他のシートを使ったことはありませんが、何かのショートカットのタイプミスで意図せず出てきてしまい、見なかったことにしたことならあるような気がします。

ワークシート以外のシートは、かなり昔のバージョンの Excel で使われていたものの名残りで、現在はもう使い道のない無用なものとみなして問題ないようです。（なぜ廃止されないのか）

ただ、その名残りは VBA で使う Excel API の設計にも残っていて、それが２つのシートコレクションの位置づけに関わっているようです。

Workbook.Sheets は４つの全種類のシートを含むコレクションです。

一方、Workbook.Worksheets はワークシートのみを保持するコレクションです。

ふつう VBA プログラミングでは、ワークシート以外のシートの存在など想定しません。したがって両コレクションの内容は同じになるのでどっちを使っても結果的に同じなのですが、通常は Worksheets を使用していれば問題ないはずです。

万一、ワークシート以外のタイプのシートが存在していると、Worksheets からワークシートの追加やシート名の変更したときなどに、名前の重複でエラーになる可能性が 0 ではありません。

その可能性を排除したい場合には、あえて Workbook.Sheets の方を使い、シートの種類を判定することになりますが、それも一筋縄ではいきません。

XlSheetType enumeration (Excel) | Microsoft Learn

シート種別	TypeName() 結果	Typeプロパティ	メモ
ワークシート	Worksheet	xlWorksheet(-4167)
グラフ	Chart	3	xlChart(-4109)は使えない？
Excel 4.0 マクロ	Worksheet	xlExcel4MacroSheet(3)	xlExcel4IntlMacroSheet(4) というのもあるらしい
MS Excel 5.0 ダイアログ	DialogSheet	なし	xlDialogSheet(-4116)

' シートがワークシートであるかをチェックする
Private Function isWorksheet(sht As Object) As Boolean
    If TypeName(sht) = "Worksheet" Then
        If sht.Type = xlWorksheet Then
            isWorksheet = True
            Exit Function
        End If
    End If
    isWorksheet = False
End Function

そのほかにも Sheets を使うべき状況はあるのかもしれませんが、調べても出てきませんでした。もしご存じの方、教えていただければ。

Sheets オブジェクトの操作

Workbook.WorkSheets、Workbook.Sheets、Window.SelectedSheets は Sheets オブジェクトを返します。

Sheets オブジェクト (Excel) | Microsoft Learn

Sheets オブジェクトはシートのコレクションで、シートの位置（インデックス）やシート名で特定の Worksheet オブジェクトを取り出すことができます。

これが普通のコレクションと違うのは、複数のインデックスやシート名を配列で渡してやることで、複数シートを取り出すことができるところです。戻り値は部分コレクションとしての Sheets オブジェクトになります。

また、コレクション内のシートオブジェクトの順序は、配列して指定した順になります。

    Dim shts As Sheets
    Dim sht As Worksheet
    
    ' インデックスの配列で複数シートを取得
    Set shts = Worksheets(Array(1, 5, 3))
    For Each sht In shts
        Debug.Print sht.Name
    Next

    ' シート名の配列で複数シートを取得
    Set shts = Worksheets(Array("Sheet1", "Sheet5", "Sheet3"))
    For Each sht In shts
        Debug.Print sht.Index
    Next

VBA で配列をソートする

VBA が配列をソートする機能を標準で提供しないのは、プログラミング言語としていかがなものかと思わざるを得ません。

VBA プログラマのみなさんはソートアルゴリズムを一から組むか、わざわざ配列データをシート上に置いて並べ替えをするといった、余計なコーディングに永らく煩わされてきたことでしょう。

ところが最近、 Microsft 365 版に至って ―― Excel に限っては ―― Sort メソッドと SortBy メソッドが WorksheetFunction オブジェクトに追加されたことで、標準で配列ソートが可能な環境がようやく整いました。

これらはワークシート関数の SORT 関数と SORTBY 関数の提供に伴うものです。（とはいえまだ公式ドキュメントには記載がありません（2023年1月時点））

WorksheetFunction.Sortメソッド (Excel) | Microsoft Learn（未記載）
WorksheetFunction.SortByメソッド (Excel) | Microsoft Learn（未記載）

使い方や引数の仕様は基本的にワークシート関数の SORT 関数、SORTBY 関数と同様のはずです。

ただ、VBA で使うにはやはりクセがあります。

    ' WorksheetFunction.Sort/SortBy は1次元配列を1行の2次元配列とみなす。
    ' Sort で1次元配列をソートするには、第4引数に TRUE (列で並べ替え)を指定する。
    arr = Array(3, 2, 5, 4, 1)                      ' 1次元配列データ
    arr = WorksheetFunction.Sort(arr, 1, 1, True)   ' 1行目(1)昇順(1)列で並べ替え(TRUE)
    For i = 1 To UBound(arr)                        ' 添え字は1ベースになる
        Debug.Print arr(i)
    Next


    ' SortBy は列で並べ替えができないので、1次元配列を列方向に並ぶよう Transpose で変換する。
    arr = Array(3, 2, 5, 4, 1)                      ' 1次元配列データ
    arr = WorksheetFunction.Transpose(arr)          ' 1列の2次元配列に変換
    arr = WorksheetFunction.SortBy(arr, arr, 1)     ' 昇順ソート
    arr = WorksheetFunction.Transpose(arr)          ' 1次元配列に戻す
    For i = 1 To UBound(arr)
        Debug.Print arr(i)
    Next

とはいえこれらメソッドがもつ強力な機能を使いこなせば、２次元配列の多重ソートや複数条件によるソートなど複雑なソートもお手軽にできるようになったわけです。

これら新規メソッドの使い勝手についても、また別の機会に検証してみたいと思います。

VBA の文字列の照合順序について

本記事マクロで自作したソート関数は、コピペで切り出してそのまま他でも流用できますが、その際の注意があります。

単純に関数部分だけをコピペするのではなく、先頭にある Option Compare ステートメント も忘れずにコピーしてください！

Option Compare Text

これを忘れると、文字列データをソートしたときに、期待とは異なる並び、つまりExcel のソート機能（「並べ替え」や SORT 関数など）による結果とはだいぶ異なった並び順となってしまいます。

それは VBA の文字列比較が、デフォルトではバイナリ形式での比較になるからです。結果は単純に Unicode の文字コード順になります（おそらく）。

一方、Excel がソートに使っている文字列の照合順序（Collation）は、システムロケールで定義された辞書順となっています。

VBA プログラムでの文字列照合順序を Excel ソート（ロケール辞書順）に合わせるには、VBA の Option Compare ステートメントで、文字列比較のモードを「Text」に変更する必要があります。

Option Compare ステートメント (VBA) | Microsoft Learn

これを省略した場合、デフォルトで Binary を設定した状態になります。もちろん運用上それが望ましい場合は、省略して問題ありません。

【Note】ちなみにWorksheetFunction の Sort メソッドと SortBy メソッド が Option Compare ステートメントの影響を受けず常にロケール辞書順になることは確認しました。

さて、照合順序の設定をしてもなお、自作ソートは Excel ソートの結果の並び順と厳密には一致しません。具体的には、大文字と小文字の違いのみで区別される文字列間で並び順が入れ違う可能性があります。

それは照合順序ではなくソートアルゴリズムの問題です。

今回自作したソート関数はクイックソートなのですが、このアルゴリズムでは等価値の並びを保存するような「安定ソート」にはなりません。

一方 Excel ソートは何かしらの安定なアルゴリズムを採用しているようです（クイックソートではなく）。

本記事でソートしたシート名では、大文字と小文字のみの違いはありえないのでこれでも問題にはなりませんが、データの配列で使う際は留意してください。

なら、マージソートのなど安定ソートを自作すればすむのかというと、日本語文字列の場合、さらにやっかいな問題があります。

まず、基本的に Excel の日本語照合順序は以下のようなルールに従っていると思われます。（完全な仕様は見つからなかったので間違っているかもしれません）

英字の大文字/小文字を区別しない
英数字の半角/全角を区別しない
日本語仮名の平仮名と片仮名を区別する（片仮名優先）
濁音・半濁音や拗音（ゃゅょ）、促音（っ）等の扱いは日本語辞書順に従う
その他漢字等の文字は JIS コードベースの文字コード比較（おそらく）

ワークシート上での「並べ替え」コマンドでセル範囲の文字列データをソートしたとき、このルールとは、また微妙に異なった並び順の結果になるのです。

これは「並べ替え」コマンドの「大文字と小文字を区別する」設定や、セルのデータより「ふりがな」を優先して照合する、などの要因があるためです。「ふりがな」は本来のデータとは別に、文字列入力したときの仮名漢字変換に使われた仮名が保持されるという、操作に依存したセルの付随データです。

VBA でこれらの挙動を再現したソートをするには、配列データをシート上のセル範囲にいったんコピーして、Range.Sort メソッドか Worksheet.Sort オブジェクトを使うしかないでしょう。

いろいろクセがあってホントにややこしい話です。今試しに、複数の配列データ

"a”,"B","b","A","ア","い","イ","あ","亜","Ａ"（最後の"Ａ"は全角）

をソートしてみたところ、次のような結果になりました。

自作クイックソート（Option Compare Binary（デフォルト））
"A","B","a","b","あ","い","ア","イ","亜","Ａ"
（Unicode 順）
自作クイックソート（Option Compare Text）
""A","Ａ","a","B","b","あ","ア","い","イ","亜"
（辞書順、大小文字区別なし、非安定ソート）
SORT関数/Sortメソッド
"a","A","Ａ","B","b","ア","あ","イ","い","亜"
（辞書順、大小文字区別なし、安定ソート、ふりがな無視）
「並び替え」コマンド（「大文字と小文字を区別する」: OFF（デフォルト））
"a","A","Ａ","B","b","ア","あ","亜","イ","い"
（辞書順、大小文字区別なし、安定ソート、ふりがな優先）
「並び替え」コマンド（「大文字と小文字を区別する」: ON）
"a","A","Ａ","b","B","ア","あ","亜","イ","い"
（辞書順、小文字優先、安定ソート、ふりがな優先）

まとめ

Excel にはワークシートを並べ替える機能がないので、VBA マクロで実装してみました。

思っていたほど簡単ではなく、Excel VBA が持つクセに振り回されてかなり苦戦しました。

やはり、Excel の機能としてシート並べ替えを用意していただいた方がみんな幸せになれそうです。

今エクセルさんはヤル気満々みたいで、どんどん新機能や改良がリリースされています。また、ユーザの要望にも耳も傾けてくれています。

そのうち「ナビゲーション」ペインでもワークシートをソートしたり、ドラッグ移動ができるように改良されるかもしれません。

子犬の目で期待しましょう。

参考資料