EXCEL VBA で Series オブジェクトをソートできなかった話

 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’ が発生する.

実行時エラー '451': Property Letプロシージャが定義されておらず,Property Getプロシージャからオブジェクトが返されませんでした
実行時エラー ‘451’: Property Letプロシージャが定義されておらず,Property Getプロシージャからオブジェクトが返されませんでした

 エラーが発生してコードが停止した時点でローカルウィンドウを見てみる.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 週間ほど粘ったが,駄目だった.これは今後の課題だ.

“EXCEL VBA で Series オブジェクトをソートできなかった話” への2件の返信

  1. はじめまして。たまたまSeriesのソートをしたくて検索していたらこのブログに行き当たりました。

    自分のやりたいことは解決しましたが、何となく気になる結末だったので(お節介ですが)この記事のコードがうまくいかなかった理由を考えてみました。

    ・まずバブルソートの実装ですが、系列をSwapしてもmyArray1,myArray2が更新されない点が参照元の記事と異なります。特にmyArray1がそれまで調べた中での最小値になっておらず、予定外のSwapが発生していました。

    ・次にPlotOder。Seriesのソートが終わってもPlotOderはソート前のままです。ソート完了後に前から1,2,…でもいいですし、もしくは例えばp=1でqのループが終わった時、1番目は(最も少ない)渡名喜村になっているはずなので、next p の直前で myCall(p).PlotOder=p とできます。

    上記の2点を修正すればSeriesオブジェクトのソート+グラフのプロットへ反映されるかと思います。

    1. ぼん 様

      貴重なコメント,ありがとうございます.
      ただ今,時間がなくて試せないのですが,時間ができた時に試してみたく存じます.

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください