Excel2010においてユーザー定義関数より Shape オブジェクトの削除および追加を行うと、シートへの行または列の追加時にエラー メッセージ "オブジェクトがシートからはみだします。その操作はできません" が表示される

Question

Excel2010においてユーザー定義関数より Shape オブジェクトの削除および追加を行った後、セル内の数式を削除し、元に戻す操作（CTRL+Z）を行うと、行または列の挿入時にエラー メッセージ "オブジェクトがシートからはみだします。その操作はできません" が表示されます。

環境

 Windows8 64bit

 Excel2010 32bit

 IE10

事象が発生するユーザー定義関数

Public Function Test()

    Dim ws  As Worksheet

    Dim shp As Shape

    Set ws = ActiveSheet

    For Each shp In ws.Shapes

        shp.Delete

    Next

    With ws.Shapes.AddPicture( _

      "C:\image.bmp", msoFalse, msoTrue, ws.Range("B1").Left, ws.Range("B1").Top, 0, 0)       

        .ScaleHeight 1, msoTrue

        .ScaleWidth 1, msoTrue

        .Placement = xlMove

    End With

End Function

再現方法

１．セル A1 に =Test() を入力します。（セル B1 に 画像が表示されます。）

２．セル A1 を選択し Delete キーにより数式を削除します。

３．CTRL+Z を押し、削除した数式を復元します。

４．任意の行または列を選択し、コンテキストメニューより 挿入 操作を行います。

上記操作で、エラーメッセージ "オブジェクトがシートからはみだします。その操作はできません" が表示されます。

対応

http://support.microsoft.com/kb/211769/ja に示されている解決方法をすべて試しましたが解決しませんでした。

エラーが発生した場合、一度ブックを上書き保存し、閉じて開き直すと問題なく行または列の挿入が可能となることを確認しました。

上記コードの .Placement = xlMove を .Placement = xlMoveAndSize に変更するとエラーが発生しないことを確認しました。

Excel2013ではエラーが発生しないことを確認しました。

図形サイズを固定したいため、Shapeオブジェクトの Placement プロパティを xlMove のまま使用したいのですが、問題なく動作させるにはどのようにすればよいでしょうか。

Answer 1

大体の趣旨は分かりましたが・・・・・・

正直なところ、ブックの再計算により、シート関数によりダイナミックに操作するという発想は持ったことがありませんし、やったこともありません。

少なくとも、私の経験の範囲では他の方のコードでもそのようなオブジェクト操作をシート関数からやるというのは見たことがありません。

Windows8+Excel2010,2013の環境（ただしすべて32ビット）はテスト用に作ってありますので、ご要望があれば、提示されているコードの追検証はできますが、ご自分でかなり入念にテストされているようなので、多分同じ結果がでるだけであまり意味はないと思います。

話はだいぶ趣旨とは異なると思いますが、別シートに張り付けてある図を他のシートに切り替えて表示するテクニックもあることはあります。

http://www.officetanaka.net/excel/function/tips/tips14.htm

参考まで。

Answer 2

y sakuda 様

ご返信ありがとうございます。

仰る通り、画像挿入の簡易化を目的としています。

やりたいことのイメージとしましては、アドイン関数化し、引数に商品コードを指定すると、共有フォルダ内の商品画像ファイルのパスを動的に生成し、関数が入力されているセル上に画像を表示するというものです。

（提示したコードでは、シート上のすべての Shapeオブジェクトを削除するようになっていますが、実際は表示先セル上の画像のみ削除するようになっています。）

商品画像が差し替えられた場合でも、再計算により常に最新画像が表示されるようにしたいと考えています。

どのセルに表示するかはユーザー任意。(関数の設定先である表のフォーマットは様々。)

1セルに関数を設定し、コピー＆ペーストかフィルで他のセルにも入力したい。

オフラインの場合もあるためリンク画像にはできないと思っています。

やはり、ユーザー定義関数でのオブジェクト操作は無理があるでしょうか。

Answer 3

回答ではありません。悪しからず。

普通はFunctionをシート上で使用してオブジェクトの操作をすることは必要はないはずですので、操作のイメージが今一つ理解できません。

図の挿入を式でやることにより、簡略化しようとされているのでしょうか？

もし、そういうことなら、セルを選択して、右クリックしてコンテキストメニューからSubを起動してそのセルの位置に図を挿入するようなやり方の方がスマートだと思いますが、そのようにはできないのでしょうか？