VBA ShapesオブジェクトとItemメソッド・Rangeプロパティ

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

今回は「Shapesオブジェクトにはどのようなものが含まれのるか?」を中心に、ItemメソッドとRangeプロパティついても改めて調べてみました。

なおShapesオブジェクトにはセル・セル範囲は原則含まれていません。「原則」と表現したのは「データの入力規則がリストの場合」はどう考えれば良いのか?が微妙なためです。

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

Shapesオブジェクトが持つ19のメソッドを分類すると…

19メソッドの内、「図形を作成する」以外のメソッドは下記の2つになります。

• Item
  • ShapesオブジェクトはShapeオブジェクトのコレクションであり、Itemメソッドはコレクションから単一のオブジェクトを取り出す役割を担います。
• SelectAll
  • Shapeオブジェクトのコレクションに含まれるすべての図形を選択します。

残りの「図形を作成する」ための17メソッドを「図形」と「図形以外」に大別すると次のようになります。

こうして対比してみると、Shapesという言葉とは裏腹に図形以外の描画オブジェクトがほぼほぼ同じの数だけ存在している事が解ります。

なお本記事では区別していない「図形」という表記には図形以外の描画オブジェクトも含めていますのでお含み置きいただければ幸いです。

なおMicrosoft officeドキュメントのShapesのページは下記になります。

Shapes オブジェクト (Excel)

Office VBA リファレンス トピック

learn.microsoft.com

AddLabelとAddTextboxの作成結果の微妙な違い

実際に両者のメソッドをそれぞれ実行した結果は下記のようになります。

Sub addlabel()
Dim sp As Shape
    Set sp = ActiveSheet.Shapes.addlabel(msoTextOrientationHorizontal, 0, 0, 104.88, 35.72)
    sp.TextFrame.Characters.Text = "Label 01"
    Range("A3").Value = "Label-Height:" & sp.Height & " Transparency:" & sp.Fill.Transparency _
        & " Visible:" & sp.Line.Visible
    Set sp = Nothing
End Sub

Sub addtextbox()
Dim sp As Shape
   Set sp = ActiveSheet.Shapes.addtextbox(msoTextOrientationHorizontal, 0, 0, 104.88, 35.72)
    sp.TextFrame.Characters.Text = "Text Box"
    Range("A3").Value = "Textbox-Height:" & sp.Height & " Transparency:" & sp.Fill.Transparency _
        & " Visible:" & sp.Line.Visible
    Set sp = Nothing
End Sub

外見上気が付いたのは、図形の高さ・透過度・外枠の有無になります。上図A3セルに出力したようにそれぞれ値が異なっています。ただし当該図形のすべての属性を比較した訳ではありませんのでお含み置きいただければ幸いです。

ShapesオブジェクトのItemメソッドでShapeオブジェクトを取り出す

ShapesオブジェクトはShapeオブジェクトのコレクションですが、コレクションから単一のオブジェクトを取り出すのに使うのはItemメソッドになります。なおShapesにパラメータ(添え字)を付ける事でもコーディングする事ができます。

以下に個々のShapeオブジェクトを取り出すコーディング事例を2つご紹介いたしますが、1つはItemメソッドを使わない方法です。

For Each…Nextステートメントを使用

Sub ForEachNext()
Dim sp As Shape
    For Each sp In ActiveSheet.Shapes
        Debug.Print sp.Name
    Next sp
    Set sp = Nothing
End Sub

Microsoft officeドキュメント

For Each...Next ステートメントを使用する (VBA)

Office VBA 言語リファレンス

learn.microsoft.com

ShapesオブジェクトのItemメソッドを使用

Sub itemMethod()
Dim i As Integer
    With ActiveSheet.Shapes
        For i = 1 To .Count
            Debug.Print .Item(i).Name
        Next i
    End With
End Sub

Microsoft officeドキュメント

Shapes.Item メソッド (Excel)

Office VBA リファレンス トピック

learn.microsoft.com

For Each…NextステートメントはItemメソッドを使って下左のように書き換える事ができますが、この形にするのであればsp変数にセットするというのは効率的ではありません。

With ActiveSheet.Shapes
For i = 1 To .Count
    Set sp = .Item(i)
...

For i = 1 To ActiveSheet.Shapes.Count
    Set sp = ActiveSheet.Shapes(i)

