VBA技術解説
新関数SORTBYをVBAで利用するラップ関数を作成

ExcelマクロVBAの問題点と解決策、VBAの技術的解説
公開日:2021-06-12 最終更新日:2021-06-12

新関数SORTBYをVBAで利用するラップ関数を作成


新関数はスピルに対応していてとても便利です。
新関数はVBAからもWorksheetFunctionで利用できます。
シート関数には件数制限があるので使用する場合は注意が必要ですが、その範囲内ならVBAでもとても便利に利用できます。

VBAでシート関数使用時の配列要素数制限
・FILTER関数 ・SORT関数、SORTBY関数 ・UNIQUE関数 ・XLOOKUP関数、HLOOKUP関数、VLOOKUP関数 ・XMATCH関数、MATCH関数 ・TRANSPOSE関数 ・VBAでシート関数使用時の配列要素数制限まとめ

その中でも、そもそもVBAに機能がなく代替実装が面倒なものにSORT関数とSORTBY関数があります。
SORT関数は、引数が単純な指定なのでそのまま使用できますが、
SORTBY関数は、引数で基準配列を作成する必要があり、この部分が少々面倒になります。


そこで、VBAでWorksheetFunction.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での短絡評価(ショートサーキット)の使い方
RangeオブジェクトのFor EachとAreasについて
画像が行列削除についてこない場合の対処
新関数SORTBYをVBAで利用するラップ関数を作成
LAMBDA以降の新関数はVBAで使えるか
数字(1~50)を丸付き数字に変換するVBA
文字列のプロパティ名でオブジェクトを操作する方法
OneDrive使用時のThisWorkbook.Pathの扱い方
セル個数を返すRange.CountLargeプロパティとは
画像「セルに配置」のVBAについて(365の新機能)


新着記事NEW ・・・新着記事一覧を見る

VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)
スピらない スピル数式 スピらせる|エクセル雑感(2023-12-06)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