Excel constants — how to reference dynamically

microsoft excelvba

I am developing a complex spreadsheet for a friend; my first in-depth Excel. Working in Excel 2003. The issue I am having is workin with xl constants , eg xlPaper. The issue comes when trying to work with the constant, it is like the value is not able to be worked with when passed the reference "xlWhatever".

Code with issue:

     Dim myRange As String, LM As Integer, RM As Integer, TM As Integer, BM As Integer, Papersizing As String, Orient As String, Quality As Integer
            On Error GoTo Errorcatch
    TM = ws.Range("f2").Value
     LM = ws.Range("f3").Value
     BM = ws.Range("f4").Value
     RM = ws.Range("f5").Value
      Papersizing = "xlPaper" & ws.Range("d2").Value
      Orient = "xl" & ws.Range("d5").Value
      MsgBox Papersizing
     Quality = ws.Range("d9").Value
    With ActiveSheet.PageSetup
    .PrintTitleRows = ""
    .PrintTitleColumns = ""
End With

    With ActiveSheet.PageSetup
    MsgBox .Papersize
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.CentimetersToPoints(LM)
        .RightMargin = Application.CentimetersToPoints(RM)
        .TopMargin = Application.CentimetersToPoints(TM)
        .BottomMargin = Application.CentimetersToPoints(BM)
        .HeaderMargin = Application.CentimetersToPoints(0)
        .FooterMargin = Application.CentimetersToPoints(0)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = Orient
        .Draft = False
        .Papersize = Papersizing
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
    End With

So Papersizing there ends up being "xlPaperA4", Orient = "xlPortrait".
The error Excel spits out is "Unable to set the PaperSize property of the PageSetup class" when dealing with the pagesize, and similar with the orientation.

Yet if I pass hard values like .Papersize = 9 it works. Not particularly dynamic. Forgive me…I'm used to PHP.

So what do I do here? is there some method to re-reference constants based upon the name of the constant?

Best Answer

xlPaperA4 and xlPortrait are constants and cannot be passed as Strings. The properties you are trying to set expect a number. What you're trying to do is like the last line here:

Const xlSomething = 123
Choice = "Something"
Value = xlSomething    ' Value = 123
Value = "xl" & Choice  ' Value = "xlSomething", Error: Not a number!

I'm not sure how powerful VBA is, but you may have to resort to a Select/Case statement.

Select ws.Range("d2").Value
    Case "A4": Papersizing = xlPaperSizeA4
    Case "A3": Papersizing = xlPaperSizeA3
    Case Else
        Papersizing = 0
        MsgBox "Bad paper size."
End Select
Related Question