Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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
share|improve this answer
    
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 '11 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! –  Hand-E-Food Sep 19 '11 at 5:01

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

share|improve this answer
    
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 '11 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 '11 at 4:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.