ExcelVBA functionについて

Excel VBAでfunctionを勉強しています。
やりたいことは、二次元配列の並べ替えです。
ID順に（昇順）に並べ替えです
ちなみに、一次元配列の並べ替えは、下記のようにしました。
戻り値を受け取る方法です。

Sub aaa()

Dim z As Variant
Dim a() As Variant
Dim j As Long, i As Long
maxrow = Cells(Rows.Count, "A").End(xlUp).Row
a = Application.Transpose(Range("A1:A22").Value)
z = SortArray(a(), 0)
For i = 1 To maxrow
For j = 0 To UBound(z)
Cells(i, 2) = z(i)

Next j
Next i
End Sub

Function SortArray(X() As Variant, Optional s As Long = 0) As Variant

Dim k As Long
Dim ub As Long
Dim y() As Variant

ub = UBound(X)
ReDim y(ub)

For k = 1 To ub

If s = 0 Then
y(k) = WorksheetFunction.Small(X(), k)

Else
y(k) = WorksheetFunction.Large(X(), k)

End If

Next k

SortArray = y()

End Function

このコードを参考に、二次元配列の並べ替えを考えました。


トンチンカンな質問かもしれませんが

考えたことは、

1.Functionに範囲を渡して並べ替えをして、結果をSubに返す
2.配列の戻り値をSubに返す。

それで、下記のコードを考えましたが、途中で分からなくなりました。


Sub 並べ替え()

Dim myarr() As Variant
myarr = Range("A1:B22")

ここのコードがわからないです。

End Sub


Function myArray(myRange As Range) As Variant
Dim i As Long, j As Long



For i = LBound(myarr, 1) To UBound(myarr, 1)
For j = LBound(myarr, 2) To UBound(myarr, 2)

ここのコードがわからないです。


Next j
Next i

End Function

配列の戻り値でできなくて、範囲を渡すほうでも考えましたができまでんでした。


お手数ですが、ご教示をお願いします。

No.3 ベストアンサー 回答者： めぐみん_ 回答日時： 2021/07/27 15:57

初級レベルなジジィなので小難しい事に脳が追いつきませんので、結果が出たコードを提示するのみにします。

Sub megu()

Range("D1").Resize(13, 2).Value = Sorted(Range("A1:B13"))

End Sub

Function Sorted(a As Range) As Variant
Dim i As Integer
Dim n As Integer
Dim b As Variant, c As Variant

b = a.Value
c = b

For i = 2 To UBound(b, 1)

n = WorksheetFunction.Rank(b(i, 1), a, 1)

c(n + 1, 1) = n
c(n + 1, 2) = b(i, 2)

Next

Sorted = c

End Function

この回答へのお礼

めぐみんさん。
いつもありがとうございます。
女性かと思っていました。
コードありがとうございます

お礼日時：2021/07/27 22:10

No.4 回答者： めぐみん_ 回答日時： 2021/07/27 17:54

No.3です。

＞c(n + 1, 1) = n

c(n + 1, 1) = b(i, 1)

が良いのかな。

No.2 回答者： Zincer 回答日時： 2021/07/27 13:55

Sub・・・End Sub

Function・・・End Function
のような操作の流れをプロシージャーと呼びます。
どちらも引数（＝（）内の変数）を渡すことができるのですが、大きな違いはFunctionプロシージャーが戻り値を返すことができることです。
ですから、
＞1.Functionに範囲を渡して並べ替えをして、結果をSubに返す
＞2.配列の戻り値をSubに返す。
おそらく２は「並べ替えるべき配列をFunctionへ渡し」が省略されているものと思いますが、この２つでは大きな差が生じます。
１の場合（範囲を渡す）
myarr = Range("A1:B22")
z = myArray(myarr, 0)
のようにして
Function myArray(myRange As Range) As Variant
を作るような構文になりますが、

２の場合（配列を渡す）
a = Application.Transpose(Range("A1:B22").Value)’ここで渡すべき配列を準備
z = myArray(a())
のようにして
Function myArray(X() As Variant, Optional s As Long = 0) As Variant
を作ることになります。
お気づきになりました？
質問の構文はどっち付かずなのです。
また、残念なお知らせがあります。
参考にした
WorksheetFunction.Small(X(), k)
WorksheetFunction.Large(X(), k)
は小さい方から（または大きい方から）何番目の値を返す関数ですので利用できません。
小さい方から（または大きい方から）何番目のID値（＝ソートキー）を持つ値（ここでは名前）を得なければいけないのです。
Functionの勉強中ということですので、とりあえずこの程度にしておきますが、「Subプロシージャに引数として範囲を渡し、その範囲をID順に並べ替える」というSubプロシージャも作ることができるのです。

以下、老婆心
maxrow = Cells(Rows.Count, "A").End(xlUp).Row　’A列の最終行を検出
a = Application.Transpose(Range("A1:A22").Value)’A1:A22を配列に収納
z = SortArray(a(), 0)’関数SortArrayに引数として渡し、ソートされた結果を配列ｚに収納
For i = 1 To maxrow’なぜ最終表まで？
For j = 0 To UBound(z)’なぜ配列Ｚの因子の数？
Cells(i, 2) = z(i)’　i、jのどちらか一方で十分では？
Next j
Next i

この回答へのお礼

回答ありがとうございました。
もう一度勉強します。
解説ありがとうございました。

お礼日時：2021/07/27 22:08

No.1 回答者： fujillin 回答日時： 2021/07/27 12:05

こんにちは

「二次元配列の並び替え」って、どのような内容を考えているのでしょうか？
添付の画像を見ると、なんとなく、キー（列または行）を指定して、それに従って並べ変えることのように思われますけれど・・・

もしも、添付画像のようなデータを「ID順に並べ替えたい」とか、「氏名順に並べ替えたい」といったことを意味しているのなら、そのままシート上で並べ替えを行ってしまった方が簡単です。
（Sortメソッドが用意されていますので・・・）

※ 意味が違っていたなら、スルーしてください。

この回答へのお礼

回答ありがとうございました。

お礼日時：2021/07/27 22:05