PR

Excel VBAですべての描画オブジェクトの結合点の順番を調べる

Excel VBA Tips
この記事は約20分で読めます。

「Excel VBA Tips」ではExcel VBAを使用していて気付いたことを取り上げて行きます。

前回前々回の2回に渡り「すべての描画オブジェクトをExcel VBAでActiveSheetに書き出し、その書き出された描画オブジェクトの種類を確認するコーディング事例」をご紹介しましたが、今回はこの「すべての描画オブジェクトを書き出す」方法を使って、「すべての描画オブジェクトにおいて、コメクタを接続する際の結合点がどのような順番で設定されているか?を調べる方法」をご紹介いたします。

調べた結果を先に述べますと、接合点の数は「0から16」の範囲となり、番号の順番は大きく「時計回り」・「反時計回り」・「その他」に別けられる事になりますが、実際にどう言う風になっているのか?を具体的に見て行きます。

なお「Excel VBAで全描画オブジェクトをシートに書き出して確認する-その1」「〃-その2」でご説明した内容を引用する箇所がありますので、合わせてご確認いただければ幸いです。

※動作は32bit版Excel 2016と64bit版Excel 2021の バージョン2210(ビルド 15726.20202)を使用して検証しています。

スポンサーリンク

すべての描画オブジェクトのShape.ConnectionSiteCountを調べる

描画オブジェクトの結合点の数はShape.ConnectionSiteCountプロパティで取得する事ができます。
※上記のリンク先はMicrosoft Officeドキュメントの該当ページになります。

前回前々回にご紹介したコーディング事例の中のShape.AutoShapeTypeプロパティを確認するコーディング箇所をShape.ConnectionSiteCountプロパティに変更するだけで、すべての描画オブジェクトの結合点の数を調べる事ができます。

ただしこれまでにも何度かご紹介して来ましたが、グラフについては単独選択ではSelection.ShapeRangeが使用できないのでVBAで確認するにはグラフを複数選択した上でSelection.ShapeRange(Index)を使って調べる必要がありますのでご注意ください。

すべての描画オブジェクトを調べた結果は次の表のようになります。
※なお「周り方(番号の順番)」と「1の位置」については後段でご説明いたします。

Noメソッド調べる図形
の種類
Connection
SiteCount
結合点の「周り方」と「1の位置」
1AddCallout4種4箇所すべて同じ
2AddConnector3種0結合点なし
3AddCurve1種0結合点なし
4AddLabel6種4箇所すべて同じ
5AddLine5種2箇所すべて同じ
6AddPolyline1種0結合点なし
7AddShape182種0~16箇所※詳細は後段でご説明いたします。
8AddTextbox6種4箇所すべて同じ
9BuildFreeform1種4箇所(AddNodesで節点を変えても)同じ
10AddChart274種4箇所すべて同じ
11AddFormControl9種4箇所すべて同じ
12AddOLEObject2種4箇所すべて同じ
13AddPicture2種4箇所すべて同じ
14AddPicture22個種4箇所すべて同じ     
15AddSmartArt134種4箇所すべて同じ
16AddTextEffect50種4箇所すべて同じ
17Add3DModel2種4箇所すべて同じ

このように表にしてみると、描画メソッドに対してバリエーションがあるのはAddShapeメソッドだけである事がわかります。

ただ結合点の数が同じであったとしても、「周り方(番号の順番)が同じであるか?」は確認が必要ですので次の章では確認方法についてご説明いたします。

結合点の周り方(番号の順番)を確認する方法

描画オブジェクトの結合点の番号を調べる方法につきましては、下記Microsoft Officeドキュメント「ConnectorFormat オブジェクト(Excel)」の中の「例」の章に2つの方法が書かれています。

  1. マクロの記録を動かした状態で、図形の結合点にコネクターを結合させるとConnectorFormatオブジェクトのEndConnectメソッドのConnectionSiteパラメータに結合点の番号が記録されます。
  2. 図形を選択した状態で「例」に書かれたコーディングを実行すると、図形のすべての結合点に対して番号をラベルに書いた上でコネクタを接続する事ができます。

