Excel vba 配列内の最大値を求めたいです。

質問

Excel vba をはじめて1ヵ月程度の初心者ですが、すいません質問させて下さい。
動的配列というのでしょうか？
その配列の中の最大値を求めたいのですが、よくわかりません。
アクティブシート内の図形で最前面以外の図形を消去しようと思ってます。
マクロで最大値の取得ができないので、暫定的にシートに計算させてしまっているのですが、マクロ上で最大値を求める方法がわかりません。

Sub testSZ()

Dim Num As Integer, Sum As Integer, Shp As Shape, Ary() As Variant, Mxm As Long

'++++++++++↓アクティブシート内の図形をカウント:=Sum
Sum = 0
For Each Shp In ActiveSheet.Shapes
Sum = Sum + 1
Next Shp

'++++++++++↓配列の数を決定
ReDim Ary(1, Sum)

'++++++++++↓配列に図形と図形のZオーダーを設定
Num = 0
For Each Shp In ActiveSheet.Shapes
Shp.Select
Set Ary(0, Num) = Shp
Ary(1, Num) = Selection.ShapeRange.ZOrderPosition
Num = Num + 1
Next Shp

'++++++++++↓配列内のZオーダー最大値を取得
Mxm = Application.WorksheetFunction.Max

'++++++++++↓最前面の図形以外を消去
Num = 0
Do
If Mxm > Ary(1, Num) Then
Ary(0, Num).Delete
End If
Num = Num + 1
Loop Until Sum = Num

Erase Shp

End Sub

Application.WorksheetFunction.Max[]
の[]にAryやAry(1,Num)をやってみたりしたのですが、
できないです。

どなたか御教授おねがい致します！！

回答No.4

fujillin · ベストアンサー

配列内の最大値などを求めるのは、通常は、全体をなめるしかないと思いますが、ご質問のケースですと、記録するときに同時に探してしまえば、新しくループをしなくてもすむでしょう。（例えば下の一行を追加）

Ary(1, Num) = Selection.ShapeRange.ZOrderPosition
If Ary(1, Num) > Mxm Then Mxm = Ary(1, Num)　'←この行を挿入
Num = Num + 1

目的が最前面の図形以外を消去するだけならば、↑で同時に最大値の時の番号（Num）も記憶しておいて、消去するループではその番号以外を消去してあげればよろしいかと…

Shape(i)はZorder順に並んでいるみたいなので（For Eachでも同じ順みたい）、図形の消去だけが目的なら、別の考え方ですが、以下のように一回のループで配列を利用しなくても可能かと思います。（未検証）
flag = False '←最前面Shapeの検索フラグ
For i = ActiveSheet.Shapes.Count To 1 Step -1
If (Shpeの検索条件に合致するか) Then
　 If flag Then Shapes(i).Delete Else flag = True
End If
Next i

お礼

御回答ありがとうございます！！
やはり、多次元の配列の最大値を求める場合は総当たりしかないという事ですね！わかりました！！
やたら繰り返し構文が多いコードでしたので、fujillinさんの御指導で、マクロ高速化になりそうです！！

For Each 構文や Do Loop 構文が、Shape(i)がZorder順なのかどうかですが、codename順（sheets()のようにcodenameが取得できませんが、いわゆる新しい図形順）なのか、Zorder順なのかで未検証のままでした。他の要素も絡んでくると大変なので、手を付けてませんでした。確かにこれが明確ならば、かなりコードも単純化しそうですね！！

補足

（お礼の後の補足です）
Sub testTurn()
Dim Shp As Shape
For Each Shp In ActiveSheet.Shapes
Shp.Select
Application.Wait [NOW()+"0:00:01"]
Next Shp
End Sub

fujillinさんの御指摘があったので、検証してみたら、For Each 構文や Do Loop 構文もZorder順で選択しているみたいです。(Excel 2007)
fujillinさん、マクロが単純化できそうです！どうもありがとうございます！！

