新関数SORTBYをVBAで利用するラップ関数を作成
新関数はスピルに対応していてとても便利です。
新関数はVBAからもWorksheetFunctionで利用できます。
シート関数には件数制限があるので使用する場合は注意が必要ですが、その範囲内ならVBAでもとても便利に利用できます。
その中でも、そもそもVBAに機能がなく代替実装が面倒なものにSORT関数とSORTBY関数があります。
SORT関数は、引数が単純な指定なのでそのまま使用できますが、
SORTBY関数は、引数で基準配列を作成する必要があり、この部分が少々面倒になります。
実務ではバージョンの問題と件数制限の問題があり、なかなか使用できませんが、
とはいえ、これからはVBAで新関数を使う機会も増えてくると思いますので、あくまで一例になりますがVBAサンプルを掲載しておきます。
WorksheetFunction.SortByのラップ関数
Function SortByX(ByRef aAry, ParamArray aPrm()) As Variant
On Error GoTo ExitFnc
'2次元配列専用
If getDimension(aAry) <> 2 Then GoTo ExitFnc
'バラメーター未指定or11以上、ParamArrayは0開始
If UBound(aPrm) < 0 Then GoTo ExitFnc
If UBound(aPrm) >= 10 Then GoTo ExitFnc
Dim i As Long, vOpt()
'基準配列と順序を「参照不可」で省略値にする：最大5組
ReDim vOpt(9)
For i = LBound(vOpt) To UBound(vOpt)
vOpt(i) = UnRef
Next
'ParamArrayからSortByのKeyとOrderを作成
For i = LBound(aPrm) To UBound(aPrm)
'偶数位置は基準配列の指定、奇数は順序
If i Mod 2 = 0 Then
vOpt(i) = makeCriteria(aAry, aPrm(i))
If Not IsArray(vOpt(i)) Then GoTo ExitFnc
Else
'1:昇順、-1:降順、以外は省略値とする
If CStr(aPrm(i)) = "1" Or CStr(aPrm(i)) = "-1" Then
vOpt(i) = aPrm(i)
End If
End If
Next
'SortBy(配列,基準配列1,[並べ替え順序1],[基準配列2,[並べ替え順序2,...)
'基準配列が、縦配列なら縦、横配列なら横、で並べ替えられます。
SortByX = WorksheetFunction.SortBy(aAry, vOpt(0), vOpt(1), vOpt(2), vOpt(3), vOpt(4), vOpt(5), vOpt(6), vOpt(7), vOpt(8), vOpt(9))
Exit Function
ExitFnc:
'Err.Raise等お好みで
MsgBox "指定エラー"
SortByX = aAry
End Function
'基準配列作成
Function makeCriteria(ByRef aAry, aPrm)
If IsArray(aPrm) Then
'配列ならそのまま
makeCriteria = aPrm
ElseIf IsNumeric(aPrm) Then
'数値なら配列から基準配列を作成
If TypeName(aPrm) = "String" Then
'文字列型なら指定行で横並べ替え
makeCriteria = WorksheetFunction.Index(aAry, aPrm, 0)
Else
'数値型なら指定列で縦並べ替え
makeCriteria = WorksheetFunction.Index(aAry, 0, aPrm)
End If
Else
'以外は指定エラー
makeCriteria = False
End If
End Function
'配列の次元数取得：配列以外は0が返る
Function getDimension(v As Variant) As Integer
On Error GoTo ExitFnc
Dim iDim As Integer
Do
getDimension = getDimension + 1
iDim = UBound(v, getDimension)
Loop
ExitFnc:
getDimension = getDimension - 1
End Function
'参照不可を作成
Function UnRef(Optional arg)
UnRef = arg
End Function
「SortByX」この名前には特に意味はありません。
適当にXだけ付けておきました。
引数を増やしたくないのと、そもそも横の並べ替え（列単位）は滅多にやらないと思うので、
基準配列の指定時に、
・文字列型なら指定行で横並べ替え
・数値型なら指定列で縦並べ替え
このようにしてみました。
縦配列なら縦、横配列なら横、で並べ替えられます。
ParamArrayについて
Errオブジェクトについて
参照不可について
SortByのラップ関数の使用例
Sub test()
Dim ary: ary = Range("A1:J10")
'指定列で縦に並べ替え：2列目昇順、1列目降順
Range("L1:U10") = SortByX(ary, 2, 1, 1, -1)
'指定行で横に並べ替え：2行目昇順、1行目降順
Range("L1:U10") = SortByX(ary, "2", 1, "1", -1)
'指定順で縦に並べ替え
Range("L1:U10") = SortByX(ary, [{4;3;5;6;2;10;4;1;9;5}], 1)
'指定順で横に並べ替え
Range("L1:U10") = SortByX(ary, [{4,3,5,6,2,10,4,1,9,5}], 1)
'指定列と指定順で縦に並べ替え
Range("L1:U10") = SortByX(ary, 2, 1, [{4;3;5;6;2;10;4;1;9;5}], 1)
'縦横混在はエラーになります
Range("L1:U10") = SortByX(ary, 2, 1, [{4,3,5,6,2,10,4,1,9,5}], 1)
End Sub
{4;3;5;6;2;10;4;1;9;5}、これは縦配列になります。
{4,3,5,6,2,10,4,1,9,5}、これは横配列になります。
スピル対応のエクセルなら、={…}でいれてみると見た目で分かります。
同じテーマ「マクロVBA技術解説」の記事
Select Caseでの短絡評価（ショートサーキット）の使い方
RangeオブジェクトのFor EachとAreasについて
画像が行列削除についてこない場合の対処
新関数SORTBYをVBAで利用するラップ関数を作成
LAMBDA以降の新関数はVBAで使えるか
数字（1～50）を丸付き数字に変換するVBA
文字列のプロパティ名でオブジェクトを操作する方法
OneDrive使用時のThisWorkbook.Pathの扱い方
セル個数を返すRange.CountLargeプロパティとは
画像「セルに配置」のVBAについて（365の新機能）
VBAでクリップボードへ文字列を送信・取得する3つの方法
新着記事NEW ・・・新着記事一覧を見る
AIは便利なはずなのに…「AI疲れ」が次の社会問題になる｜生成AI活用研究(2026-02-16)
カンマ区切りデータの行展開｜エクセル練習問題(2026-01-28)
開いている「Excel/Word/PowerPoint」ファイルのパスを調べる方法｜エクセル雑感(2026-01-27)
IMPORTCSV関数（CSVファイルのインポート）｜エクセル入門(2026-01-19)
IMPORTTEXT関数（テキストファイルのインポート）｜エクセル入門(2026-01-19)
料金表（マトリックス）から金額で商品を特定する｜エクセル練習問題(2026-01-14)
「緩衝材」としてのVBAとRPA｜その終焉とAIの台頭｜エクセル雑感(2026-01-13)
シンギュラリティ前夜：AIは機械語へ回帰するのか｜生成AI活用研究(2026-01-08)
電卓とプログラムと私｜エクセル雑感(2025-12-30)
VLOOKUP/XLOOKUPが異常なほど遅くなる危険なアンチパターン｜エクセル関数応用(2025-12-25)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
8.マクロとは？VBAとは？VBAでできること｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.メッセージボックス(MsgBox関数)｜VBA入門
- ホーム
- マクロVBA応用編
- マクロVBA技術解説
- 新関数SORTBYをVBAで利用するラップ関数を作成
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。
This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.