新関数SORTBYをVBAで利用するラップ関数を作成
新関数はスピルに対応していてとても便利です。
新関数はVBAからもWorksheetFunctionで利用できます。
シート関数には件数制限があるので使用する場合は注意が必要ですが、その範囲内ならVBAでもとても便利に利用できます。
VBAでシート関数使用時の配列要素数制限
・FILTER関数 ・SORT関数、SORTBY関数 ・UNIQUE関数 ・XLOOKUP関数、HLOOKUP関数、VLOOKUP関数 ・XMATCH関数、MATCH関数 ・TRANSPOSE関数 ・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について
第133回.引数の数を可変にできるパラメーター配列（ParamArray）｜VBA入門
・引数の構文 ・名前付き引数について ・ParamArrayキーワード（パラメーター配列）とは ・ParamArrayキーワード（パラメーター配列）の使用例 ・サイト内の関連ページ
Errオブジェクトについて
第134回.Errオブジェクトとユーザー定義エラー
・Errオブジェクト ・Errオブジェクトのエラー情報が解除されるのは ・Err.Raiseメソッド ・Errorステートメント ・Error関数 ・CVErr関数 ・エラー番号およびエラーメッセージ ・実行時エラー関連記事
参照不可について
省略可能なVariant引数の参照不可をラップ関数で利用
省略可能(Optional)なVariant引数を省略した場合、その引数は「参照不可」となります。この「参照不可」の状態について説明します。そして、これをあえて利用してワークシート関数のラップ関数を作成してみます。
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での短絡評価（ショートサーキット）の使い方
・Select Caseの基本 ・短絡評価（ショートサーキット）について ・Ifステートメントのネストの書き換え ・Select Case での短絡評価（ショートサーキット） ・Select Case を使った短絡評価使用の是非
RangeオブジェクトのFor EachとAreasについて・正解は ・RangeオブジェクトをFor Eachで取得すると ・RangeオブジェクトをItemで取得すると ・RangeオブジェクトのFor EachとAreasの最後に
画像が行列削除についてこない場合の対処複数ブック・複数シートを次々に処理していくようなVBAにおいて、行削除や列削除をした時に、画像がセルの移動についてこない場合があります。プロパティは、「セルに合わせて移動やサイズ変更をする」になっている場合です。プロパティで「セルに合わせて移動する」になっているにも関わらず、画像が行列削除で移動しないといった現象が…
新関数SORTBYをVBAで利用するラップ関数を作成LAMBDA以降の新関数はVBAで使えるか
・TEXTSPLIT関数 ・TEXTBEFORE関数,TEXTAFTER関数 ・TOROW関数,TOCOL関数 ・WRAPROWS関数,WRAPCOLS関数 ・VSTACK関数,HSTACK関数
数字（1～50）を丸付き数字に変換するVBA数値には丸付き数字があります。ただし1～50までしかありません。①②③④⑤⑥⑦⑧⑨⑩⑪⑫⑬⑭⑮⑯⑰⑱⑲⑳㉑㉒㉓㉔㉕㉖㉗㉘㉙ChrW(12…
文字列のプロパティ名でオブジェクトを操作する方法・CallByName関数 ・Propertyについて ・文字列のプロパティ名でオブジェクトを操作するVBAサンプル
OneDrive使用時のThisWorkbook.Pathの扱い方マクロVBAでは、現在動いているマクロVBAが書かれているブックのバス（Path）を取得し、そのフォルダを使って処理を組み立てる方法が良く用いられます。ところがOneDriveと同期しているフォルダでは、このような処理が従来のVBAコードでは行えなくなっています。
セル個数を返すRange.CountLargeプロパティとは・Countプロバティ ・CountLargeプロバティ ・LongLong型について
画像「セルに配置」のVBAについて（365の新機能）・IMAGE関数 ・手動で画像を「セルに配置」 ・「セルに配置」⇔「セルの上に配置」 ・「セルに配置」「セルの上に配置」のVBA ・画像が「セルに配置」されたセルのコピー
VBAでクリップボードへ文字列を送信・取得する3つの方法・VBAの3つの方法について ・1. DataObjectを使うVBA ・2. TextBox を使うVBA ・3. Win32 API を使うVBA
新着記事NEW ・・・新着記事一覧を見る
TRIMRANGE関数（セル範囲をトリム：端の空白セルを除外）｜エクセル入門(2024-08-30)
正規表現関数（REGEXTEST,REGEXREPLACE,REGEXEXTRACT）｜エクセル入門(2024-07-02)
エクセルが起動しない、Excelが立ち上がらない｜エクセル雑感(2024-04-11)
ブール型（Boolean）のis変数・フラグについて｜VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する｜VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次｜エクセルの神髄(2024-03-20)
VBA10大躓きポイント（初心者が躓きやすいポイント）｜VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成｜ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。｜エクセル雑感(2024-02-17)
初級脱出10問パック｜VBA練習問題(2024-01-24)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.繰り返し処理（For Next)｜VBA入門
5.RangeとCellsの使い方｜VBA入門
6.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
7.セルのクリア（Clear,ClearContents）｜VBA入門
8.メッセージボックス(MsgBox関数)｜VBA入門
9.条件分岐（Select Case）｜VBA入門
10.マクロとは？VBAとは？VBAでできること｜VBA入門
- ホーム
- マクロVBA応用編
- マクロVBA技術解説
- 新関数SORTBYをVBAで利用するラップ関数を作成
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。