新関数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 ・・・新着記事一覧を見る
第5章：AI×VBAでつまづかない！トラブルシューティングとAIとの付き合い方
｜生成AI活用研究(2025-05-20)
第4章：【事例で学ぶ】AIとVBAでExcel作業を劇的に効率化する！
｜生成AI活用研究(2025-05-20)
第3章：AIを「自分だけのVBA先生」にする！質問・相談の超実践テクニック｜生成AI活用研究(2025-05-19)
第2章 VBAって怖くない！Excelを「言葉で動かす」（超入門）｜生成AI活用研究(2025-05-18)
第1章：AIって一体何？あなたのExcel作業をどう変える？（AI超基本）｜生成AI活用研究(2025-05-18)
AI時代のExcel革命：AI×VBAで“書かない自動化”超入門｜生成AI活用研究(2025-05-17)
Geminiと100本ノック 23本目：シート構成の一致確認｜生成AI活用研究（5月16日）
AIが問う出版の未来は淘汰か進化か：AIと書籍の共存の道とは｜生成AI活用研究(2025-05-16)
Geminiと100本ノック 22本目：FizzBuzz発展問題｜生成AI活用研究（5月15日）
すぐに使える！生成AI プロンプト作成 実践ガイド｜生成AI活用研究(2025-05-15)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.繰り返し処理（For Next)｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
5.ひらがな⇔カタカナの変換｜エクセル基本操作
6.RangeとCellsの使い方｜VBA入門
7.メッセージボックス(MsgBox関数)｜VBA入門
8.セルのクリア（Clear,ClearContents）｜VBA入門
9.FILTER関数（範囲をフィルター処理）｜エクセル入門
10.条件分岐（Select Case）｜VBA入門
- ホーム
- マクロVBA応用編
- マクロVBA技術解説
- 新関数SORTBYをVBAで利用するラップ関数を作成
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。