このようにコーデングの仕方には色々あるのですが、見やすく解り易いコーデングにするためには、極力冗長的な部分を排除して効率性を求めた方が良い認識です。

また可能であれば記述の仕方は1つのアプリケーションの中では統一されていた方が可読性が上がるはずです。

ただし実際には理想通りにならない事の方が多いのですが…

ShapesオブジェクトのRangeプロパティの使い方

Shapes.RangeプロパティはShapeオブジェクトのコレクション内の図形のサブセットを表すShapesRangeオブジェクトを返します。(出典は下記Microsoft Officeのページ)

Shapes.Range プロパティ (Excel)

Office VBA リファレンス トピック

learn.microsoft.com

このことをあまり深く考えずにコーデングしているとItemメソッドもRangeプロパティも変わらないのでは?と思えてしまいますが、決して同じではありませんので注意が必要です。

下記のMicrosoft OfficeドキュメントのShapeオブジェクトのページの注意書きに「図形を表す 3 つのオブジェクト」の説明がされています。

Shape オブジェクト (Excel)

Office VBA リファレンス トピック

learn.microsoft.com

その内容を一部修正して箇条書きにすると次のようになります。

• Shapesコレクション
  • ワークシート上のすべての図形を表す。
• ShapeRangeコレクション
  • ワークシート上の指定されている図形のサブセットを表す。
• Shapeオブジェクト
  • ワークシート上の1つの図形を表す。

従ってワークシート上、例えばActivesheetに対してShapeRangeを使用する事はできません。なぜならActivesheetは「指定されている図形」を対象にしていないからです。(選択された図形はSelectionを使用する事になりますが、Selectionに関しても色々な事があるので次の記事でテーマにする予定です。)

そのためShapeRangeの代わりに使用するのがShapes.Rangeプロパティです。ただしShapeRangeは単独でも使用できますが、Rangeは単独では使用できずパラメータとしてIndexが必須です。
※ShapeRangeはIndexを付けても使用できます。

Shapes.RangeプロパティはIndexを使う事によって初めて「指定されている図形」として認識します。

ShapeオブジェクトとShapeRangeオブジェクトのプロパティは48個は同じなのですが、微妙な違いがあるので、違うところだけを下記の表にまとめます。

Shapesオブジェクにどのような描画オブジェクトが存在するかを知る

冒頭の章でShapesオブジェクで図形及び図形以外の描画オブジェクトを作成するメソッドが17個存在する事をご説明しました。

実際にどのような描画オブジェクトなのかを知るために、ShapeオブジェクトのTypeプロパティを使用します。Microsoft officeドキュメントのShape.Typeのページは下記になります。

Shape.Type プロパティ (Excel)

Office VBA リファレンス トピック

learn.microsoft.com

またTypeプロパティが返すのはMsoShapeType値になりますがその説明のページは下記になります。

MsoShapeType 列挙 (Office)

Office VBA リファレンス トピック

learn.microsoft.com

MsoShapeType列挙(Enumerations)について

MsoShapeType列挙(Enumerations)には32の値が掲載されていますが、これらはOffice製品全体が対象になっているのですべてがExcelで使用されている分けではありません。

ちなみに

msoTable(=19)は「テーブル」ですが、

これはPowerPointで使用されている値でExcelでは使われません。

また

これは異なるTypeの図形が2つ以上選択されている場合になります。
そのためActivesheetではなくSelection.shapeRange.typeで使用する事になりますが、Selectionに関しては色々な事があるので次の記事でテーマにする予定です。

ShapeオブジェクトのTypeプロパティを使用する際の注意点

ところでShapeオブジェクトのTypeプロパティを使用するためにはShapesオブジェクトから1つの図形を取り出す必要があります。

つまり下記のようになります。

ただし注意が必要なのは対象がActivesheetだとすると、Activesheetに最低1つの図形が存在していないとShapesオブジェクトから1つ図形を取り出す事ができません。

「ShapesオブジェクトのItemメソッドでShapeオブジェクトを取り出す」の章でご説明したすべての図形を取り出すコーディングであれば問題ないのですが、あまり深く考えずにインデックスを付けて1つの図形だけ取り出してコーディングしてしまうと、Activesheetに図形が無い場合はエラーになるので注意が必要です。

下記のコーディングのパーツはActivesheetのIndex=1の図形がフォームコントロールであるかを確認する場合です。

If ActiveSheet.Shapes.Count <> 0 Then
    If ActiveSheet.Shapes(1).Type = msoFormControl Then
