「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の位置」 |
|---|---|---|---|---|
| 1 | AddCallout | 4種 | 4箇所 | すべて同じ |
| 2 | AddConnector | 3種 | 0 | 結合点なし |
| 3 | AddCurve | 1種 | 0 | 結合点なし |
| 4 | AddLabel | 6種 | 4箇所 | すべて同じ |
| 5 | AddLine | 5種 | 2箇所 | すべて同じ |
| 6 | AddPolyline | 1種 | 0 | 結合点なし |
| 7 | AddShape | 182種 | 0～16箇所 | ※詳細は後段でご説明いたします。 |
| 8 | AddTextbox | 6種 | 4箇所 | すべて同じ |
| 9 | BuildFreeform | 1種 | 4箇所 | (AddNodesで節点を変えても)同じ |
| 10 | AddChart2 | 74種 | 4箇所 | すべて同じ |
| 11 | AddFormControl | 9種 | 4箇所 | すべて同じ |
| 12 | AddOLEObject | 2種 | 4箇所 | すべて同じ |
| 13 | AddPicture | 2種 | 4箇所 | すべて同じ |
| 14 | AddPicture2 | 2個種 | 4箇所 | すべて同じ |
| 15 | AddSmartArt | 134種 | 4箇所 | すべて同じ |
| 16 | AddTextEffect | 50種 | 4箇所 | すべて同じ |
| 17 | Add3DModel | 2種 | 4箇所 | すべて同じ |
このように表にしてみると、描画メソッドに対してバリエーションがあるのはAddShapeメソッドだけである事がわかります。
ただ結合点の数が同じであったとしても、「周り方(番号の順番)が同じであるか?」は確認が必要ですので次の章では確認方法についてご説明いたします。
結合点の周り方(番号の順番)を確認する方法
描画オブジェクトの結合点の番号を調べる方法につきましては、下記Microsoft Officeドキュメント「ConnectorFormat オブジェクト(Excel)」の中の「例」の章に2つの方法が書かれています。
- マクロの記録を動かした状態で、図形の結合点にコネクターを結合させるとConnectorFormatオブジェクトのEndConnectメソッドのConnectionSiteパラメータに結合点の番号が記録されます。
- 図形を選択した状態で「例」に書かれたコーディングを実行すると、図形のすべての結合点に対して番号をラベルに書いた上でコネクタを接続する事ができます。
今回は2.の「列」に書かれたコーディングを元にアレンジを加えて「周り方」を確認しています。
※実際にアレンジしたものは後段のコーディング事例でご説明いたします。
AddShapeメソッド以外の結合点の「周り方」を確認した結果
AddShapeメソッド以外の16のメソッドに対して調査した結果をご紹介いたします。
その前に注意すべきポイントを列挙します。
- 「1の位置/周り方」は描画オブジェクトを回転・反転していない状態で調べた結果になります。
- 各メソッドの残り図形の種類も同じ「周り方」になる事を確認しています。
- 結合点が直線的に並ぶ時は、「周り方」を「その他」にしています。
| No | メソッド | 図形 の種類 | Connection SiteCount | 結合点の順番 | 1の位置/周り方 |
|---|---|---|---|---|---|
| 1 | AddCallout | 4種 | 4箇所 |  | 右/時計回り |
| 2 | AddConnector | 3種 | 0 | なし | |
| 3 | AddCurve | 1種 | 0 | なし | |
| 4 | AddLabel | 6種 | 4箇所 |  | 上/反時計回り |
| 5 | AddLine | 5種 | 2箇所 |  | 始点が1/その他 |
| 6 | AddPolyline | 1種 | 0 | なし | |
| 7 | AddShape | 182種 | 0～16箇所 | ※後段でご説明いたします。 | |
| 8 | AddTextbox | 6種 | 4箇所 |  | 上/反時計回り |
| 9 | BuildFreeform | 1種 | 4箇所 |  | 始点が1で 始点1と終点4が重なる /反時計回り |
| 10 | AddChart2 | 74種 | 4箇所 |  | 上/反時計回り |
| 11 | AddFormControl | 9種 | 4箇所 |  | 上/反時計回り |
| 12 | AddOLEObject | 2種 | 4箇所 |  | 上/反時計回り |
| 13 | AddPicture | 2種 | 4箇所 |  | 上/反時計回り |
| 14 | AddPicture2 | 2個種 | 4箇所 |  | 上/反時計回り |
| 15 | AddSmartArt | 134種 | 4箇所 |  | 上/反時計回り |
| 16 | AddTextEffect | 50種 | 4箇所 |  | 上/反時計回り |
| 17 | Add3DModel | 2種 | 4箇所 |  | 上/反時計回り |
AddCalloutメソッドだけが「時計回り」になっていて、それ以外は「反時計回り」がほとんどを占めています。
※AddShapeメソッドにつきましては、コーディング事例をご紹介した後にまとめてご説明いたします。
コーディング事例
すべての描画オブジェクトにするとコーディングが長くなってしまうので、今回はAddLineメソッドとAddShapeメソッドだけに絞ってご紹介いたします。
その他のメソッドにつきましては前回と前々回にご紹介したコーディング事例と今回とを組み合わせてお試しいただければ幸いです。
プログラムの構造は今回はメインサブルーチンから、描画メソッドを呼び出すファンクションと、そのファンクションから呼ばれる共通出力処理と結合点書出しのための子サブルーチンの計4個で構成しています。
ワークシート上への出力項目
基本的には、前回と前々回と変わらないのですが、コーディング量を減らすために出力するのはShape.ConnectionSiteCountプロパティだけにしています。
なおこれまでと同様に「指定したグラフの種類(2項目)を追加した描画オブジェクトが持つプロパティ(5項目目)で判定できるか?」を調べるための数式(6項目)は、そのままにしています。
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
|---|---|---|---|---|---|---|---|
| No | 種類 | Name | ConnectionSiteCount | 判定プロパティ | 比較数式 | 描画図形 |
実際のコーディング事例
メイン処理
- ヘッダー行と各メソッドのタイトル行のセットと行送りの値のセット
- 結合点の多い図形には多くのコネクタが必要になるので、行送りを増やす調整をしています。
- 各メソッドごとの判定プロパティのセット
- 画面更新の制御と列幅自動調整
※コーディング行が横幅に入り切れていないのでマウスのドラックかキーボードの矢印ボタンでスクロールしてください。
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の位置」と「周り方」が異なるため、すべての種類を表記いたします。
すべての種類の一覧表
一覧表の項目は次のようになります。
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
|---|---|---|---|---|---|---|---|
| 種類 | 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の位置」でのそれぞれの集計結果
次の表の左が「周り方」での集計結果、右が「1の位置」での集計結果になります。
ただし、結果から「何かの規則性が見つけられるか?」と言うと残念ながらこれと言った規則性は存在していません。
「周り方」のまとめ
- 「反時計回り」の方が数的には多いのですが「時計回り」も相当数あり、どちらかにまとめられない状況になっているようです。
- 周り方が結合点の数によって決まってくるのか?と言うと「その他」は確かに結合点の数が2・3の場合に限られるのですが、「時計回り」・「反時計回り」はばらけています。
「1の位置」のまとめ
- 「上」から始まるものが多いのですが「右」もそれなりに存在しています。
- 「下」、「左」、「中」は結合点数6箇所以下に存在しています。
| 結合点数 | 時計回り | 反時計回り | その他 | 合計 |
|---|---|---|---|---|
| 2 | 1 | 2 | 3 | |
| 3 | 2 | 4 | 2 | 8 |
| 4 | 48 | 65 | 113 | |
| 5 | 13 | 13 | ||
| 6 | 8 | 6 | 14 | |
| 7 | 2 | 1 | 3 | |
| 8 | 4 | 10 | 14 | |
| 9 | 1 | 1 | 2 | |
| 10 | 2 | 2 | ||
| 12 | 2 | 2 | 4 | |
| 16 | 1 | 1 | 2 | |
| 合計 | 71 | 103 | 4 | 178 |
| 結合点数 | 上 | 下 | 左 | 右 | 中 |
|---|---|---|---|---|---|
| 2 | 1 | 1 | 1 | ||
| 3 | 6 | 1 | 1 | ||
| 4 | 66 | 1 | 2 | 43 | 1 |
| 5 | 8 | 2 | 3 | ||
| 6 | 7 | 1 | 6 | ||
| 7 | 1 | 2 | |||
| 8 | 10 | 4 | |||
| 9 | 1 | 1 | |||
| 10 | 2 | ||||
| 12 | 2 | 2 | |||
| 16 | 1 | 1 | |||
| 合計 | 103 | 4 | 5 | 62 | 4 |
「周り方」と「1の位置」の関係性
| 1の位置 | 時計回り | 反時計回り | その他 | 合計 |
|---|---|---|---|---|
| 上 | 4 | 97 | 2 | 103 |
| 下 | 3 | 1 | 4 | |
| 左 | 1 | 3 | 1 | 5 |
| 右 | 62 | 62 | ||
| 中 | 1 | 3 | 4 | |
| 合計 | 71 | 103 | 4 | 178 |
前章では規則性は無かったのですが、左表を見ていただくと解るように「周り方」と「1の位置」には関係性がありそうです。なお例外はあるので気を付ける必要はあります。
- 時計回りの時は「1の位置」は概ね「右」
- 反時計回りの時は「1の位置」は概ね「上」
ただし、コーディング時には「結合点の数」を考慮しないと想定した位置にコネクタを結合する事ができないので、「この規則性を生かして何かできるのか?」というと難しい認識です。
最後に
「すべての描画オブジェクトにおいて、コメクターを接続する際の結合点がどのような順番で設定されているか?を調べる方法」についてご説明してきました。
Microsoftのドキュメントでは、「接続する際の結合点」の事を「接続サイト」と表現している時もありますが、「接続サイト」だけで内容を把握するのは難いので、このような下手な表現にしています。
これまで調べた結果を大別すると、周り方には「時計回り」と「反時計回り」などがあり、しかも「1の位置」も「上」や「右」などがあって、コーディングで「指定した位置にコネクタをつなげたい」という場合には、図形ごとに周り方と番号の位置を判断しなければならない事がお分かりいただけた事と存じます。
少し話は変わるのですが、ShapeオブジェクトにはRerouteConnections メソッドがありこのメソッドは「図形間を結ぶコネクタの経路を最短にする」ように接続してくれます。
※文中のリンク先はMicrosoft Officeドキュメントの該当ページになります。
では「図形ごとに考慮しない」状況でExcelはどのようにしてこの最短経路を特定しているのでしょうか?
これは内部仕様が分からないと正しい事は言えないのですが、筆者の想定としては「コネクタを接続する2つの図形のすべての結合点を結ぶ組み合わせを試して、その結果から最短になるルートを抽出しているのではないか?」と思います。
そうなると『「周り方」とか「1の位置」とかを調べても役に立たないのでは?』と思われるかもしれませんが、唯一「周り方」とか「1の位置」が必要になるのは、先ほども書きました「指定した位置にコネクタをつなげたい」場合です。
このような要件が必要になる事は少ないかもしれませんが、もしも必要になった時はまずは図形の種類を特定した上で、それぞれの図形の結合点の順番を考慮しないとつなげる事が難しくなる事だけは記憶に留めて置いていたければ幸いです。
以上最後までご一読いただき誠にありがとうございました。
