【Excel VBA】Sort メソッドの CustomOrder

結論を先に書きますと、
Excel VBA の Sort メソッドで CustomeOrder を使う場合、カンマで繋げた文字列を一旦変数に格納してから指定すると、255文字を超える場合はエラーになってしまうので、変数に格納せずに直接指定した方がいいですよ、という話です。
まぁ、私に言われなくても、普通はそうするんですかね…。

Excel VBA で、特定の範囲を並べ替えする必要がありました。
で、そのときに、普通に昇順とか降順とかじゃなくて、

6 → 150 → 11 → 3 → 9 → 10 → 113 → 360 → … (全部で140個くらいある)

のように、順番を指定する必要がありました。
『Sort メソッド』というやつです。
VBA でない普通の Excel で使う並べ替えでも、並べ替えのウィンドウで『順序』のところから『ユーザー設定リスト』を選ぶと、その順番を指定する画面になるので、そこで上のように順番を指定してあげれば、その順番に並べ替えることができます。
それを VBA で実現しようとすると、『CustomOrder』という引数で指定することになります。
Sort メソッドの詳細は以下がわかりやすいと思います。
大好きな Office TANAKA さんです。

Office TANAKA - Excel VBA Tips[独自の基準で並べ替える]

CustomeOrder はカンマで繋いだものを指定すればいいみたいだったので、

順番 = "6,150,11,3,9,10,113,360,…(140個くらいを全部書く)"
(省略)
… , CustomOrder:=順番

のように、一旦文字列として変数に入れて、それを CustomeOrder で指定しました。
そしたら、エラーになったんですよね…。
なんでかわからなくていろいろやってみたんですが、どうやら上でいうところの文字列『順番』の文字数が255を超えているからだめみたいで。
順番の指定は、上記に書いた通り140個くらいあったので、全部をカンマでつなげた文字列の文字数は550文字を超えていました。
文字数を255文字まで減らしたらイケたんですね。
じゃぁ、どうしようかな…と思って、仕方ないので1つ設定っぽいシートを作ってそこに縦に書きました。
そして、

CustomOrder:=Application.WorksheetFunction.TextJoin(",", True, Range(Cells(1, 1), Cells(1, 1).End(xlDown)))

のように、文字列として変数に入れないで直接 CustomeOrder で指定してしまえばイケました。
シートに書く場所を作る必要がありましたが、まぁ他のものも一緒にそのシートに書くことにしたのでいいかな、と。

今回みたいに CustomeOrder を使って並べ替えの順番を指定したのは初めてだったので、いろいろ勉強になりました。

もう30年近く Excel を使っていますが、まだまだわからないことだらけですね…。
最近は Google スプレッドシートの使用頻度がかなり高くなっていますが、それでもやっぱり Excel が大好きです。
「VBA なんてもうすぐ無くなる」ってずっと言われてきましたけど、まだ使うことができて本当に嬉しいです。
このまま使い続けられるといいなー。