Cells.Countがオーバーフロー

0
    マクロでCells.Countを取得しようとするとオーバーフローします。これは、ベータ版で発見して報告したバグですが、製品版でも直っていないようです。残念。

    たとえば、次のコードはエラーになります。



    オーバーフローしているのは黄色い行です。



    これは当然ですね。Long型では入るはずがありません。
    しかし、受ける変数をDoubleに変えても



    それならバリアント型では



    いずれにしてもエラーです。
    ここでは、Cells.Countを変数に代入していますが「MsgBox Cells.Count」でも「Debug.Print Cells.Count」でも「If Cells.Count > 1 Then」でもオーバーフローします。
    どうやら、そもそもCellsのCountが怪しいようです。
    オブジェクトライブラリで調べてみると



    なるほど、Countプロパティ自体がLong型で宣言されているのですね。Excel 2007はワークシートの領域が広がりましたので、全セルの合計は17,179,869,184個です。Long型の許容値2,147,483,647を楽に超えています。おそらくこれが原因でしょう。

    ちなみに、次のようなコードもオーバーフローします。



    ここまでのマクロは標準モジュールに書きました。これをSheet1などのシートモジュールに書くと、エラー表示が異なります。



    これではデバッグもできませんね。
    もっとも、なぜか次のように明示的にWorksheetオブジェクトを指定してやると、全行数×全列数の計算結果は取得できるようです。理由は調べていませんが、不思議ですね・・・てゆーか、早いとこ直して欲しいです。






    AutoFilterメソッドの絞り込み

    0
      日付のグループ化」でも書きましたが、Excel 2007ではオートフィルタに新機能がいくつか追加されています。そのひとつが"日付フィルタ"です。



      「今月」や「先週」などを選択するだけで、指定した日付による絞り込みが可能です。ワンタッチで絞り込めるので、ひんぱんに日付を扱うユーザーにとっては朗報でしょう。この"日付フィルタ"は、内部的に"フィルタオプションの設定"を実行しているのだろうと、マクロ記録で調べてみました。すると案の定、RangeオブジェクトのAutoFilterメソッドが記録されました。
      AutoFilterメソッドの引数は次の通りです。

      Range.AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)


      Fieldは絞り込む列位置を指定します。条件はCriteria1とCriteria2に指定し、どんな絞り込みを行うかをOperatorに指定します。VisibleDropDownにFalseを指定するとドロップダウン矢印(▼)が非表示になります。
      Operatorには次の定数を指定できます。

      定数 値 説明
      xlAnd1抽出条件1と抽出条件2の論理演算子AND
      xlBottom10Items4表示される最低値項目(抽出条件1で指定される項目数)
      xlBottom10Percent6表示される最低値項目(抽出条件1で指定される割合)
      xlFilterCellColor8セルの色
      xlFilterDynamic11動的フィルタ
      xlFilterFontColor9フォントの色
      xlFilterIcon10フィルタアイコン
      xlFilterValues7フィルタの値
      xlOr2抽出条件1または抽出条件2の論理演算子OR
      xlTop10Items3表示される最高値項目(抽出条件1で指定される項目数)
      xlTop10Percent5表示される最高値項目(抽出条件1で指定される割合)


      太字がExcel 2007で追加された新しい定数です。"日付フィルタ"はOperatorに定数xlFilterDynamicを指定します。たとえば「明日」で絞り込むときは次のようにします。

      Sub Sample1()
      Range("A1:A7").AutoFilter Field:=1, _
      Criteria1:=xlFilterTomorrow, _
      Operator:=xlFilterDynamic
      End Sub


      Operatorに定数xlFilterDynamicを指定して"日付フィルタ"による絞り込みを行うときは、Criteria1に次の定数を指定できます。こちらはヘルプに載ってません。

      定数 値 説明
      xlFilterToday1今日
      xlFilterYesterday2昨日
      xlFilterTomorrow3明日
      xlFilterThisWeek4今週
      xlFilterLastWeek5先週
      xlFilterNextWeek6来週
      xlFilterThisMonth7今月
      xlFilterLastMonth8先月
      xlFilterNextMonth9来月
      xlFilterThisQuarter10今四半期
      xlFilterLastQuarter11前四半期
      xlFilterNextQuarter12来四半期
      xlFilterThisYear13今年
      xlFilterLastYear14昨年
      xlFilterNextYear15来年
      xlFilterYearToDate16今年の初めから今日まで
      xlFilterAllDatesInPeriodQuarter117期間内の全日付:第1四半期
      xlFilterAllDatesInPeriodQuarter218期間内の全日付:第2四半期
      xlFilterAllDatesInPeriodQuarter319期間内の全日付:第3四半期
      xlFilterAllDatesInPeriodQuarter420期間内の全日付:第4四半期
      xlFilterAllDatesInPeriodJanuary21期間内の全日付:1月
      xlFilterAllDatesInPeriodFebruray22期間内の全日付:2月
      xlFilterAllDatesInPeriodMarch23期間内の全日付:3月
      xlFilterAllDatesInPeriodApril24期間内の全日付:4月
      xlFilterAllDatesInPeriodMay25期間内の全日付:5月
      xlFilterAllDatesInPeriodJune26期間内の全日付:6月
      xlFilterAllDatesInPeriodJuly27期間内の全日付:7月
      xlFilterAllDatesInPeriodAugust28期間内の全日付:8月
      xlFilterAllDatesInPeriodSeptember29期間内の全日付:9月
      xlFilterAllDatesInPeriodOctober30期間内の全日付:10月
      xlFilterAllDatesInPeriodNovember31期間内の全日付:11月
      xlFilterAllDatesInPeriodDecember32期間内の全日付:12月


      新機能"日付フィルタ"に関する解説はここまでです。

      ところで!上の定数一覧を見て、何か「えっ!?」と驚くことに気づきませんか?先に言っておきますが、私はタイプミスをしていません。
      「期間内の全日付:2月」の定数をご覧ください。

      xlFilterAllDatesInPeriodFebruary
      ではなく
      xlFilterAllDatesInPeriodFebruray
      です。
      マクロ記録すると「xlFilterAllDatesInPeriodFebruray」が記録されますし、そもそも「xlFilterAllDatesInPeriodFebruary」という定数は定義されていません。"2月"を表す英単語は確かFebruaryでしたよね(^^;

      Googleで「Februray」を検索すると、けっこうヒットします。それだけ間違えやすい綴りということなのかもしれませんが、もしかすると「Februray」という綴りも認容されているのでしょうか。しかし、Excelでは次のように「February」しか表示されません。下図のセルC2は「mmmm」という表示形式で月名を表示しています。セルD2はTEXT関数でやってみました。ダイアログボックスは、マクロで「MsgBox Format("2007/2/1", "mmmm")」を実行した結果です。



      また、ユーザー設定リストも「February」で登録されてます。



      いったい、どうしたんでしょうね(^^;

      ベンチマーク(2)

      0
        Excel 2003とExcel 2007で、並べ替えの速度を比較してみました。
        セル範囲A1:A60000にランダムな数値を入力し、この範囲を並べ替えます。
        並べ替えのコードは、マクロ記録で生成されたコードを使いました。
        まず、Excel 2003の結果からご覧ください。


        Sub Macro1() ''マクロ記録で生成されたままのコード
        Dim i As Long, j As Long
        Dim Start As Long, Finish As Long
        Application.ScreenUpdating = False
        ''60000個の乱数を入力
        For i = 1 To 10
        For j = 1 To 60000
        Randomize
        Cells(j, 1) = Int(Rnd() * 1000000)
        Next j
        Start = GetTickCount()
        ''マクロ記録で生成されたコード(ここから)
        Range("A1:A60000").Sort Key1:=Range("A1"), Order1:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom, SortMethod:=xlPinYin, _
        DataOption1:=xlSortNormal
        ''マクロ記録で生成されたコード(ここまで)
        Finish = GetTickCount()
        Debug.Print (Finish - Start) / 1000 & "秒"
        Next i
        Application.ScreenUpdating = True
        End Sub


        1回目0.250秒
        2回目0.250秒
        3回目0.234秒
        4回目0.250秒
        5回目0.281秒
        6回目0.250秒
        7回目0.250秒
        8回目0.250秒
        9回目0.250秒
        10回目0.234秒
        平均0.250秒



        マクロ記録で生成されるコードは、決して最適化されていません。指定しなくてもいいプロパティなども律儀に規定値を指定したりしますので、不要な部分を削ったコードでもう一度やってみました。


        Sub Macro2() ''シンプルにしたコード
        Dim i As Long, j As Long
        Dim Start As Long, Finish As Long
        Application.ScreenUpdating = False
        ''60000個の乱数を入力
        For i = 1 To 10
        For j = 1 To 60000
        Randomize
        Cells(j, 1) = Int(Rnd() * 1000000)
        Next j
        Start = GetTickCount()
        Range("A1:A60000").Sort Key1:=Range("A1")
        Finish = GetTickCount()
        Debug.Print (Finish - Start) / 1000 & "秒"
        Next i
        Application.ScreenUpdating = True
        End Sub


        1回目0.266秒
        2回目0.235秒
        3回目0.234秒
        4回目0.250秒
        5回目0.234秒
        6回目0.250秒
        7回目0.265秒
        8回目0.250秒
        9回目0.250秒
        10回目0.234秒
        平均0.247秒


        あまり変わりませんね。差は誤差の範囲でしょう。
        さて、同じように60000個の数値をExcel 2007で並べ替えてみます。こちらも最初は、マクロ記録で生成されたコードを使います。
        なお、見ていただくとわかりますが、並べ替えに関するオブジェクトはExcel 2007で新しくなっています


        Sub Macro3() ''マクロ記録されたままのコード
        Dim i As Long, j As Long
        Dim Start As Long, Finish As Long
        Application.ScreenUpdating = False
        ''60000個の乱数を入力
        For i = 1 To 10
        For j = 1 To 60000
        Randomize
        Cells(j, 1) = Int(Rnd() * 1000000)
        Next j
        Start = GetTickCount()
        ''マクロ記録で生成されたコード(ここから)
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add _
        Key:=Range("A1"), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:A60000")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        End With
        ''マクロ記録で生成されたコード(ここまで)
        Finish = GetTickCount()
        Debug.Print (Finish - Start) / 1000 & "秒"
        Next i
        Application.ScreenUpdating = True
        End Sub


        1回目0.375秒
        2回目0.375秒
        3回目0.391秒
        4回目0.391秒
        5回目0.375秒
        6回目0.469秒
        7回目0.375秒
        8回目0.390秒
        9回目0.375秒
        10回目0.391秒
        平均0.391秒


        Excel 2003に比べて6割ほど遅くなったでしょうか。
        こちらも、マクロ記録で生成されたコードから不要部分をカットしてみましょう。


        Sub Macro2() ''シンプルにしたコード
        Dim i As Long, j As Long
        Dim Start As Long, Finish As Long
        Application.ScreenUpdating = False
        ''60000個の乱数を入力
        For i = 1 To 10
        For j = 1 To 60000
        Randomize
        Cells(j, 1) = Int(Rnd() * 1000000)
        Next j
        Start = GetTickCount()
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add _
        Key:=Range("A1")
        With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:A60000")
        .Apply
        End With
        Finish = GetTickCount()
        Debug.Print (Finish - Start) / 1000 & "秒"
        Next i
        Application.ScreenUpdating = True
        End Sub


        1回目0.359秒
        2回目0.391秒
        3回目0.391秒
        4回目0.391秒
        5回目0.375秒
        6回目0.391秒
        7回目0.375秒
        8回目0.390秒
        9回目0.375秒
        10回目0.375秒
        平均0.381秒


        気落ち速くなりましたかね。まぁ、ほとんど差はないと見ていいでしょう。

        続いてもう一つ確認です。
        Excel 2007では、このように新しいオブジェクトなどが新設されていますが、Excel 2003までの古いオブジェクトもそのまま使えるようになっています。つまり、Excel 2003で実行したコードは、Excel 2007でも同じように動作するはずなんです。上記のうち、Excel 2003で検証したコード「Sub Macro2 (シンプルにしたコード)」を、そのままExcel 2007で実行してみましょう。こちらは、結果だけお見せします。

        1回目0.406秒
        2回目0.391秒
        3回目0.391秒
        4回目0.406秒
        5回目0.406秒
        6回目0.406秒
        7回目0.406秒
        8回目0.421秒
        9回目0.421秒
        10回目0.406秒
        平均0.406秒


        一番遅い結果となりましたね。
        以上の検証から次のことがわかりました。

        ・Excel 2007では"並べ替え"のオブジェクトが新しくなった
        ・"並べ替え"の動作は、Excel 2003より遅くなった
        ・古いオブジェクトをExcel 2007で使うと遅くなった


        並べ替えの動作検証だけでは何とも言えませんが、Excel 2007のマクロで速度を気にするなら、Excel 2007用の新しいオブジェクトを使えってことでしょうか。引き続き、いろんな動作で速度を検証してみようと思います。

        残念なRecentFileオブジェクト

        0
          Excel 2007では、ファイルを開いた履歴である「最近使用したドキュメント」に新機能が追加されました。



          [ファイル]メニューを開くと右に表示されるファイル履歴は、上図のようにピンのアイコンが追加されました。このアイコンをクリックすると緑色になり、そのファイルは履歴から削除されなくなります

          さて、この「削除されるかどうか」のオン/オフですが、VBAから取得/設定することはできません。オブジェクトライブラリで確認すると、RecentFileオブジェクトには該当するプロパティが存在しないのです。



          たとえば、すべての"ファイル履歴"を取得して表示するには次のようにします。

          Sub Sample1()
          Dim msg As String, i As Long
          With Application
          For i = 1 To .RecentFiles.Maximum
          msg = msg & .RecentFiles(i).Name & vbCrLf
          Next i
          End With
          MsgBox msg
          End Sub




          余談ですが、RecentFileオブジェクトのNameプロパティは、次のようなファイル名を返します。

          • カレントドライブとカレントディレクトリが、ファイルのパスと同じ場合 → 名前だけ
          • カレントドライブは同じだが、カレントディレクトリがファイルのパスと異なる場合 → ドライブ名を除いたパスと名前
          • カレントドライブとカレントディレクトリが、ファイルのパスと異なる場合 → ドライブ名を含むパスと名前

          実際にやってみましょう。履歴に登録されているファイルはすべて「C:¥Work」フォルダに存在するとします。

          ・カレントディレクトリが「C:¥Work」の場合


          ・カレントディレクトリが「C:¥Tmp」の場合


          ・カレントディレクトリが「E:¥」の場合


          なお、RecentFileオブジェクトのPathプロパティは、常にファイルのフルパスを返します。

          さて本題に戻りましょう。ファイル履歴のうち、ピンをクリックして「履歴から削除されない」ファイルを見つけるにはどうしたらいいでしょう。適当なプロパティやメソッドがないのならレジストリを調べてみます。ファイルの履歴は

          HKCU¥Software¥Microsoft¥Office¥12.0¥Excel¥File MRU

          に記録されています。この場所はExcelのバージョンによって異なりますので注意してください。

          • Excel 5.0/95/97 → Recent File List
          • Excel 2000/2002/2003 → Recent Files
          • Excel 2007 → File MRU

          レジストリの値を見ると、各データは「item 1」「item 2」という名前で、次のように保存されていました。

          [F00000000][T01C71998A01347D0]*C:¥Work¥Book1.xls


          最初の[F00000000]が「履歴から削除するかどうか」の判定に使われているようです。

          • [F00000000] → 削除される(ピンのアイコン:オフ)
          • [F00000001] → 削除されない(ピンのアイコン:オン)

          次の[T01C71998A01347D0]は何かのIDに使われているのでしょう。今回のケースとは関係なさそうなので無視します。「*」から後ろにフルパスが記録されています。このレジストリデータを直接参照すれば、履歴に登録されているファイルが「履歴から削除される」かどうかを判定することができそうです。詳細な解説は割愛しますが、次のようなコードでやってみました。

          Sub Sample2()
          Dim WSH, RF, i As Long, msg As String, buf As String
          Set WSH = CreateObject("WScript.Shell")
          Set RF = Application.RecentFiles
          For i = 1 To RF.Maximum
          buf = WSH.RegRead( _
          "HKCU¥Software¥Microsoft¥Office¥12.0¥Excel¥File MRU¥Item " & i)
          If Mid(buf, 10, 1) = "1" Then
          msg = msg & "削除[×]" & Chr(9) & RF(i).Name & vbCrLf
          Else
          msg = msg & "削除[○]" & Chr(9) & RF(i).Name & vbCrLf
          End If
          Next i
          MsgBox msg
          Set WSH = Nothing
          End Sub




          上記のコードでは、レジストリデータの「10文字目」が「1」かどうかを判定しています。あまり美しいやり方ではありませんが、まぁとにかくこうして判定するということです。コードを簡素化するために、あえてエラー処理などはしていません。

          ファイルの履歴が削除されなくなるというのは、ユーザーにとってうれしい新機能です。せっかくの機能なのですから、専用のプロパティを用意して欲しかったですね。そうしたら、マクロからオン/オフを切り替えるなどの使い方もできるのに。

          ベンチマーク(1)

          0
            おそらく多くのユーザーが興味を持っているテーマだと思いますが、Webや雑誌などではあまり詳細に検証されるケースは少ないと感じます。今回は「新しいExcel 2007って、速度的にどーよ?」について書きます。ただ、Excel 2007は大きく仕様が変更されましたので、単純に同じマクロで検証するのは不適切なのかもしれません。そうした点も含めて、こうした動作速度の話は今後も何回かに分けて続けるつもりです。まずは、拙作「Excelベンチマーク」を使って測定してみました。

            「Excelベンチマーク」は、マクロで連続動作を自動的に実行し、終了までにかかった時間を計測する仕組みです。私のサイトのライブラリに登録してありますので、興味ある方はお試しください。今回使用したのは「xlBench2000」です。

            Office TANAKAのライブラリ(http://www.officetanaka.net/library/index.htm)

            xlBench2000では次のテストを行います。

            ・描画テスト:ランダムな位置にオートシェイプを挿入
            ・再計算テスト:3000個の参照式を再計算
            ・UserFormテスト:テキストボックスやリストボックスなどの変更
            ・グラフテスト:10系列の3Dグラフを再描画
            ・罫線テスト:罫線の描画と線種などの変更
            ・セル移動テスト:書式設定したセル範囲を移動
            ・ソートテスト:100個の乱数を並べ替え
            ・ブックの開/閉テスト:ブックを別名で40回保存して開く
            ・HTMLの開/閉テスト:ブック(HTML形式)を別名で40回保存して開く



            テストに使用したパソコンのスペックは、以下の通りです。いわゆるショップブランドのパソコンです。
            CPU:Intel(R) Celeron(R) プロセッサ D335 (2.80GHz)
            チップセット:Intel(R) 845GV チップセット
            メモリ:PC2700 768MB
            ビデオ:Intel(R) エクストリームグラフィックス 3D (Intel(R) 845GV 内蔵)
            OS:Windows(R) XP Home Edition Service Pack2

            さて、結果をご覧ください。単位は秒です。



            ほとんどのテストで、Excel 2007は散々な結果に終わりました。UserFormテストに関しては、ほとんど差がありません。これは画面を見た印象とも一致します。テキストボックスやリストボックスの操作はExcel 2003と同じくらいの速度でした。それ以外は・・・遅いですね(^^; このへんに関しては、新しいオブジェクトとの関連などいろいろな事情があるでしょうし、そうした事情の多くは公式なドキュメントとして公開されていませんので、まずは想像するしかないのですが。

            最初の描画テストは、ワークシート上にオートシェイプを挿入するテストです。中心となるコードは

            Sheet1.Shapes.AddShape(msoShapeOval, pos, pos2, 100, 100).Select
            Selection.ShapeRange.Fill.ForeColor.SchemeColor = _
            Val(Right(pos, 1))

            です。位置を決めるposとpos2は乱数で決めています。塗りつぶしの色は、乱数posの下一桁を設定しています。このコード自体は、Excel 2003でもExcel 2007でも問題なく動作します。ちなみに、Excel 2007では描画エンジンが新しくなりましたので、オートシェイプの初期値や設定できる項目などがExcel 2003に比べて変わっています。しかし、悲しいかな、Excel 2007(RTM版)でオートシェイプの挿入をマクロ記録しても、何も記録されません。ローカルウィンドウで調べたところ、従来と同じShapeオブジェクトが使われているようですし、従来と同じ方法でオブジェクトにアクセスできます。また、新しく追加されたオブジェクトの中には、オートシェイプに関係するオブジェクトが見あたりませんので、どうやらExcel 2007でもShapeオブジェクトで間違いなさそうです。遅くなった原因は、高機能になった新型の描画エンジンが原因なのでしょうか。

            まぁ、オートシェイプの挙動が遅くなったのは、まだ許せる範囲です。一般的なユーザーは、オートシェイプの挿入や設定に関して、それほど神経質ではないでしょう。しかし、次の再計算テストが遅くなったのは問題です。これは描画エンジンだとか、新しいオブジェクトだとかの話ではありません。表計算ソフトとして最も重要なポイントです。

            再計算テストは次のように行いました。



            セルA2に「=A1+1」という数式を入力します。その下のセルA3には「=A2+1」というように、上のセルに1を加えるという数式をセル範囲A2:A30001まで30000個準備します。セルA1を変更すると30000個の参照式が再計算されますので、この速度を計測しました。セルA1の変更は400回です。

            step = 4
            For i = 1 To 100 * step
            Sheet1.Range("A1") = i
            Next i

            ScreenUpdatingプロパティにFalseを設定して画面の更新を抑止するなど、マクロ的に高速化する手はいくつか考えられますが、何よりも一般的な手動操作で再計算が遅いようでは話になりません。何とかExcelの設定だけで速度の改善を検討したいところです。このへんの検証はかなり時間がかかりますので、後日あらためて行うつもりです。

            さて、最も遅くなったグラフの書き換えですが・・・これは、次のコンテンツにまとめようと思います。先日「1つのコンテンツが長すぎて読むのが大変」とお叱りのメールをいただきましたので(^^; この話は何度かに分けて書くことにしましょう。

            マクロはどう変わったのか

            0
              マクロユーザーにとってExcel 2007の関心といえば、新しいUIのリボンでも、設定数が無制限になった条件付き書式でも、カラフルになったグラフ機能でもなく、既存のマクロは動くのか?という一点でしょう。結論から言うとExcel 2007のマクロは基本的には変わっていません。もちろん、新機能に改良に合わせて新しいオブジェクトやプロパティが追加されていますが、今までのマクロは従来通り動くと考えていいでしょう。もちろん、すべてのマクロを動作確認したわけではありませんが、VBAの仕様から見てそう判断できます。

              たとえばVBEのオブジェクトブラウザでは、VBAで使用できるオブジェクトやプロパティの詳細を確認することができます。これを見る限り、Excel 2007の新しいVBAは、従来のVBAを継承していることがわかります。



              また、もっと詳しく調べたいときには「OLE/COM Object Viewer」を使うと便利です。これはVisual Basic 6.0などに付属するツールで、DLLの内部を調べて、プロパティやメソッドの定義を確認することができます。



              このツールで、新しいVBAのObject Libraryを見ると、従来の定義はほとんどそのまま残っているのがわかります。したがって、古いマクロでも今まで通りに動作するはずです。実際に、多くのマクロを試してみましたが、どれもエラーにはなりませんでした。


              しかし、安心はできません。Excel 2007は大きく仕様が変わった新しいExcelです。特にメニュー周りの変化には要注意です。何しろ、今まで当たり前に操作していたメニューとツールバーがなくなってしまったのですから。これは従来のマクロにも影響を与えます。たとえばマクロでツールバーを作成したり、独自のメニューを追加しているようなシステムは、Excel 2007ではエラーにはならないものの、今までと同じUIを提供することはできなくなりました。たとえば次のマクロは新しいツールバーを作成して表示します。

              Sub Sample1()
                Dim myBar As CommandBar
                Set myBar = CommandBars.Add
                myBar.Controls.Add ID:=23
                myBar.Controls.Add ID:=3
                myBar.Controls.Add ID:=2521
                myBar.Visible = True
              End Sub


              Excel 2003で実行すると次のようになります。



              ところが、そもそもツールバーという機能自体が廃止されたExcel 2007の実行結果は次の通りです。



              確かにエラーにはなりません。なりませんけど、これで納得できるでしょうか。実行すると、リボンに[アドイン]タブが追加され、[ユーザー設定のツールバー]グループにアイコンが追加されます。リボンはツールバーのように、ワークシート上でフローティング状態にすることはできません。操作するには、いちいちリボンのタブを切り替えなければならないのです。

              では、メニューを操作するマクロはどうでしょう。次のマクロは[書式]メニューの末尾に[文字色の色]という独自のメニューを追加します。

              Sub Sample2()
                With CommandBars(1).Controls("書式(&O)").Controls.Add()
                  .Caption = "文字列の色(&X)..."
                  .OnAction = "chgTextColor"
                  .BeginGroup = True
                  .FaceId = 1988
                End With
              End Sub


              Excel 2003では次のようになります。



              これがExcel 2007では、



              となります。今度は[アドイン]タブの[ユーザーメニュー]グループが追加されました。[書式]メニューに限った話ではありません。[ファイル]メニューだろうと[挿入]メニューだろうと、そのメニュー自体がなくなってしまったのですから、Addメソッドの追加先はすべて[ユーザーメニュー]グループとなります。

              メニューやツールバーを操作するようなマクロは、エラーにこそなりませんが従来と同じUIを提供できません。"使い物にならない"と言っても過言ではありません。これはVBAが変わったというより、Excel 2007(だけじゃありませんが)のメニューシステムそのものが大きく変わったためです。

              なお、マクロではありませんが、今までのExcelには「ブックにツールバーを添付する」という機能がありました。独自に作成したりカスタマイズしたツールバーをブックに添付すると、別のパソコンにツールバーを移動することができました。そのように、ツールバーを添付したブックをExcel 2007で開くと、マクロでツールバーを作成したときと同じようになります。



              [アドイン]タブに追加されたツールバーとメニューは、右クリックから削除できます。





              Topページへ戻る
              calendar
                  123
              45678910
              11121314151617
              18192021222324
              252627282930 
              << November 2018 >>
              selected entries
              categories
              archives
              recent comment
              recent trackback
              recommend
              recommend
              recommend
              recommend
              recommend
              links
              profile
              search this site.
              others
              mobile
              qrcode