VBAクイズ問17～問20解説 VBA実務上のトラブル事例

Sub q_17()
    '前提
    With Workbooks.Add
        .Sheets(1).Cells(1, 1).Value = "ブック２"

        .Windows(1).WindowState = xlMinimized

        Application.DisplayAlerts = False
        .SaveAs ThisWorkbook.Path & "\" & "Book2.xlsx"
        Application.DisplayAlerts = True

        .Close
    End With

    '使用時
    ThisWorkbook.Windows(1).WindowState = xlNormal
    With Workbooks.Open(ThisWorkbook.Path & "\" & "Book2.xlsx")
        If Application.Version > 14 Then Debug.Print "" '2013以降ダミー出力
        Debug.Print Cells(1, 1)
        If Application.Version <= 14 Then Debug.Print "" '2010以前ダミー出力
        .Close False
    End With
End Sub

設問	正解
1.Excel 2007/2010の環境の場合
2.Excel 2013/2016の環境の場合	ブック２

私の会社では業務の進捗管理を行うエクセルの調書があって、それらの調書を週に一回管理システムに集計するマクロを実行しています。

このようなマクロにおいて、近年になって原因不明の誤動作が起きて利用者が右往左往していることがありました。

その時の経験を元にこの問題を作成しました。

まず前提の部分です。

新規でブック２を作成
A1にブック２と出力
ウィンドウ表示状態を最小化
名前を付けて保存
ブックを閉じる

ここで重要なのは、3の.Windows(1).WindowState = xlMinimizedです。

次に使用時の部分です。

現在のウィンドウ状態を標準に変更（最大化状態を解除）
ブック２を開く
Cells(1,1)の値を出力
ブック２を閉じる

ここで問題なのは、3のCells(1,1)ですね。

実はこのCells(1,1)ですが、厳密にはActiveWindow.ActiveSheet.Cells(1,1)の省略表記となります。

つまり、現在アクティブなウィンドウのシートのA1を出力するということですね。

で、アクティブなウィンドウって何でしょう？誰が決めたんですか？

たぶん、このコードを書いた本人は、Open直後だからアクティブになって当然だと思っていたのでしょうね。

ところが今回のように、開いたばかりのブックがアクティブウィンドウになっていないという現象が起こります。

これによってOfficeのバージョンによっては後のCells(1,1)の結果が変わります。

前者はこのプロシージャに入る前からアクティブだったブックのA1の内容：空欄が出力されます。

後者はBook2.xlsxのA1：ブック２が出力されます。

対策は簡単でCells(1,1)なんて書き方は止めて、ブック・シートを指定した書き方に統一することです。

今回の例ならWithしていますから、.ActiveSheet.Cells(1,1)とすれば済む話です。

何度も言いますがブックやシートの省略は止めましょう。

コードから読み取れることですが、再現条件は次の3つ全てに該当する場合です。

Openしたブックを前に保存した人が最小化したまま保存していること
実行しているExcelのバージョンが2007や2010であること。
使用中のExcelの子ウィンドウの表示モードが最大化されていないこと

1.まず最小化したまま保存とは、最小化されているのに、どうやったら保存終了出来るのかと言うと、次のようなパターンが考えられます。

* 2007や2010でExcelの右上の２つの×ボタンのうち上にある方で閉じた時
* 2013や2016でShiftを押しながら×ボタンで閉じた時
* 2013や2016でExcelの左上の端をダブルクリックで閉じた時
* タスクバーを右クリックして閉じた時
* Windowsのシャットダウン処理で終了確認が表示されて閉じた時

特に2013や2016ではSDIに変わったことで、最小化したまま保存終了されることがとても増えました。

2.次にバージョンが2007や2010であること

これは2010まではMDIだった事が関係しています。今更試すことは出来ませんが、2003以前もMDIでしたので同様の症状は起こるのかも知れません。

MDIだからこそ次の条件が生まれます。

3.使用中のExcelの子ウィンドウの表示モードが最大化されていないこと

どういうことか分かりづらいと思いますので、ここで動画をお見せします。

（が大人の事情により動画が用意できませんでした。）

というわけで、今後は最小化されたブックがあるかも知れないという点を考慮して、連続処理の実行前に最大化しておく処置を取ったほうが良いかもしれません。

今後は2010以前の環境は減って行くと思いますから、類似するトラブルは減りそうですけどね。

そもそもブック・シートを省略した記法に大きな問題があるので、Open時にWith文を使い.Cells()などとするか、Workbook型の変数にブックは格納しておくことが必須かと思います。

問18

Sub q_18()
    Charts.Add Before:=Worksheets(1)
    Debug.Print Worksheets(1).Name
End Sub

設問	正解
1.	Sheet1

罠でも何でもない問題です。

Excelのシートには、ワークシート(WorkSheet)、グラフシート(Chart)、他色々があります。

それら全てのシートを管理しているのがSheetsコレクションで、ワークシートのみを管理しているのがWorksheetsコレクションというわけです。

上記のコードではグラフシートをワークシートの前に挿入しています。

その後Worksheetsコレクションの１番目のワークシートのい名前を取得しているため、グラフ1ではなくSheet1が出力されます。

もしグラフ1を取得したいならSheets(1)とするかCharts(1)とする必要があります。

詳しくはこの辺が参考になると思います。

Office TANAKA - SheetsとWorksheetsって何が違うの？

普段はあまり意識する機会は無いのですが、グラフシートやマクロシートを使っている場面では、シートを先頭に移動／挿入させたつもりが移動されなかった。なんていう場面で困る事があるかも知れませんね。

問19

様式シート「tmp」を非表示で持たせておいて、それをコピーして使うような場合に発生する問題です。

プログラム通りなら3番目のシートはtmp (2)になっているはずですが、実際にはtmpが出力されます。

ここでは順番に流れを追ってみます。

Sub q_19()
    Dim sh  As Object
    '実行前は Sheet1 のみが存在

    'Sheet1の次に新規シートを追加(Sheet2)
    Worksheets.Add After:=Sheets(1)
    'shにSheet2をセット
    Set sh = ActiveSheet
    'Sheet2の名前をtmpに変更
    sh.Name = "tmp"
    'Sheet2を非表示
    sh.Visible = xlSheetHidden

    'tmpの次にtmpのコピーを追加(tmp (2))
    sh.Copy After:=sh
    'これで、Sheet1、tmp、tmp (2)の順で並んでいるはずである。

    '3番目のシートの名前を出力
    '　＞なぜか tmp と出力される。
    Debug.Print Worksheets(3).Name
End Sub

ちなみに全てのシートを表示状態にすると、Sheet1、tmp (2)、tmpの順で並んでいます。

表でも説明しましょう。

追加前はこうでした。

インデックス	1	3
シート名	Sheet1	tmp
表示	ON	OFF

ここでWorksheets("tmp").Copy After:=Worksheets("tmp")すると、なぜかこうなります。

インデックス	1	2	3
シート名	Sheet1	tmp (2)	tmp
表示	ON	OFF	OFF

ちなみに、可視シートが右にある場合は問題ありません。

例えば追加前はこんなシート構成のとき

インデックス	1	2	3
シート名	Sheet1	tmp	SheetEND
表示	ON	OFF	ON

ここでWorksheets("tmp").Copy After:=Worksheets("tmp")すると、正常にtmpの右に追加されます。

インデックス	1	2	3	4
シート名	Sheet1	tmp	tmp (2)	SheetEND
表示	ON	OFF	OFF	ON

実際にこのようなことをする時は、tmp (2)に相当するシートは最終的に可視化するはずですから、tmpシートを可視化してからコピーしてその後不可視にするのが良いかと思います。

つまり、シートをコピーする部分をこうすればOKです。

    sh.Visible = xlSheetVisible
    sh.Copy After:=sh
    sh.Visible = xlSheetHidden

問20

Sub q_20()
    With Workbooks.Add
    
        '前提
        With ActiveSheet.Shapes.AddShape( _
                Type:=msoShapeHeart, _
                Left:=1, _
                Top:=1, _
                Width:=100, _
                Height:=100)
            .Name = "ハート 1"
        End With
        
        '使用時
        Dim shp As Shape
        For Each shp In ActiveSheet.Shapes
            If shp.Name = "ハート 1" Then
                Debug.Print shp.TopLeftCell.Address
            End If
        Next
        
    .Close False
    End With
End Sub

シェイプに名前を付けて管理する場合です。

こちらの実行結果は（結果なし）です。

実はと言うと、If shp.Name = "ハート 1" Thenは絶対にTrueにならないのでDebug.Printに到達しません。

エクセルのシェイプの名前の付け方はとても不思議な仕組みで、図を選択した時の表示名はハート 1ですが、実際にシェイプのNameプロパティを取得するとHeart 1が返ります。

なぜか設置初期状態のNameは和名ではなく英名がベースとなるようです。

しかし仮に名前を.Name = "a 1"に変えるとshp.Nameの結果もa 1となるため、Trueになり、a 1と出力されます。

つまり一度Nameを設定して初めてNameプロパティは表示されている名前と一致するのです。

ちなみに上記ではハート 1と設定しているはずですが、これまた厄介なことに設定変更前と同じ名前を設定した場合は、その処理は無かったことにされるようです。従って英名の方のNameは更新されない為、shp.Nameの結果は元と変わらずHeart 1のままのようです。

まとめ

今回は問17～問20について説明しました。

これで出題した全ての問題の解説は終了です。

また、こういうマイナーな問題をかき集めて、不定期で公開出来たらと思います。

以上

何か御座いましたらコメント欄、またはTwitterからどうぞ♪

それではまた来週♪　ちゅんちゅん(・8・)

えくせるちゅんちゅん

ことりがエクセルをちゅんちゅんするブログ

VBAクイズ問17～問20解説 VBA実務上のトラブル事例

前の記事

解説について

基礎知識

解説

問17

問18

問19

問20

まとめ