"BOKU"のITな日常

62歳・文系システムエンジニアの”BOKU”は日々勉強を楽しんでます

EXCEL2007とEXCEL2016で画像のサイズ変更のVBA動作に非互換/EXCELの小ネタ

EXCELのデフォルト設定が変わって、今まで動いていたVBAが動かなくなる。

よくある話なので驚きはしないですが、今回は、2つの原因が同時に発生していて、ちょっと慌てました。

意外に、情報がネットにあがってないように思えたので、備忘がてら書いておきます。

f:id:arakan_no_boku:20190307220715j:plain

 

画像のサイズ変更などの一括処理の非互換が・・

 

もとはこの記事です。

arakan-pgm-ai.hatenablog.com

ここで、枝葉の処理として以下のことをやってます。

  • フォームボタン以外の画像があれば、全部消す。
  • ファイルから画像を挿入してフォームボタン以外の画像を縦横比固定にして横幅を拡大して表示する。

ごくシンプルな処理ですが、EXCEL2007で問題なく動作していたものが、EXCEL2016では動かなく(つまり、削除もされず、サイズ変更もされない)なりました。

現在は、上記記事のソースも修正して、EXCEL2016で動くようにしてますが、非互換情報を確認した時に気づかなかった部分なので驚きました。

なお、今更、EXCEL2007かよ!というツッコミはごめんなさい。

実は、つい最近までプライベートではEXCEL2007しか持ってなかったのです(笑)

 

原因はEXCELのデフォルト値が変更になったから

 

今回EXCEL2007とEXCEL2016の非互換で問題になったのは、以下の2か所です。

  • Pictures.Insertで画像ファイルを取り込んだShapeのTypeが「msoPicture」から「msoLinkedPicture」に変わっていた。
  • プロパティのデフォルトが「セルに合わせて移動するが、サイズ変更をしない」になっていた。 

  

ShapeのTypeが「msoPicture」から「msoLinkedPicture」に変わっていた

 

何故、これが困るかというと、VBAで画像とかを扱う時、TypeでShapeの種類を確認して処理しなければいけないからです。

例えば、シートの画像を削除したいとき

For Each img In Sheets(mainSheetName).Shapes
        img.Delete
Next img

こうやってしまうと、指定シートの画像もフォームオブジェクト(ボタンとか)も全部消えていまいます。

だから、ボタンは消したくない場合とかは

For Each img In Sheets(mainSheetName).Shapes
    If img.Type <> msoFormControl Then
        img.Delete
    End If
Next img

 という風に、Typeで除外するものを指定する必要があります。

上記ならEXCEL2007でもEXCEL2016でも動きます。

でも「Pictures.Insert」で取り込んだ画像イメージだけを消したいと思って、今回は以下のようにしていたところ。

For Each img In Sheets(mainSheetName).Shapes
    If img.Type = msoPicture Then
        img.Delete
    End If
Next img

EXCEL2007では画像が削除されますが、EXCEL2016では削除されませんでした。

デフォルトのTypeが違っているのですから。

まあ、この2種類程度なら、msoPicture または msoLinkedPictureみたいに、ORで複数のTypeを指定する方法で何とかなるでしょうが・・なんだかなあ。

 下位互換で何とかならないんですかねえ。

 

プロパティのデフォルトが変更になっていた影響

 

VBAでサイズ変更するときは以下のようなコードでやってました。

Sheets(mainSheetName).Pictures.Insert imgFileName
For Each img2 In Sheets(mainSheetName).Shapes
    If img2.Type <> msoFormControl Then
        img2.LockAspectRatio = True
        img2.Width = 150
    End If
Next img2

サイズ変更する対象のTypeを指定する必要があるのは、前記の削除と同じです。

フォームのボタンまでサイズ変更するわけにはいきませんから。

それで「msoFormControl以外」という条件でサイズ変更してます。

削除はうまく言ったから大丈夫だろうと思うと、画像のサイズ変更ではまだ条件が足りませんでした。

この原因がプロパティでした。

EXCEL2007の時と異なり、EXCEL2016ではプロパティがデフォルトで「セルに合わせて移動するが、サイズ変更をしない」になっているからです。

だから、EXCEL2016の場合は「 XXX.Placement = xlMoveAndSize」のように、移動もサイズ変更も可能だよと、プロパティを変更してやらないといけません。

Sheets(mainSheetName).Pictures.Insert imgFileName
For Each img2 In Sheets(mainSheetName).Shapes
    img2.Placement = xlMoveAndSize
    If img2.Type <> msoFormControl Then
        img2.LockAspectRatio = True
        img2.Width = 150
    End If
Next img2

 後者のコードなら、EXCEL2007でも動きますから、ShapeのPlacementプロパティに「xlMoveAndSize」を設定するのは、サイズ変更等をするなら必須と思ったほうがよさそうです。 

これで動くようになりました。

やれやれ・・です。