Series オブジェクトを PlotOrder プロパティでソートする必要があった話をした．今回，Series オブジェクトをソートするために Collection オブジェクトに代入したのだが，配列の最終要素を取得するところで実行時エラーとなり，解決していない．
Series オブジェクトをソートするための方法としては，元のデータで比較する方法と，Series オブジェクトの最終 Point オブジェクトの Top プロパティを比較する方法がある．
意味としてはどちらも同じだが，どうせなら元のデータで比較するのが王道と思われたのでそちらを試したのだが，今の自分にはスキル不足で手に負えなかった．悔しい．
Collection オブジェクトに代入した Series オブジェクトから Values プロパティを取得することは不可能
理由は不明だが，Series オブジェクトを Collection オブジェクトに代入してしまうと Variant 型配列である Values プロパティの個々の要素を取得することはできなかった．これが，直接 Series オブジェクト経由でなら取得できるのは不可解である．
Dim mySeries As Series
Dim k As Long
Dim myColl As Collection
Dim p As Long
Dim q As Long
Set myColl = New Collection
For Each mySeries In .SeriesCollection
With mySeries
Set myPoint = .Points.Item(.Points.Count)
Debug.Print .Name, .Values(6)
myColl.Add Item:=mySeries
End With
Next mySeries
For p = 1 To myColl.Count
'Debug.Print myColl(p).Name, TypeName(myColl(p).XValues), TypeName(myColl(p).Values)
'Debug.Print myColl(p).Name, myColl(p).Points(1).Top, myColl(p).Points(6).Top
'Debug.Print myColl(p).Name, VarType(myColl(p).Values), LBound(myColl(p).Values), UBound(myColl(p).Values)
'Debug.Print myColl(p).Values(UBound(myColl(p).Values))
'実行時エラー '451': Property Letプロシージャが定義されておらず，Property Getプロシージャからオブジェクトが返されませんでした
For q = myColl.Count To p Step -1
Debug.Print myColl(p).Values(6)
Next q
Next p
上記のコードのうち，11 行目は正常に動作する．しかし，20 行目と 23 行目は実行時エラー ‘451’ が発生する．
エラーが発生してコードが停止した時点でローカルウィンドウを見てみる．Values プロパティは Variant 型の配列になっており，要素に値が格納されているのが分かる．
配列を Variant 型変数に代入すると取得できる
その後のテストで，Collection の要素である Series オブジェクトの Values プロパティを Variant 型の変数に代入すると，ループにより各要素を取得できた．下記のテストコードには 2 つの Debug.Print があるが，どちらも動作する．
Dim r As Long
Dim myVar As Variant
Dim myAr As Variant
r = 1
For Each myVar In myColl(p).Values
Debug.Print r, myVar
r = r + 1
Next myVar
myAr = myColl(p).Values
For r = LBound(myAr) To UBound(myAr)
Debug.Print r, myAr(r)
Next r
この線で進めるなら Variant 型変数をもう一つ宣言してバブルソートすればよい．
バブルソートは VBA Collectionのバブルソート ～ 単純実装からリフレクションを使った汎用化まで から引用した．
Dim myAr1 As Variant
Dim myAr2 As Variant
For p = 1 To myColl.Count
myAr1 = myColl(p).Values
myAr1 = myAr1(UBound(myAr1))
For q = myColl.Count To p Step -1
myAr2 = myColl(q).Values
myAr2 = myAr2(UBound(myAr2))
Debug.Print myAr1, myAr2
If myAr1 > myAr2 Then
CollectionSwap myColl, p, q
End If
Next q
Next p
Sub CollectionSwap(C As Collection, Index1 As Long, Index2 As Long)
Dim Item1 As Variant
Dim Item2 As Variant
If IsObject(C.Item(Index1)) Then
Set Item1 = C.Item(Index1)
Else
Let Item1 = C.Item(Index1)
End If
If IsObject(C.Item(Index2)) Then
Set Item2 = C.Item(Index2)
Else
Let Item2 = C.Item(Index2)
End If
C.Add Item1, after:=Index2
C.Remove Index2
C.Add Item2, after:=Index1
C.Remove Index1
End Sub
ソートの結果
下記のテストコードをソート前とソート後に置いて，結果を比較する．
For r = 1 To myColl.Count
myVar = myColl(r).Values
myVar = myVar(UBound(myVar))
Debug.Print r, myColl(r).Name, myVar
Next r
さて，結果であるが，少し不可解なものとなった．例として沖縄県の結果を示す．なんとなく昇順に見えるが，所々おかしい．
1 多良間村 738 2 伊是名村 1112 3 北大東村 487 4 久米島町 4665 5 渡名喜村 219 6 粟国村 516 7 座間味村 570 8 渡嘉敷村 641 9 伊平屋村 878 10 南大東村 1039 11 東村 1222 12 与那国町 1421 13 北中城村 16181 14 西原町 29299 15 大宜味村 2219 16 金武町 11249 17 本部町 10812 18 伊江村 2418 19 国頭村 3011 20 竹富町 3752 21 宜野座村 6495 22 今帰仁村 8825 23 恩納村 11926 24 嘉手納町 12191 25 与那原町 18715 26 中城村 25179 27 北谷町 29938 28 八重瀬町 32308 29 読谷村 40467 30 宮古島市 41289 31 豊見城市 69660 32 石垣市 45417 33 名護市 61543 34 南風原町 42609 35 南城市 44923 36 糸満市 57254 37 宜野湾市 102606 38 浦添市 117418 39 うるま市 117934 40 沖縄市 148791 41 那覇市 300368
こちらはソート前である．
1 与那国町 1421 2 竹富町 3752 3 多良間村 738 4 八重瀬町 32308 5 久米島町 4665 6 伊是名村 1112 7 伊平屋村 878 8 北大東村 487 9 南大東村 1039 10 渡名喜村 219 11 粟国村 516 12 座間味村 570 13 渡嘉敷村 641 14 南風原町 42609 15 与那原町 18715 16 西原町 29299 17 北中城村 16181 18 北谷町 29938 19 嘉手納町 12191 20 読谷村 40467 21 伊江村 2418 22 金武町 11249 23 恩納村 11926 24 本部町 10812 25 今帰仁村 8825 26 東村 1222 27 大宜味村 2219 28 国頭村 3011 29 南城市 44923 30 宮古島市 41289 31 うるま市 117934 32 豊見城市 69660 33 沖縄市 148791 34 糸満市 57254 35 名護市 61543 36 浦添市 117418 37 石垣市 45417 38 宜野湾市 102606 39 那覇市 300368 40 宜野座村 6495 41 中城村 25179
おそらくどこかでコードの転記ミスをしている．
Series オブジェクトの PlotOrder プロパティを変更する方法は？
もう少し考察を進めて，データ系列を示す Series オブジェクトにおいては .NewSeries メソッドで追加された順に .PlotOrder プロパティが増えていくことを思い出そう．PlotOrder プロパティは SeriesCollection のインデックスにもなっている．
事実，Series オブジェクトの PlotOrder プロパティを変更することでデータ系列の順番を変更することもできる．これをソートに使えないだろうか．
Dim k As Long
'Dim myColl As Collection
Dim p As Long
Dim q As Long
Dim r As Long
Dim myVar As Variant
Dim myAr As Variant
Dim myVar1 As Variant
Dim myVar2 As Variant
Dim myAr1 As Variant
Dim myAr2 As Variant
Dim myLong1 As Long
Dim myLong2 As Long
For p = 1 To .SeriesCollection.Count - 1
Set myVar1 = .SeriesCollection(p) 'Series
myAr1 = myVar1.Values 'Variant()
myLong1 = myAr1(UBound(myAr1)) 'Item
'Debug.Print p, myLong1
For q = .SeriesCollection.Count To p + 1 Step -1
Set myVar2 = .SeriesCollection(q) 'Series
myAr2 = myVar2.Values 'Variant()
myLong2 = myAr2(UBound(myAr2)) 'Item
'Debug.Print q, myLong2
If myLong1 > myLong2 Then
'(6)-1
'Set myVar = myVar1
'Set myVar1 = myVar2
'Set myVar2 = myVar
'(6)-2
myCht.SeriesCollection(p).PlotOrder = q
'(6)-3
'myCht.SeriesCollection(q).PlotOrder = p
'(6)-4
'.SeriesCollection(p) = myVar1
'.SeriesCollection(q) = myVar2
End If
Next q
Next p
For Each mySeries In .SeriesCollection
Debug.Print mySeries.PlotOrder, mySeries.Name, mySeries.Values(6)
Next mySeries
(6)-2 の結果
これも結果がおかしい．何となく昇順に見える程度で，実際にはめちゃくちゃだ．
1 東村 1222 2 宜野座村 6495 3 中城村 25179 4 国頭村 3011 5 渡嘉敷村 641 6 北大東村 487 7 多良間村 738 8 今帰仁村 8825 9 久米島町 4665 10 南大東村 1039 11 渡名喜村 219 12 粟国村 516 13 座間味村 570 14 伊平屋村 878 15 伊是名村 1112 16 伊江村 2418 17 八重瀬町 32308 18 大宜味村 2219 19 与那国町 1421 20 竹富町 3752 21 本部町 10812 22 金武町 11249 23 恩納村 11926 24 宮古島市 41289 25 南風原町 42609 26 嘉手納町 12191 27 与那原町 18715 28 北中城村 16181 29 西原町 29299 30 北谷町 29938 31 読谷村 40467 32 南城市 44923 33 宜野湾市 102606 34 石垣市 45417 35 豊見城市 69660 36 うるま市 117934 37 糸満市 57254 38 名護市 61543 39 浦添市 117418 40 沖縄市 148791 41 那覇市 300368
(6)-3 の結果
桁数程度しか昇順になっていない．
1 伊平屋村 878 2 座間味村 570 3 北大東村 487 4 渡名喜村 219 5 粟国村 516 6 渡嘉敷村 641 7 多良間村 738 8 伊是名村 1112 9 南大東村 1039 10 東村 1222 11 与那国町 1421 12 国頭村 3011 13 大宜味村 2219 14 伊江村 2418 15 竹富町 3752 16 久米島町 4665 17 今帰仁村 8825 18 宜野座村 6495 19 本部町 10812 20 金武町 11249 21 恩納村 11926 22 嘉手納町 12191 23 北中城村 16181 24 与那原町 18715 25 中城村 25179 26 北谷町 29938 27 西原町 29299 28 八重瀬町 32308 29 宮古島市 41289 30 読谷村 40467 31 南風原町 42609 32 南城市 44923 33 宜野湾市 102606 34 石垣市 45417 35 糸満市 57254 36 名護市 61543 37 豊見城市 69660 38 浦添市 117418 39 うるま市 117934 40 沖縄市 148791 41 那覇市 300368
(6)-4 は失敗する
実行時エラーが発生してしまう．
結局ループなのか
どうにもうまく行かない．結局，Collection オブジェクトに Series オブジェクトを代入してソートし，二重ループで SeriesCollection と Name プロパティが一致した場合に PlotOrder を書き換えるという方法しか思いつかない．
For p = 1 To myColl.Count
myVar1 = myColl.Item(p).Values 'Values//Series/Variant()
myLong1 = myVar1(UBound(myVar1)) 'Values(6)/Long
'Debug.Print myLong1
For q = myColl.Count To p Step -1
myVar2 = myColl.Item(q).Values
myLong2 = myVar2(UBound(myVar2))
'Debug.Print , myLong2
If myLong1 > myLong2 Then
'(6)-1
' Stop
'Debug.Print myColl.Item(p).Name, myColl.Item(q).Name, ,
Set myColItemP = myColl.Item(p)
Set myColItemQ = myColl.Item(q)
myColl.Add myColItemQ, after:=p
myColl.Remove p
myColl.Add myColItemP, after:=q
myColl.Remove q
'Debug.Print myColl.Item(p).Name, myColl.Item(q).Name
End If
Next q
Next p
For p = 1 To myColl.Count
'Debug.Print myColl.Item(p).Name,
'Debug.Print p, myColl.Item(p).PlotOrder, myColl.Item(p).Name
For q = 1 To .SeriesCollection.Count
If myColl.Item(p).Name = .SeriesCollection(q).Name Then
'Debug.Print .SeriesCollection(q).PlotOrder,
'(6)-6
.SeriesCollection(q).PlotOrder = myColl.Item(p).PlotOrder
'(6)-7
'.SeriesCollection(q).PlotOrder = p
'Debug.Print .SeriesCollection(q).PlotOrder
End If
Next q
Debug.Print p, myColl.Count, myColl.Item(p).PlotOrder, myColl.Item(p).Name
Next p
For Each mySeries In .SeriesCollection
'Debug.Print mySeries.PlotOrder, mySeries.Name, mySeries.Values(6)
Next mySeries
で，やってみた．
(6)-6 の結果
1 41 3 多良間村 2 41 6 伊是名村 3 41 8 北大東村 4 41 5 久米島町 5 41 10 渡名喜村 6 41 11 粟国村 7 41 12 座間味村 8 41 13 渡嘉敷村 9 41 7 伊平屋村 10 41 9 南大東村 11 41 26 東村 12 41 1 与那国町 13 41 17 北中城村 14 41 16 西原町 15 41 27 大宜味村 16 41 22 金武町 17 41 24 本部町 18 41 21 伊江村 19 41 28 国頭村 20 41 2 竹富町 21 41 40 宜野座村 22 41 25 今帰仁村 23 41 23 恩納村 24 41 19 嘉手納町 25 41 15 与那原町 26 41 41 中城村 27 41 18 北谷町 28 41 4 八重瀬町 29 41 20 読谷村 30 41 30 宮古島市 31 41 32 豊見城市 32 41 37 石垣市 33 41 35 名護市 34 41 14 南風原町 35 41 29 南城市 36 41 34 糸満市 37 41 38 宜野湾市 38 41 36 浦添市 39 41 31 うるま市 40 41 33 沖縄市 41 41 39 那覇市
(6)-7 の結果
1 41 1 多良間村 2 41 2 伊是名村 3 41 3 北大東村 4 41 4 久米島町 5 41 5 渡名喜村 6 41 6 粟国村 7 41 7 座間味村 8 41 8 渡嘉敷村 9 41 9 伊平屋村 10 41 10 南大東村 11 41 11 東村 12 41 12 与那国町 13 41 13 北中城村 14 41 14 西原町 15 41 15 大宜味村 16 41 16 金武町 17 41 17 本部町 18 41 18 伊江村 19 41 19 国頭村 20 41 20 竹富町 21 41 21 宜野座村 22 41 22 今帰仁村 23 41 23 恩納村 24 41 24 嘉手納町 25 41 25 与那原町 26 41 26 中城村 27 41 27 北谷町 28 41 28 八重瀬町 29 41 29 読谷村 30 41 30 宮古島市 31 41 31 豊見城市 32 41 32 石垣市 33 41 33 名護市 34 41 34 南風原町 35 41 35 南城市 36 41 36 糸満市 37 41 37 宜野湾市 38 41 38 浦添市 39 41 39 うるま市 40 41 40 沖縄市 41 41 41 那覇市
結果が不安定である．
そもそも Collection オブジェクトに正常に格納できているのだろうか？
テストしてみるしかない．
Dim p As Long
Dim myVar As Variant
For Each mySeries In myCht.SeriesCollection
myColl.Add Item:=mySeries
Next mySeries
'(6)-8
For Each myVar In myColl
Debug.Print myVar.PlotOrder, myVar.Name,
myVar = myVar.Values
myVar = myVar(UBound(myVar))
Debug.Print myVar
Next myVar
'(6)-9
For p = 1 To myColl.Count
Debug.Print myColl.Item(p).PlotOrder, myColl.Item(p).Name,
myVar = myColl.Item(p).Values
myVar = myVar(UBound(myVar))
Debug.Print myVar
Next p
For Each ~ Next ステートメントの結果
1 与那国町 1421 2 竹富町 3752 3 多良間村 738 4 八重瀬町 32308 5 久米島町 4665 6 伊是名村 1112 7 伊平屋村 878 8 北大東村 487 9 南大東村 1039 10 渡名喜村 219 11 粟国村 516 12 座間味村 570 13 渡嘉敷村 641 14 南風原町 42609 15 与那原町 18715 16 西原町 29299 17 北中城村 16181 18 北谷町 29938 19 嘉手納町 12191 20 読谷村 40467 21 伊江村 2418 22 金武町 11249 23 恩納村 11926 24 本部町 10812 25 今帰仁村 8825 26 東村 1222 27 大宜味村 2219 28 国頭村 3011 29 南城市 44923 30 宮古島市 41289 31 うるま市 117934 32 豊見城市 69660 33 沖縄市 148791 34 糸満市 57254 35 名護市 61543 36 浦添市 117418 37 石垣市 45417 38 宜野湾市 102606 39 那覇市 300368 40 宜野座村 6495 41 中城村 25179
For ~ Next ステートメントの結果
1 与那国町 1421 2 竹富町 3752 3 多良間村 738 4 八重瀬町 32308 5 久米島町 4665 6 伊是名村 1112 7 伊平屋村 878 8 北大東村 487 9 南大東村 1039 10 渡名喜村 219 11 粟国村 516 12 座間味村 570 13 渡嘉敷村 641 14 南風原町 42609 15 与那原町 18715 16 西原町 29299 17 北中城村 16181 18 北谷町 29938 19 嘉手納町 12191 20 読谷村 40467 21 伊江村 2418 22 金武町 11249 23 恩納村 11926 24 本部町 10812 25 今帰仁村 8825 26 東村 1222 27 大宜味村 2219 28 国頭村 3011 29 南城市 44923 30 宮古島市 41289 31 うるま市 117934 32 豊見城市 69660 33 沖縄市 148791 34 糸満市 57254 35 名護市 61543 36 浦添市 117418 37 石垣市 45417 38 宜野湾市 102606 39 那覇市 300368 40 宜野座村 6495 41 中城村 25179
Collection オブジェクトには正常に格納されている
これはきちんと取得できているようだ．となると，ソートに問題があることになる．
課題
2019 年 12 月 25 日時点で Series オブジェクトのソートは解決できていない．2 週間ほど粘ったが，駄目だった．これは今後の課題だ．
はじめまして。たまたまSeriesのソートをしたくて検索していたらこのブログに行き当たりました。
自分のやりたいことは解決しましたが、何となく気になる結末だったので(お節介ですが)この記事のコードがうまくいかなかった理由を考えてみました。
・まずバブルソートの実装ですが、系列をSwapしてもmyArray1,myArray2が更新されない点が参照元の記事と異なります。特にmyArray1がそれまで調べた中での最小値になっておらず、予定外のSwapが発生していました。
・次にPlotOder。Seriesのソートが終わってもPlotOderはソート前のままです。ソート完了後に前から1,2,…でもいいですし、もしくは例えばp=1でqのループが終わった時、1番目は(最も少ない)渡名喜村になっているはずなので、next p の直前で myCall(p).PlotOder=p とできます。
上記の2点を修正すればSeriesオブジェクトのソート+グラフのプロットへ反映されるかと思います。
ぼん 様
貴重なコメント，ありがとうございます．
ただ今，時間がなくて試せないのですが，時間ができた時に試してみたく存じます．