今回は2.の「列」に書かれたコーディングを元にアレンジを加えて「周り方」を確認しています。
※実際にアレンジしたものは後段のコーディング事例でご説明いたします。

AddShapeメソッド以外の結合点の「周り方」を確認した結果

AddShapeメソッド以外の16のメソッドに対して調査した結果をご紹介いたします。

その前に注意すべきポイントを列挙します。

  • 「1の位置/周り方」は描画オブジェクトを回転・反転していない状態で調べた結果になります。
  • 各メソッドの残り図形の種類も同じ「周り方」になる事を確認しています。
  • 結合点が直線的に並ぶ時は、「周り方」を「その他」にしています。
Noメソッド図形
の種類
Connection
SiteCount
結合点の順番1の位置/周り方
1AddCallout4種4箇所右/時計回り
2AddConnector3種0なし
3AddCurve1種0なし
4AddLabel6種4箇所上/反時計回り
5AddLine5種2箇所始点が1/その他
6AddPolyline1種0なし
7AddShape182種0~16箇所※後段でご説明いたします。
8AddTextbox6種4箇所上/反時計回り
9BuildFreeform1種4箇所始点が1で
始点1と終点4が重なる
/反時計回り
10AddChart274種4箇所上/反時計回り
11AddFormControl9種4箇所上/反時計回り
12AddOLEObject2種4箇所上/反時計回り
13AddPicture2種4箇所上/反時計回り
14AddPicture22個種4箇所  上/反時計回り
15AddSmartArt134種4箇所上/反時計回り
16AddTextEffect50種4箇所上/反時計回り
17Add3DModel2種4箇所上/反時計回り

AddCalloutメソッドだけが「時計回り」になっていて、それ以外は「反時計回り」がほとんどを占めています。
※AddShapeメソッドにつきましては、コーディング事例をご紹介した後にまとめてご説明いたします。

コーディング事例

すべての描画オブジェクトにするとコーディングが長くなってしまうので、今回はAddLineメソッドとAddShapeメソッドだけに絞ってご紹介いたします。

その他のメソッドにつきましては前回前々回にご紹介したコーディング事例と今回とを組み合わせてお試しいただければ幸いです。

プログラムの構造は今回はメインサブルーチンから、描画メソッドを呼び出すファンクションと、そのファンクションから呼ばれる共通出力処理と結合点書出しのための子サブルーチンの計4個で構成しています。

ワークシート上への出力項目

基本的には、前回前々回と変わらないのですが、コーディング量を減らすために出力するのはShape.ConnectionSiteCountプロパティだけにしています。

なおこれまでと同様に「指定したグラフの種類(2項目)を追加した描画オブジェクトが持つプロパティ(5項目目)で判定できるか?」を調べるための数式(6項目)は、そのままにしています。

12345678
No種類NameConnectionSiteCount判定プロパティ比較数式 描画図形

実際のコーディング事例

メイン処理

  • ヘッダー行と各メソッドのタイトル行のセットと行送りの値のセット
    • 結合点の多い図形には多くのコネクタが必要になるので、行送りを増やす調整をしています。
  • 各メソッドごとの判定プロパティのセット
  • 画面更新の制御と列幅自動調整

※コーディング行が横幅に入り切れていないのでマウスのドラックかキーボードの矢印ボタンでスクロールしてください。

Sub AllDrawingObject1()
Dim lid As Long  '列挙型数値の繰返しカウント
Dim lrow As Long  '送り行
Dim cnt As Long  '連番カウンター
    Application.ScreenUpdating = False   '画面固定
'ヘッダー行
    Cells(1, 1).Value = "No": Cells(1, 2).Value = "種類": Cells(1, 3).Value = "Name"
    Cells(1, 4).Value = "ConnectionSiteCount": Cells(1, 5).Value = "判定プロパティ"
    Cells(1, 6).Value = "比較数式": Cells(1, 7).Value = " ": Cells(1, 8).Value = "描画図形": Range("A1:J1").Font.Bold = True
