EXCEL VBA: YOU MAY HAVE A ERROR ON CHANGING PRINTAREA DYNAMICALLY & HOW TO AVOID IT

Jan 15, 2021 · 5 min read · VBA Excel ·

Overview

Help me improve my English!

As you probably see, I'm not a native English speaker.
If you find an English expression that feel incorrect or awkward, please let me know.

Message box of Disqus is under the article.
Or my E-Mail is here.

ABOUT THIS ARTICLE

You may want your implementaion to change print area of a worksheet dynamically with PageSetup.PrintArea according to some conditions.

At that time, you may be subjected to a unexpected error if you are not careful with the Cell Reference Style of Excel App.

In this article, I'd like to describe the case the error occurs and two methods how to avoid it.

You can download Excel file created for explanation, and view its source code from here!

VERIFICATION ENVIRONMENT

Windows 10 Home(64bit)
MSOffice 2016

THE CASE

ABOUT

Let's assume that the worksheet of your Excel book has its print area set.

The program performs the processing to extend the area to one more line below.
e.g. If initial area is $A$1:$E$5, after executing it'll chnage to $A$1:$E$6.

CODE

 1'******************************************************************************************
 2'*Function :it's a function Before Modified
 3'*          extend PrintArea to one line below
 4'******************************************************************************************
 5Public Sub changePrintAreaBeforeModified()
 6    
 7    'Consts
 8    Const FUNC_NAME As String = "changePrintAreaBeforeModified"
 9    
10    'Vars
11    Dim prePrintAreaAddress As String
12    
13    On Error GoTo ErrorHandler
14    
15    With ThisWorkbook.Worksheets(1)
16    
17        'Current Print Area Address
18        prePrintAreaAddress = .PageSetup.PrintArea
19        
20        'extend PrintArea to one line below
21        .PageSetup.PrintArea = .Range(prePrintAreaAddress).Resize(.Range(prePrintAreaAddress).Rows.Count + 1).Address
22        
23        Debug.Print .PageSetup.PrintArea
24        
25    End With
26
27ExitHandler:
28
29    Exit Sub
30    
31ErrorHandler:
32
33    MsgBox "An error has occurred and the macro will be terminated." & _
34           vbLf & _
35           "Func Name:" & FUNC_NAME & _
36           vbLf & _
37           "Error No." & Err.Number & vbNewLine & _
38           Err.Description, vbCritical, SOURCE_NAME
39        
40    GoTo ExitHandler
41        
42End Sub

A ERROR OCCURED IF R1C1 USED

DETAIL

Above code works if you use A1 reference style.
But if you use R1C1 one, you'll get a error on the highlighted line.

The Error

CAUSE

PrintArea property of PageSetup object gets different addresses depending on application's reference style at the time the function is executed.

A1 style address string if A1 used
R1C1 style address string if R1C1 used

And the address string that Worksheet.Range requires must be A1 style, not allowed if R1C1 style.

Thus, prePrintAreaAddress stores the R1C1 styled address and the error occurs when Worksheet.Range gets prePrintAreaAddress as a argument.

HOW TO AVOID

PATTERN 1. SWITCH APPLICATION'S REF STYLE ITSELF

 1'******************************************************************************************
 2'*Function :it's a function after midification of pattern No.1
 3'*          extend PrintArea to one line below
 4'******************************************************************************************
 5Public Sub changePrintAreaModified01()
 6    
 7    'Consts
 8    Const FUNC_NAME As String = "changePrintAreaModified01"
 9    
10    'Vars
11    Dim prePrintAreaAddress As String
12    Dim currentStyle As XlReferenceStyle
13
14    On Error GoTo ErrorHandler
15    
16    With ThisWorkbook.Worksheets(1)
17        
18        'change the reference style to A1 style
19        currentStyle = Application.ReferenceStyle
20        Application.ReferenceStyle = xlA1
21        
22        'Current Print Area Address
23        prePrintAreaAddress = .PageSetup.PrintArea
24        
25        'extend PrintArea to one line below
26        .PageSetup.PrintArea = .Range(prePrintAreaAddress).Resize(.Range(prePrintAreaAddress).Rows.Count + 1).Address
27        
28        Debug.Print .PageSetup.PrintArea
29        
30        'restore the reference style
31        Application.ReferenceStyle = currentStyle
32        
33    End With
34
35ExitHandler:
36
37    Exit Sub
38    
39ErrorHandler:
40
41    MsgBox "An error has occurred and the macro will be terminated." & _
42           vbLf & _
43           "Func Name:" & FUNC_NAME & _
44           vbLf & _
45           "Error No." & Err.Number & vbNewLine & _
46           Err.Description, vbCritical, SOURCE_NAME
47        
48    GoTo ExitHandler
49        
50End Sub
51

Application's reference style is switched to A1 forcibly before setting PrintArea and restored after it.

The disadvantage is that,
when there is a time-consuming process between switching or when the function is called many times,
the user may see a screen flicker during the process because of switching the ref-style.

PATTERN 2. MODIFY THE REF STYLE OF THE OBJECT'S ADDRESS TO A1 STYLE

With Application.ConvertFormula, the address string stored a variable can be changed to A1 ref-style without involving Application.ReferenceStyle.

 1'******************************************************************************************
 2'*Function :it's a function after midification of pattern No.2
 3'*          extend PrintArea to one line below
 4'******************************************************************************************
 5Public Sub changePrintAreaModified02()
 6    
 7    'Consts
 8    Const FUNC_NAME As String = "changePrintAreaModified02"
 9    
10    'Vars
11    Dim prePrintAreaAddress As String
12    
13    On Error GoTo ErrorHandler
14    
15    With ThisWorkbook.Worksheets(1)
16    
17        'Current Print Area Address
18        prePrintAreaAddress = .PageSetup.PrintArea
19        
20        'modify the address of prePrintAreaAddress to xlR1C1 style
21        '** it doesn't change application's reference style
22        If Application.ReferenceStyle = xlR1C1 Then prePrintAreaAddress = Application.ConvertFormula(prePrintAreaAddress, xlR1C1, xlA1)
23        
24        'extend PrintArea to one line below
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 "An error has occurred and the macro will be terminated." & _
38           vbLf & _
39           "Func Name:" & FUNC_NAME & _
40           vbLf & _
41           "Error No." & Err.Number & vbNewLine & _
42           Err.Description, vbCritical, SOURCE_NAME
43        
44    GoTo ExitHandler
45        
46End Sub
47

AT THE END

THe latter resolution is more flexible and user-friendly, I think.

Translations:

"Japanese" |