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

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

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


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

VBAでシート関数使用時の配列要素数制限
VBAでワークシート関数が使えるのはとても便利です。WorksheetFunctionのシート関数を使う事は多いですが、配列を引数に指定した場合は要素数に制限があります。この制限があることは、ワークシート関数だという事を考えれば仕方ないのかもしれません。

その中でも、そもそも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入門
Subプロシージャー、Functionプロシージャーにおいて、引数リストの数を特定せず、不定個数の引数を渡せるよう可変にしたい場合があります。ワークシートの関数では、引数の個数が不定の関数が多数あります。=SUM(数値1,数値2,...) このように、最後が「,...」となっていて、いくつでも(限度はありますが)指…

Errオブジェクトについて

第134回.Errオブジェクトとユーザー定義エラー
VBA実行時には種々のエラーが発生します。実行時エラーに関する情報は、Errオブジェクトには入っていますので、VBA実行でエラー発生した場合は、Errオブジェクトを参照しエラー内容を調べることになります。Errオブジェクトの使い方と、ユーザー定義エラーの生成方法について解説します。

参照不可について

省略可能な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技術解説」の記事

列幅・行高をDPI取得しピクセルで指定する
VBAでWMIの使い方について
アクティブシート以外のWindowを設定できるWorksheetView
LSetとユーザー定義型のコピー(100桁の足し算)
省略可能なVariant引数の参照不可をラップ関数で利用
ブックのいろいろな開き方(GetObject,参照設定,アドイン)
入力規則への貼り付けを禁止する
Select Caseでの短絡評価(ショートサーキット)の使い方
RangeオブジェクトのFor EachとAreasについて
画像が行列削除についてこない場合の対処
新関数SORTBYをVBAで利用するラップ関数を作成


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

還暦のVBA:VBAまでたどりつけるか… (2021-09-29)
VLOOKUPを使うことを基本としてシートを設計すべきか|エクセル雑感(2021-08-17)
コンピューターはブラックボックスで良い|エクセル雑感(2021-08-14)
小文字"abc"を大文字"ABC"に変換する方法|エクセル雑感(2021-08-13)
ADOでテキストデータを集計する|VBAサンプル集(2021-08-04)
VBA学習のお勧めコース|エクセル雑感(2021-08-01)
エクセル馬名ダービー|エクセル雑感(2021-07-21)
在庫を減らせ!毎日棚卸ししろ!|エクセル雑感(2021-07-05)
日付型と通貨型のValueとValue2について|エクセル雑感(2021-06-26)
DXってなんだ? ITと何が違うの?|エクセル雑感(2021-06-24)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.Excelショートカットキー一覧|Excelリファレンス
3.変数宣言のDimとデータ型|VBA入門
4.RangeとCellsの使い方|VBA入門
5.繰り返し処理(For Next)|VBA入門
6.マクロって何?VBAって何?|VBA入門
7.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
8.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
9.セルに文字を入れるとは(Range,Value)|VBA入門
10.並べ替え(Sort)|VBA入門




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


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



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