'初期値セット
    lrow = 2  '1行目はヘッダーなので2行目から
    cnt = 0  '連番カウンター初期化(サブルーチンで使用)
'AddLine
    Cells(lrow, 3).Value = "AddLine": Cells(lrow, 3).Font.Bold = True: lrow = lrow + 1  'メソッドタイトル&行送り
    For lid = 1 To 5
        If drawPattern1(cnt, lrow, "AddLine", lid) Then Cells(lrow, 5).Value = Selection.ShapeRange(1).Line.Style
        lrow = lrow + 5  '行送り
    Next
'AddShape
    Cells(lrow, 3).Value = "AddShape": Cells(lrow, 3).Font.Bold = True: lrow = lrow + 1  'メソッドタイトル&行送り
    For lid = 1 To 183
        If drawPattern1(cnt, lrow, "AddShape", lid) Then Cells(lrow, 5).Value = Selection.ShapeRange(1).AutoShapeType
        '結合点の多い図形は行送りを増やす
        If lid = msoShape16pointStar Or lid = msoShapeSquareTabs Then
            lrow = lrow + 10  '行送り
        ElseIf lid = msoShapeDecagon Or lid = msoShape10pointStar Then lrow = lrow + 6 '行送り
        ElseIf lid = msoShapeDodecagon Or lid = msoShape12pointStar Or lid = msoShapeCornerTabs Or lid = msoShapePlaqueTabs Then lrow = lrow + 7  '行送り
        Else
            lrow = lrow + 5  '行送り
        End If
    Next
'後処理
    Application.ScreenUpdating = True   '固定解除
    Cells.Select: Cells.EntireColumn.AutoFit '列幅自動調整
    ActiveSheet.Cells(1, 1).Select  'A1セルを選択する
End Sub

描画処理

  • 描画メソッドを指定したセルで実行
    • 高さ or 幅を広げないと形が表示されない図形への対応
  • 比較数式をセット(背景色を付ける)
  • 図形塗りつぶしの透明度を上げる事でコネクタの色を鮮明化する。
    • 透明度属性を持たない一部の図形への対応
  • 「結合点書出し」と「共通出力処理」のサブルーチンをコールする。
  • On Errorステートメントの処理

※ファンクションの引数のbLinkは今回の描画メソッドでは使用しませんが、Optional設定をしているので(設定してもしなくても問題が無いので)そのまま残してあります。