...

※ActiveSheet.Shapes.CountはActivesheetに図形が無い場合は0を返してくれます。(エラーにはなりません。)

Shapesオブジェクに「データの入力規則がリストの場合」が含まれているか?

先の章でフォームコントロールには「データの入力規則がリストの場合」も含まれる事を話しましたが、実際にシート上に入力規則を設定して確認して見ます。

• ホームタブメニュの編集グループの「検索と選択」→「オブジェクトの選択と表示…」から「選択」サブウインドウを開くと「Drop Down XX」と表示されます。XXは描画オブジェクトに自動的に付与される連番です。

このように「データの入力規則がリストの場合」は「選択」サブウインドウに描画オブジェクト名が表示される事が解ります。ただしこれで章題の件が解決される分けではありません。

「データの入力規則がリストの場合」の特殊性—その1

• 開発タブメニュのコントロールグループの挿入をクリックすると左図のウインドウが開くので、赤枠の「コンボボックス」のアイコンをクリックしてシート上に配置すると…

• 「選択」サブウインドウには「データの入力規則がリストの場合」と同様に「Drop Down XX」と表示されます。

つまり両者は同じ描画オブジェクト名が付き、MsoShapeType列挙(Enumerations)も共にmsoFormControl(=8)となるので区別する事ができません。

なお「選択」サブウインドウの上図の赤矢印の位置に境界線が表示されていて、どうやら何かしら描画オブジェクトの区別がされているようなのです。

「データの入力規則がリストの場合」の特殊性—その2

次に「データの入力規則がリストの場合」と「コンボボックス」をそれぞれをシートに追加設定して見ます。

※「コンボボックス」の方は2つの描画オブジェクトが表示されます。

• 左図では「データの入力規則がリストの場合」をA1セルに追加してC1セルにも設定しているのですが、「選択」サブウインドウには一つしか表示がされません。
• 連番は後から追加した番号になっていて、A1セルをクリックしても連番の表示は変わらない
• 図形の名前を変える事ができない。
• 左図のようにC1セルが選択されているのに「選択」サブウインドウの「Drop Down XX」には背景色が付きません。

ところで境界線の向こうに表示されるオブジェクトはもう一つあって、それは「コメント」なのですが次にコメントの場合の動きを見て行きます。

• A3セルとC3セル(左図下矢印)に「コメント」を設定しています。

「選択」サブウインドウの境界線の向こうに2つの描画「Comment XX」が表示されるので動きとしては問題ありません。

なお「コメント」はデフォルト非表示なので「選択」サブウインドウの「目のアイコン」は取消線が引かれていますが、「データの入力規則がリストの場合」の「Drop Down XX」も「目のアイコン」に取消線が引かれています。

これは入力規則を設定したセルからフォーカスが移動している状態を表しています。そのために例えば上図でA1セルをクリックすれば「目のアイコン」の取消線は無くなります。

また「コメント」はC2セルにフォーカスが当たっただけでは「選択」サブウインドウの「Comment XX」には背景色が付きません。

描画オブジェクトは自分自身が選択されると「選択」サブウインドウの自身の名前に背景色が付きますが、「コメント」の場合は吹き出しにフォーカスが当たって始めて背景色が付きます。

以下は「コメント」の表示・非表示に関する余談になります。

①「コメント」を設定したC2セルを選択してマウスの右ボタンでショートカットメニューを表示して「コメントの表示/非表示」をクリックします。
クリックすると文言が「コメントを表示しない」に変わります。

この時「選択」サブウインドウの「目のアイコン」は取消線が引かれています。

　
②「コメント」が表示されるとともに、「選択」サブウインドウの「目のアイコン」の取消線は消えます。

　
　
　
　
　
　
③　②の状態で、今度は「選択」サブウインドウの「目のアイコン」をクリックすると取消線が引かれます。
※左図で「選択」サブウインドウの「Comment XX」に背景色が付いているのはマウスのカーソルが当たっているためです。
　
　
　
　
　

④　③の状態でC2セルを選択してマウスの右ボタンでショートカットメニューを表示すると、「選択」サブウインドウの「目のアイコン」の取消線は消えてしまいます。
ショートカットメニューの表示は「コメントを表示しない」になりますが、恐らく「目のアイコン」の取消線を引いても変わっていないと思われます。(確認できませんが…)

