[Excel VBA] PageSetup.PrintAreaによるシートの印刷範囲の変更を行うときにエラーになる場合＆その回避方法

Jan 15, 2021 · この記事は約4分で読めます · VBA Excel ·

Share on:

概要

この記事について

ワークシートオブジェクトのPageSetup.PrintAreaプロパティを用いて
条件に従ってシートの印刷範囲を変更するような処理を実装したい場合があるかもしれない。

そのとき、セルの参照形式に気をつけないと、思わぬエラーになる可能性がある。

この記事で、エラーの発生ケースとその二通りの回避方法について記したい。

説明のために作成したExcelファイルとソースコード、テスト用データはこちらでダウンロードできます。

検証環境

Windows 10 Home(64bit)
MSOffice 2016

事例

About

ブックのシートに印刷範囲が設定されている。

その印刷範囲を、列は変えずに印刷範囲の下限をひとつ下の行に変更する処理をVBAで記述したい。
e.g. 印刷範囲が$A$1:$E$5ならば、関数実行後に印刷範囲が$A$1:$E$6となるようにしたい。

コード

 1'******************************************************************************************
 2'*関数名    ：changePrintAreaBeforeRevised
 3'*機能      ：PrintAreaをひとつ下の行に変更する 修正前
 4'*引数      ：
 5'******************************************************************************************
 6Public Sub changePrintAreaBeforeRevised()
 7    
 8    '定数
 9    Const FUNC_NAME As String = "changePrintAreaBeforeRevised"
10    
11    '変数
12    Dim prePrintAreaAddress As String
13    
14    On Error GoTo ErrorHandler
15    
16    With ThisWorkbook.Worksheets(1)
17    
18        '現在の印刷範囲アドレス
19        prePrintAreaAddress = .PageSetup.PrintArea
20        
21        '印刷範囲をひとつ下の行に変更する
22        .PageSetup.PrintArea = .Range(prePrintAreaAddress).Resize(.Range(prePrintAreaAddress).Rows.Count + 1).Address '★01
23        
24        Debug.Print .PageSetup.PrintArea
25        
26    End With
27
28ExitHandler:
29
30    Exit Sub
31    
32ErrorHandler:
33
34    MsgBox "エラーが発生したため、マクロを終了します。" & _
35           vbLf & _
36           "関数名：" & FUNC_NAME & _
37           vbLf & _
38           "エラー番号：" & Err.Number & vbNewLine & _
39           Err.Description, vbCritical, SOURCE_NAME
40        
41    GoTo ExitHandler
42        
43End Sub

R1C1参照形式だとエラー発生

内容

上記は、
参照形式がデフォルト（A1参照形式）の場合はうまく動作する。

しかし、
R1C1参照形式を使用している場合、
★01の箇所でエラーとなる。

エラー

原因

PageSetupオブジェクトのPrintAreaプロパティは、
コード実行時点の参照形式によって取得文字列が異なる。

R1C1参照形式の場合はR1C1形式の文字列
A1参照形式の場合はA1形式の文字列

また、Rangeオブジェクトに指定する文字列は
A1参照形式のみ想定され、R1C1参照形式を許容していない。

したがって、
prePrintAreaAddressにはR1C1参照形式のアドレス文字列が格納され、
Range(prePrintAreaAddress)としてRangeオブジェクトに格納する時点でエラーとなる。

回避方法

参照形式自体を切り替える

 1'******************************************************************************************
 2'*関数名    ：changePrintAreaBeforeRevised
 3'*機能      ：PrintAreaをひとつ下の行に変更する 修正01
 4'*引数      ：
 5'******************************************************************************************
 6Public Sub changePrintAreaRevised01()
 7    
 8    '定数
 9    Const FUNC_NAME As String = "changePrintAreaRevised01"
10    
11    '変数
12    Dim prePrintAreaAddress As String
13    Dim currentStyle As XlReferenceStyle
14
15    On Error GoTo ErrorHandler
16    
17    With ThisWorkbook.Worksheets(1)
18        
19        'セルの参照形式をA1形式に変更
20        currentStyle = Application.ReferenceStyle
21        Application.ReferenceStyle = xlA1
22        
23        '現在の印刷範囲アドレス
24        prePrintAreaAddress = .PageSetup.PrintArea
25        
26        '印刷範囲をひとつ下の行に変更する
27        .PageSetup.PrintArea = .Range(prePrintAreaAddress).Resize(.Range(prePrintAreaAddress).Rows.Count + 1).Address
28        
29        Debug.Print .PageSetup.PrintArea
30        
31        'セルの参照形式を復旧する
32        Application.ReferenceStyle = currentStyle
33        
34    End With
35
36ExitHandler:
37
38    Exit Sub
39    
40ErrorHandler:
41
42    MsgBox "エラーが発生したため、マクロを終了します。" & _
43           vbLf & _
44           "関数名：" & FUNC_NAME & _
45           vbLf & _
46           "エラー番号：" & Err.Number & vbNewLine & _
47           Err.Description, vbCritical, SOURCE_NAME
48        
49    GoTo ExitHandler
50        
51End Sub
52

PrintArea取得・設定前後で
参照形式を強制的にA1参照形式に切り替える。

欠点は、
切り替え・復旧の間に時間がかかる処理がある場合や、
この関数を何度も呼び出す場合、
ユーザ側の視点から、シートの参照形式の部分が交互に変わってチラつくように見えるかもしれない。

アドレス文字列を別の参照形式に変更する

Application.ConvertFormulaを用いて
文字列だけを変更する。

 1'******************************************************************************************
 2'*関数名    ：changePrintAreaBeforeRevised
 3'*機能      ：PrintAreaをひとつ下の行に変更する 修正02
 4'*引数      ：
 5'******************************************************************************************
 6Public Sub changePrintAreaRevised02()
 7    
 8    '定数
 9    Const FUNC_NAME As String = "changePrintAreaRevised02"
10    
11    '変数
12    Dim prePrintAreaAddress As String
13    
14    On Error GoTo ErrorHandler
15    
16    With ThisWorkbook.Worksheets(1)
17    
18        '現在の印刷範囲アドレス
19        prePrintAreaAddress = .PageSetup.PrintArea
20        
21        'アドレスをxlA1参照形式のものに修正
22        If Application.ReferenceStyle = xlR1C1 Then prePrintAreaAddress = Application.ConvertFormula(prePrintAreaAddress, xlR1C1, xlA1)
23        
24        '印刷範囲をひとつ下の行に変更する
25        .PageSetup.PrintArea = .Range(prePrintAreaAddress).Resize(.Range(prePrintAreaAddress).Rows.Count + 1).Address
26        
27        Debug.Print .PageSetup.PrintArea
28        
29    End With
30
31ExitHandler:
32
33    Exit Sub
34    
35ErrorHandler:
36
37    MsgBox "エラーが発生したため、マクロを終了します。" & _
38           vbLf & _
39           "関数名：" & FUNC_NAME & _
40           vbLf & _
41           "エラー番号：" & Err.Number & vbNewLine & _
42           Err.Description, vbCritical, SOURCE_NAME
43        
44    GoTo ExitHandler
45        
46End Sub

終わりに

アドレス文字列を別の参照形式に変更する方法が最も自然で応用性も高いかと思う。

comments powered by Disqus

Translations:

"English" |