Function drawPattern1(ByRef cnt As Long, lrow As Long, stMethod As String, vType As Variant, Optional bLink As Boolean = False) As Boolean
On Error GoTo Err_Handle
Dim sp As Shape
Dim i_w As Integer, i_h As Integer
    drawPattern1 = True
    ActiveSheet.Cells(lrow, 8).Select  'セルを選択する
    Cells(lrow, 6).FormulaR1C1 = "=IF(RC[-1]<>RC[-4],""X"","""")"  '比較数式セット
    Cells(lrow, 6).Interior.Color = RGB(234, 234, 234)  '背景色
    With ActiveCell
        If stMethod = "AddLine" Then
            Set sp = ActiveSheet.Shapes.AddLine(.Left, .Top, .Left + 20, .Top + 15)
            sp.Line.Style = vType: sp.Line.Weight = 4#  '属性追加指定
        ElseIf stMethod = "AddShape" Then
            i_w = 40: i_h = 40
            '高さ/幅を広げないと形が表示されない図形への対応
            If CInt(vType) = msoShapeLeftRightArrow Or CInt(vType) = msoShapeLeftRightArrowCallout Or CInt(vType) = msoShapeLeftRightRibbon Then
                i_w = 60
            ElseIf CInt(vType) = msoShapeUpDownArrow Or CInt(vType) = msoShapeUpDownArrowCallout Then i_h = 60
            End If
            Set sp = ActiveSheet.Shapes.AddShape(vType, .Left, .Top, i_w, i_h)
        End If
    End With
    If stMethod = "AddLine" Or (stMethod = "AddShape" And vType = msoShapeLineInverse) Then
        'nop 塗りつぶしの透明度を持たない図形
    Else
        sp.Fill.Transparency = 0.8
    End If
    Call connectionDraw(sp)
    sp.Select
    Set sp = Nothing
    Call commonOutPut(cnt, lrow, vType)
    Exit Function '<<<<<<<<
Err_Handle:
    Cells(lrow, 2).Value = vType: Cells(lrow, 3).Value = Err.Number & "-" & Err.Description
    Cells(lrow, 6).Clear  '数式削除
    drawPattern1 = False: Err.Clear: On Error GoTo 0
End Function

共通出力処理

  • 連番、種類(列挙パラメータ等)、Name、ConnectionSiteCountの出力
Sub commonOutPut(ByRef cnt As Long, lrow As Long, vType As Variant)
    Cells(lrow, 2).Value = vType  '列挙パラメータ等
    With Selection.ShapeRange(1)
        Cells(lrow, 3).Value = .Name
        Cells(lrow, 4).Value = .ConnectionSiteCount
    End With
    cnt = cnt + 1: Cells(lrow, 1).Value = cnt  '連番出力
End Sub

結合点書出し

  • 16色の配色を配列にセットして1からConnectionSiteCountまで下記処理を繰り返します。
    • AddConnectorメソッドでコネクタを描画し、終点矢印と線の色を設定
    • AddLabelメソッドに結合点の番号をセットして、文字の色を設定
Sub connectionDraw(spTarget As Shape)
Dim i As Integer, l_left As Long, l_top As Long
Dim l_color(1 To 16) As Long
    l_color(1) = RGB(255, 0, 0): l_color(2) = RGB(0, 0, 128): l_color(3) = RGB(0, 100, 0): l_color(4) = RGB(178, 34, 34)
    l_color(5) = RGB(255, 20, 147): l_color(6) = RGB(0, 0, 255): l_color(7) = RGB(60, 179, 113): l_color(8) = RGB(153, 50, 204)
    l_color(9) = RGB(0, 206, 209): l_color(10) = RGB(50, 205, 50): l_color(11) = RGB(123, 104, 238): l_color(12) = RGB(95, 158, 160)
    l_color(13) = RGB(128, 128, 0): l_color(14) = RGB(240, 128, 128): l_color(15) = RGB(0, 139, 139): l_color(16) = RGB(128, 128, 128)
    l_left = ActiveCell.Left + spTarget.Width + 15: l_top = ActiveCell.Top - 15
    For i = 1 To spTarget.ConnectionSiteCount
        With ActiveSheet.Shapes.AddConnector(msoConnectorStraight, l_left, l_top, l_left + 15, l_top + 15)
            .ConnectorFormat.EndConnect spTarget, i
            .Line.EndArrowheadStyle = msoArrowheadTriangle
            .Line.ForeColor.RGB = l_color(i)
        End With
        With ActiveSheet.Shapes.AddLabel(msoTextOrientationHorizontal, l_left, l_top - 10, 30, 10)
            .TextFrame.Characters.Text = i
            .TextFrame.Characters.Font.Color = l_color(i)
        End With
        l_top = l_top + 10
    Next
End Sub

AddShapeメソッドで描画される図形の結合点

結合点は0~16箇所になる事は前段でご紹介していますが、その詳細につきましてご説明いたします。

結合点の数だけでなく個々の図形によって「1の位置」と「周り方」が異なるため、すべての種類を表記いたします。

すべての種類の一覧表

一覧表の項目は次のようになります。

12345678
種類Name結合点数判定プロパティ比較数式結合点描画を画像に変換周り方1の位置

「結合点の数」が4箇所というのが一番多く113種類、2番目は6箇所と8箇所で共に14種類で、3番目は5箇所で13種類、残りはすべて1桁の種類になります。

なお、下記ExcelシートはMicrosoftサポートの「OneDrive から Web ページやブログに Excel ブックを埋め込む」に基づきOneDrive「https://onedrive.live.com」にアクセスして表示しています。
※iframeを使用しています。

  • 周り方
    • 結合点の順番が右回りか?左回りか?は図形の形によって微妙の場合もありますが、ざっくりとどちらの方向に周っているかを表しています。
    • 次の3つの区分を設定
      • 時計回り、反時計回り、その他(結合点が直線的に並んでいる場合に該当)
  • 1の位置
    • 結合点の「1」が図形上のどの場所にあるか?を表しています。
      これも図形の形によって判定が微妙の場合もありますが、ざっくりと上の部分か?下か?右か?左か?中間なのか?を表しています。
    • 次の5つの区分を設定
      • 上、下、左、右、中

「周り方」と「1の位置」でのそれぞれの集計結果

次の表の左が「周り方」での集計結果、右が「1の位置」での集計結果になります。
ただし、結果から「何かの規則性が見つけられるか?」と言うと残念ながらこれと言った規則性は存在していません。

「周り方」のまとめ

  • 「反時計回り」の方が数的には多いのですが「時計回り」も相当数あり、どちらかにまとめられない状況になっているようです。
  • 周り方が結合点の数によって決まってくるのか?と言うと「その他」は確かに結合点の数が2・3の場合に限られるのですが、「時計回り」・「反時計回り」はばらけています。

「1の位置」のまとめ

  • 「上」から始まるものが多いのですが「右」もそれなりに存在しています。
  • 「下」、「左」、「中」は結合点数6箇所以下に存在しています。
結合点数時計回り反時計回りその他合計
2123
32428
44865113
51313
68614
7213
841014
9112
1022
12224
16112
合計711034178
結合点数
2111
3611
46612431
5823
6716
712
8104
911
102
1222
1611
合計10345624
※横計は左表と同じになります。

「周り方」と「1の位置」の関係性

1の位置時計回り反時計回りその他合計
4972103
314
1315
6262
134
合計711034178

前章では規則性は無かったのですが、左表を見ていただくと解るように「周り方」と「1の位置」には関係性がありそうです。なお例外はあるので気を付ける必要はあります。

  • 時計回りの時は「1の位置」は概ね「右」
  • 反時計回りの時は「1の位置」は概ね「上」

ただし、コーディング時には「結合点の数」を考慮しないと想定した位置にコネクタを結合する事ができないので、「この規則性を生かして何かできるのか?」というと難しい認識です。

最後に

「すべての描画オブジェクトにおいて、コメクターを接続する際の結合点がどのような順番で設定されているか?を調べる方法」についてご説明してきました。

Microsoftのドキュメントでは、「接続する際の結合点」の事を「接続サイト」と表現している時もありますが、「接続サイト」だけで内容を把握するのは難いので、このような下手な表現にしています。

これまで調べた結果を大別すると、周り方には「時計回り」と「反時計回り」などがあり、しかも「1の位置」も「上」や「右」などがあって、コーディングで「指定した位置にコネクタをつなげたい」という場合には、図形ごとに周り方と番号の位置を判断しなければならない事がお分かりいただけた事と存じます。

少し話は変わるのですが、ShapeオブジェクトにはRerouteConnections メソッドがありこのメソッドは「図形間を結ぶコネクタの経路を最短にする」ように接続してくれます。
※文中のリンク先はMicrosoft Officeドキュメントの該当ページになります。

では「図形ごとに考慮しない」状況でExcelはどのようにしてこの最短経路を特定しているのでしょうか?

これは内部仕様が分からないと正しい事は言えないのですが、筆者の想定としては「コネクタを接続する2つの図形のすべての結合点を結ぶ組み合わせを試して、その結果から最短になるルートを抽出しているのではないか?」と思います。

そうなると『「周り方」とか「1の位置」とかを調べても役に立たないのでは?』と思われるかもしれませんが、唯一「周り方」とか「1の位置」が必要になるのは、先ほども書きました「指定した位置にコネクタをつなげたい」場合です。

このような要件が必要になる事は少ないかもしれませんが、もしも必要になった時はまずは図形の種類を特定した上で、それぞれの図形の結合点の順番を考慮しないとつなげる事が難しくなる事だけは記憶に留めて置いていたければ幸いです。

以上最後までご一読いただき誠にありがとうございました。