水平改ページでエラー『インデックスが有効範囲にありません。』が出た![VBA]
こんにちは。
仕事の自動化にやりがいと達成感を感じるガッくんです。
この記事の目次
背景・目的
VBAで大量ファイルの印刷設定を行っていたら、水平の改ページでエラーが出てハマったのでまとめておきます。
エラーが出たプログラム
Excel
Excelで『 1 』をA列〜I列、1行〜144行の範囲全てに入力し、改ページプレビュー設定にしました。ソースコード
図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 のソースコードが問題であったことが分かりました。VBAの出力状況
出力内容を確認すると、ページ数は 2 ページなので水平の改ページ数は『 1 』になるはずですが、水平改ページ数が『 2 』になっています。
原因究明と試行錯誤
改ページを 1 行ズラすと 3 ページ目が現れました。
たまたま改ページが外枠の印刷範囲の線と同じ位置にあったようです。
改ページが印刷範囲の 1 番下の行と同じ位置なら何もしない様に VBA を書き換えたいのですが、印刷範囲は簡単に VBA から取得出来ますが、そもそも上のエラーが出てしまうため、改ページの位置が取得出来ません。
そこで、とりあえず印刷範囲を取得してみることにしました。
ちなみに、 3 ページ目が存在する状態で VBA を実行するとエラーは出ませんでした。
修正したプログラム
ソースコード
図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 』になっています。
また、エラーが出なくなりました。
コメント
たまたま解決策が発見できてしまいました。
VBAのシートオブジェクトの『HPageBreaks』プロパティは印刷範囲の設定が無いと印刷範囲の1番下と改ページがたまたま同じ位置の時に来た時にインデックスエラーが発生します。
改ページをVBAから操作する時は印刷範囲の設定をきちんとしましょう。
以上