解析エンジニアの自動化 blog

コツコツと自動化した方法を残す blog

水平改ページでエラー『インデックスが有効範囲にありません。』が出た![VBA]



こんにちは。
仕事の自動化にやりがいと達成感を感じるガッくんです。


この記事の目次



背景・目的


VBAで大量ファイルの印刷設定を行っていたら、水平の改ページでエラーが出てハマったのでまとめておきます。



動作環境


Windows 7
Excel 2007



エラーが出たプログラム

Excel

Excelで『 1 』をA列〜I列、1行〜144行の範囲全てに入力し、改ページプレビュー設定にしました。

図1 Excelの中身

ソースコード

図1 のExcelに下のソースコードを標準モジュールに追加しました。

Sub 改ページテスト()
   
    ' このブックのインデックス 1 のシート上で
    With ThisWorkbook.Worksheets(1)
        
        ' 水平改ページ数を表示
        水平改ページ数 = .HPageBreaks.Count
        Debug.Print "水平改ページ数 = " & 水平改ページ数
       
        ' 1 番下の水平改ページの行を表示
        水平改ページ行 = .HPageBreaks.Item(水平改ページ数).Location.Row
        Debug.Print "水平改ページ行 = " & 水平改ページ行
       
    End With
   
End Sub

エラー内容

実行すると図2 の様なエラーメッセージが表示され、『デバッグ』ボタンを押すと図3 のソースコードが問題であったことが分かりました。

図2 エラーメッセージ

図3 問題のソースコード

VBAの出力状況

出力内容を確認すると、ページ数は 2 ページなので水平の改ページ数は『 1 』になるはずですが、水平改ページ数が『 2 』になっています。

図4 イミディエイトウィンドウ



原因究明と試行錯誤

改ページを 1 行ズラすと 3 ページ目が現れました。

たまたま改ページが外枠の印刷範囲の線と同じ位置にあったようです。

改ページが印刷範囲の 1 番下の行と同じ位置なら何もしない様に VBA を書き換えたいのですが、印刷範囲は簡単に VBA から取得出来ますが、そもそも上のエラーが出てしまうため、改ページの位置が取得出来ません。

そこで、とりあえず印刷範囲を取得してみることにしました。

ちなみに、 3 ページ目が存在する状態で VBA を実行するとエラーは出ませんでした。

図5 改ページをズラす


図6 3 ページ目が出現



修正したプログラム

ソースコード

図1 のExcelに下のソースコードを標準モジュールに追加しました。

Sub 改ページテスト()
   
    ' このブックのインデックス 1 のシート上で
    With ThisWorkbook.Worksheets(1)
       
        ' 印刷設定が無い場合は
        If .PageSetup.PrintArea = vbNullString Then
           
            ' 表示
            Debug.Print "印刷設定がありません"
           
            印刷範囲1 = .Cells(1, 1).Address(0, 0)
            印刷範囲2 = .Cells(1, 1).SpecialCells(xlLastCell).Address(0, 0)
            印刷範囲 = 印刷範囲1 & ":" & 印刷範囲2
           
            .PageSetup.PrintArea = 印刷範囲
           
            Debug.Print "印刷範囲が無いので設定しました。"
           
        ' 印刷設定がある場合は
        Else
           
            ' 印刷範囲の最後の行を表示
            startRow = .Range(.PageSetup.PrintArea).Rows.Row
            endRow = startRow + .Range(.PageSetup.PrintArea).Rows.Count - 1
            Debug.Print "印刷範囲の最終行は " & endRow
           
        End If
       
        ' 水平改ページ数を表示
        水平改ページ数 = .HPageBreaks.Count
        Debug.Print "水平改ページ数 = " & 水平改ページ数
       
        ' 1 番下の水平改ページの行を表示
        水平改ページ行 = .HPageBreaks.Item(水平改ページ数).Location.Row
        Debug.Print "水平改ページ行 = " & 水平改ページ行
       
    End With
   
End Sub

VBAの出力状況

出力内容を確認すると、最初は印刷範囲は未設定だったので、入力範囲を印刷範囲に設定しました。

すると、ページ数は 2 ページで水平の改ページ数は『 1 』になっています。

また、エラーが出なくなりました。

図7 イミディエイトウィンドウ



コメント

たまたま解決策が発見できてしまいました。

VBAのシートオブジェクトの『HPageBreaks』プロパティは印刷範囲の設定が無いと印刷範囲の1番下と改ページがたまたま同じ位置の時に来た時にインデックスエラーが発生します。

改ページをVBAから操作する時は印刷範囲の設定をきちんとしましょう。



以上