Excel 2013>VBA>PageSetup>User Defined Formula>Interference

[aworthey]

Hello,

I have code that saves a PDF of my spreadsheet to my desktop with custom print settings. It works perfectly except when I enable the PageSetup settings. When these settings are enabled within the code, my user-defined formula generates error messages within the cell.

What is it about the PageSetup settings that is interfering with my user-defined formula?

Here's the PDF print code:

Sub Button2()


Application.ScreenUpdating = False


Dim strFilename As String
Dim rngRange As Range
Dim konumber As String


konumber = Environ$("UserName")
Set rngRange = ThisWorkbook.Sheets("CostWorksheet").Range("D4")
strFilename = rngRange.Value & Format(Now(), "mmddyyyy hhmm")


ThisWorkbook.Sheets("CostWorksheet").Activate


'With ActiveSheet.PageSetup
    '.Orientation = xlLandscape
    '.Zoom = False
    '.FitToPagesWide = 1
    '.FitToPagesTall = 1
'End With


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Users\" & konumber & "\Desktop\" & strFilename & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True


ActiveCell.Offset(1, 100).Activate
    
Application.ScreenUpdating = True


End Sub

Thanks!

[aworthey]

Here's the fix I found:

Sub Button2()


Application.ScreenUpdating = False


Dim strFilename As String
Dim rngRange As Range
Dim konumber As String


konumber = Environ$("UserName")
Set rngRange = ThisWorkbook.Sheets("CostWorksheet").Range("D4")
strFilename = rngRange.Value & Format(Now(), "mmddyyyy hhmm")


ThisWorkbook.Sheets("CostWorksheet").Activate


With ThisWorkbook.Sheets("CostWorksheet").PageSetup
    .Orientation = xlLandscape
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 1
End With


ThisWorkbook.Sheets("CostWorksheet").EnableCalculation = False
ThisWorkbook.Sheets("CostWorksheet").EnableCalculation = True


ThisWorkbook.Sheets("CostWorksheet").Calculate


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Users\" & konumber & "\Desktop\" & strFilename & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True


ActiveCell.Offset(1000, 1000).Activate
    
Application.ScreenUpdating = True


End Sub

[mdmackillop]

I can't see what would cause an issue. Can you post your workbook?

[aworthey]

Here's 2 user-defined formulas that I have on 2 general Modules for summing and taking an average horizontally on visible cells:

Function Sum_Visible_Cells(Cells_To_Sum As Object)
Dim cell As Object
Dim Total As Double


Application.Volatile


For Each cell In Cells_To_Sum
If (cell.Rows.Hidden = False) And (cell.Columns.Hidden = False) Then
Total = Total + cell.Value
End If


Next
Sum_Visible_Cells = Total


End Function

Function AverageVisible(rng As Object)
    Dim rCell As Object
    Dim iCount As Integer
    Dim dTtl As Double
    
    Application.Volatile
    
    iCount = 0
    dTtl = 0
    
    For Each rCell In rng
    If (rCell.Rows.Hidden = False) And (rCell.Columns.Hidden = False) Then
    dTtl = dTtl + rCell
    iCount = iCount + 1
    End If
    
    Next
    AverageVisible = dTtl / iCount
    
End Function

Somehow the PageSetup settings were preventing the calculations in the sheet using those formulas from functioning.

[aworthey]

But the disable and enable then calculate commands corrected the problem.

[mdmackillop]

You could tidy up a bit for clarity, and I suspect omit the EnableCalculation lines.

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("CostWorksheet")


With ws
    With .PageSetup
        .Orientation = xlLandscape
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
    .EnableCalculation = False
    .EnableCalculation = True
    .Calculate
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Users\" & konumber & "\Desktop\" & strFilename & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
    Application.Goto .Cells(1000, 1000)
End With

[aworthey]

Thanks for those suggestions!