あとすいません。勘違いしてました。多次元の配列の最大値を求める場合もApplication.WorksheetFunction.Max(Ary)で出来ました。
今回の場合は図形が配列にSetされていたからダメだとわかりました。訂正します。。。

回答No.5

Wendy02

こんばんは。

VBAの教科書では、オブジェクトを確保するのは、配列にするよりも、コレクション・オブジェクトにするというように書いてあるかとは思います。

　　Dim colShp As New Collection
　　　　For Each shp In ActiveSheet.Shapes
　　　　colShp.Add shp
　　Next

このように確保します。
しかし、もともと、Shapes は、コレクションですから、Index を取っていけば、それで、ZOrderPosistion と一致しているはずですから、以下のようにすればよいと思います。オブジェクトですから、ひとつずつ削除するのではなく、一括して削除します。

Sub TestSample()
　　Dim i As Long
　　Dim mx As Long
　　With ActiveSheet
　　　　mx = .Shapes.Count
　　　　If mx = 1 Then Exit Sub　'既に処理した場合は解除
　　　　.Shapes(1).Select '誤動作を避けるため
　　　　For i = 1 To mx - 1
　　　　　　.Shapes(i).Select False
　　　　Next i
　　End With
　　Selection.Delete
End Sub

なお、配列の最大値を取る方法には、裏技があって、ふつうは、１次元配列で、ワークシートのMax を取ります。検討してみてください。

お礼

御回答ありがとうございます！
なるほど、ZOrderPosition と Index がいっしょなのですね！！
New Collection というのがあるのですか。。。
やっと少しVBAをさわるのに慣れてきましたが、
まだまだ教科書的な基本事を知らないままですね。
Wendy02さんの描いて頂いたコードがスッキリまとめてくれていて、わかり安くて良いですね！！
どうもありがとうございます！！

回答No.3

myRange

回答２です。
戯けた回答をしてました。
objectが入ってますね。

で、素直に比較していったらどうでしょうか。
-------------------------------------
　Dim myMax, i

　myMax = ary(1, 0)

　For i = 1 To sum
　　　If ary(1, i) > myMax Then myMax = ary(1, i)
　Next i

　MsgBox myMax
-----------------------------------------

お礼

出来ました！！
配列をさらに変数に代入させるという事が、考えつきませんでした。
myRangeさんの御指摘の比較の総当たり方法で成功しました！！！
コードも描いて頂きありがとうございます！！

配列をさらに変数に代入させるというアドバイスも頂いたので、
ある意味、配列をわざわざ二次元にしなくとも二つの配列に増やしてもいいのではと思い、
配列を Ary1(0, Num) ←Shape を 格納 と Ary2(0, Num) ← ZOrderPosition を格納で
Mxm = Application.WorksheetFunction.Max(Ary2)
でもコードが動きました！！

出来て嬉しいです！ありがとうございます！！！

回答No.2

myRange

これではどうでしょう

Mxm = WorksheetFunction.Max(Ary, 1)

外しましたらご容赦！

お礼

御回答ありがとうございます！
Mxm = WorksheetFunction.Max(Ary, 1)
の 『, 1』は何でしょうか？？
myRangeさんの御指摘で、
配列に図形が入っているとできないとう事がわかりました！
ありがとうございます！！

回答No.1

nattocurry

ActiveSheet.Shapes.Count
でどうでしょう？

補足

早速の御回答ありがとうございます！
すいません、ActiveSheet.Shapes.Count は突っ込まれるかと思いました。説明も不足してました。
あと、Erase Shp は Erase Ary です。すいません。。。
ActiveSheet.Shapes.Count を使わないのは、Shape 自体にいくつかの分類を作り名前を与えています。
（For each next の構文内に If Shp.name Like "～～" で指定）
複数の特定の同名を付けられた図形の中で、というのと、別のマクロで ZOrderPosition を逐一変えてしまっている、という点があり、話を複雑にしてしまっています。
今のところ配列内の最大値をマクロで求めたいと思っています。