以上のことから、ショートカットメニューの「コメントの表示/非表示」の操作の方が上位で、「選択」サブウインドウの「目のアイコン」の操作はショートカットメニューを表示させただけでも置き換えられてしまうと推測されます。

「データの入力規則がリストの場合」の特殊性—その3

次に左図のように「データの入力規則がリストの場合」の「目のアイコン」に取消線が引かれた状態でSheet1をシートコピーした時の動きを見て行きます。
　
　
　

• 境界線の向こうに表示されている「コメント」につきましては「目のアイコン」に取消線が引かれた状態でもそのまま「選択」サブウィンドウに表示されているのに対して、「データの入力規則がリストの場合」の「Drop Down XX」は表示されていません。(赤い右矢印)

• ただし、入力規則が設定されているC1セルをクリックすると表示が復活します。

「データの入力規則がリストの場合」の特殊性—まとめ

「データの入力規則がリストの場合」の描画オブジェクトとしての動き方を3点ほどご紹介しました。

描画オブジェクトの「コンボボックス」の装いをまといながらもセルの制約を受けるため、次の点が他の描画オブジェクトとは異なっています。

1. 入力規則が複数あっても描画オブジェクトとしては1つにまとめられてしまう。
2. 描画オブジェクトとして常に認知されている分けではない。
   • 「選択」サブウィンドウから消えた状態ではShapes.Countには含まれない。
   • シートのコピー以外でも「選択」サブウィンドウから外れる事象が散見される。(再現性はないが…)

従いまして

1. Shapesオブジェクではすべての「データの入力規則がリストの場合」を扱う事はできない。
2. 「データの入力規則がリストの場合」を描画オブジェクトとしては扱うには「選択」サブウィンドウから消えた状態では無い事を確認する必要がある。
3. 「データの入力規則がリストの場合」とフォームコントロールの「コンボボックス」を見分けなければならない。

という結果になります。

「1.」を除けばこれらを解決する手段が無いわけではありませんが少し面倒くさい話になります。
※ここで扱うと更に長くなってしまうのでこの話題は別の記事でご紹介する予定です。

ここでは「1.」の『Activesheetにどれだけ「データの入力規則がリストの場合」が含まれているか?』を知るにはどのようにすれば良いか?を最後に見て行きます。

RangeオブジェクトのSpecialCellsメソッドで「データの入力規則がリストの場合」が含まれているかを知る

「入力規則が設定されているかを調べるExcelマクロ」につきましては下記「インストラクターのネタ帳」のサイトで詳しく説明されていましたので、説明を割愛いたします。是非一度サイトをご参照いただければ幸いです。

入力規則が設定されているかを調べるExcelマクロ:Excel VBA Rangeオブジェクト/入力規則・Validation

アクティブシートにデータの入力規則の設定されているセルが存在するかどうかを調べるExcelマクロをご紹介しています。

www.relief.jp

なお「Cells.SpecialCells(xlCellTypeAllValidation)」は入力規則が設定されていないシートで実行するとエラーになってしまいます。
※なおxlCellTypeAllValidationはXlCellType列挙(Enumerations)の値の1つで「(入力)条件の設定が含まれているセル」を表します。

エラーを回避できるような入力規則の存在を教えてくれるプロパティを探したのですが、残念ながらVBAの中では見つかりませんでした。

そのためこのエラーを回避するためには「On Error ステートメント」を使用する必要がありますのでご注意ください。

また、「設定されている入力規則の数」を調べたい時は次のようにCountプロパティを記述する事ができます。

activesheet.cells.SpecialCells(xlCellTypeAllValidation).count

※このコーディンクも先程ご説明したのと同様にシートに入力規則が存在しないとエラーになります。

最後に

今回は「Shapesオブジェクにどのような描画オブジェクトが存在するか?」を知るための「ItemメソッドとRangeプロパティの使い分け」を中心に、Shapesオブジェクに関する話題をご紹介しました。

「図形を表す 3 つのオブジェクト」がコーディングでShapesオブジェクを操作する上では重要な要素になる認識です。

なお次回は文中でも取り上げましたSelectionに関する話題をご紹介したく存じます。

Selectionには「選択された描画オブジェクト」だけではなくセル・セル範囲も含まれますので、Shapesオブジェクトよりも面倒くさいところがあります。また「選択された描画オブジェクト」には色々な状態がありますので一筋縄では処理できません。

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