Excel2007 VBAで全てのコマンドバーを参照すると異常終了する

Question

Excel 2003のVBAプログラムを2007対応させる中で、以下症状に悩んでおります。

CommandBarオブジェクト、CommandBarControlオブジェクトの

全てを参照するマクロを実行すると、Excelが異常終了してしまいます。

原因や対策について、助言頂けると大変助かります。

On Error Resume Next で扱えるエラー数には上限がある？ ようにも見受けられるのですが、

リボン機能との絡みとかもあるかも知れないので、このあたりの情報提供も歓迎します。

以下サンプルコードです。

'============================================================

' 全てのコマンドバーを参照する

' ※Excel2003 では正常に動作するが 2007では異常終了する

'============================================================

Sub CommandBarCheck()

Dim myBars As CommandBar

Dim myBCtrl As CommandBarControl

Dim myBCtrl2 As CommandBarControl

Dim myBCtrl3 As CommandBarControl

On Error Resume Next

For Each myBars In Application.CommandBars

For Each myBCtrl In myBars.Controls

For Each myBCtrl2 In myBars.Controls(myBCtrl.Caption).Controls

For Each myBCtrl3 In myBars.Controls(myBCtrl.Caption).Controls(myBCtrl2.Caption).Controls

Next myBCtrl3

Next myBCtrl2

Next myBCtrl

Next myBars

End Sub

Answer 1

こんにちは。

正直この手の"禁止系"の処理はどこまで制御するかが難しい上に、大体の場合において抜け道があるので個人的にはお薦めしないのですが、どうしても処理するのであれば下記のような方法もあるかと思います。

・「Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"」を実行してリボン自体を非表示にする。

・各ショートカットキーを潰す。

・各右クリックメニューを潰す。

・独自のコマンドはフローティングツールバーに見立てたユーザーフォーム or オリジナルの右クリックメニュー/ショートカットキーから実行する。

Excelの各コマンドを無効にする方法もあることはあるのですが、手間が掛かりますので、私としては運用でカバー(○○は使わないでください等と通達)できるのであれば、運用でカバーすることをお薦めします。

Answer 2

>Excel既存のメニューバー、ツールバー、ショートカットメニューを操作禁止として、アプリ独自のメニューバーで制御するスタイルで開発されています。

私の伺っているのはそういう意味ではなく、例えば、

＞In myBars.Controls(myBCtrl.Caption).Controls

はコマンドバーの中の、コマンドバーと同じCaptionのコントロールの下のControlコレクションと言う意味になります。

感覚的にありえないものに見えるのですが、具体的にはどのコマンドバーのどのコントロールの下のコレクションを取得しているのかと言う意味です。

2007で異常が起こっているらしいことは分かっていますがプログラム自体もなにかおかしいように感じております。

Answer 3

レスありがとうございます。

ループ処理の背景について

Excel2000上で構築された古いアプリがあり、

Excel既存のメニューバー、ツールバー、ショートカットメニューを

操作禁止として、アプリ独自のメニューバーで制御するスタイルで

開発されています。

これらのコマンドバーを制御する中で、ループ処理が含まれています。

(例えばEnabledの一括設定など)

Excel2007からのリボン導入後は、上記の開発スタイル自体が

マッチしていないと認識してはいるのですが、最小の手間でExcel2007上で

稼働させることが課題となっております。

>確かに2007では異常終了しました。

>しかし、2010では正常終了しますね。

2010では動作するのですね。

情報ありがとうございます。

最悪、2007でなく2010にする手もありそうです。

2007で異常終了したと推測されるコマンドバーは、2003に存在せず

2007に追加されたもののようです。また、2010では動作するとのこと

なので、仮説ですが、2007で追加されたコマンドバーには後方互換上の

不具合があって、2010では修正されたのかもしれません。

Answer 4

回答はできませんので、結果報告のみ。

確かに2007では異常終了しました。

しかし、2010では正常終了しますね。

それからOn　ErrorはVBAの実行時エラーに対応しているだけで、Excel自身の異常終了には対応できません。

また、提示されたプログラム何をどういう趣旨でループさせているのかちょっと理解できません。

何をやろうとされているのか解説願います。

Answer 5

質問者ですが、その後把握できた情報です。

異常終了した箇所は、下記と思います。

コマンドバーの階層                 INDEX      Caption

---

CommandBar                                59  PivotTable Context Menu

CommandBarControl 1階層目   19  レポートにプロパティを表示(&P)

CommandBarControl 2階層目     3   プロパティをすべて表示しない(&H)

CommandBarControl 3階層目     1  ※

---

※： アクセスした際に異常終了   これが何かは不明

On Error Resume Next としているので、エラーは無視されると期待していたのですが、

ダメなようです。  異常終了なのでエラー番号も不明です。

このような状況ですが、何か情報ありましたら、宜しくお願いいたします。