1

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?

2 Answers 2

1

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
2
  • Awesome, thanks. I had not come across the ability to do Select Case in VBA yet...but this must be the only answer. Cheers!
    – Onyx
    Sep 19, 2011 at 4:28
  • There are definitely other answers, but would likely take a lot more work to implement. I'm glad it got you going! Sep 19, 2011 at 5:01
2

While you probably could build a workaround (see Dave Peterson's links to Pearson's post in http://www.pcreview.co.uk/forums/convert-string-representation-excel-constant-actual-value-t3148528.html) why not use the direct values in the spreadsheet, or let the use pick Potrait/Landscape etc with Data Validation but then use a formula to return the correct constant values 1/2?

The constant values are easy to obtain by pressing F2 and looking them up in the Library.

paper it

2
  • Thanks for the reply. Workarounds for this are ok where the orientation is concerned. But once you talk about paper sizes...ouch! msdn.microsoft.com/en-us/library/ff839964.aspx - Paper sizes I couldn't find the constants referred to on the Cpearson site though...maybe redesigned?
    – Onyx
    Sep 19, 2011 at 4:23
  • You can find those easily enough in the F2 Library as "Member of Excel.XlPaperSize" [added screenshot above]. I would setup up a data validation table of papersizes and constant values, let users pick the papersize then feed the constant value into the code.
    – brettdj
    Sep 19, 2011 at 4:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .