VBA Finding ‘Constant’ name if number & Enumerated data type
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: VBA Finding ‘Constant’ name if number & Enumerated data type

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    The intrinsic constant data types, such as xlCVError or xlPaperSize are like collections in concept I think, however I do not seem to be able to use collection syntax, such as xlPaperSize.Count or For Each lngConst In xlPaperSize......

    The problem I am trying to solve is...
    Let PSize = WorkSheets(1).PageSetup.PaperSize
    'PSize as returned is a Double or Long integer number, this functions OK
    'Now I want to find the text string of the intrinsic constant that evaluates to this number, PSize
    'ie is the paper size "xlA4", "xlLetter", "xlUser" or any other of the 50 or so paper size constants??

    Suggestions much appreciated

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    I might be missing your request here, but do this.

    Go into your object browser,
    search fo papersize

    All of the constants will be listed to the right.

    As you click on any constant,
    the number will be displayed on the bottom
    left above the file reference



    [ This Message was edited by: TsTom on 2002-03-22 02:21 ]

  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Bruce

    Firstly the XlPaperSize is a Property and not an Object as so is not part of any Collection, or a Collection itself.

    The "For Each" loop can only be used on a collection of Objects with a single Object variable as the Object or an array.

    But to answer you question the best thing maybe to simply copy the Constants from the "PaperSize Property" in the VBE help to a Worksheet, place a number next to each on the right in the order they appear. Then simply use either the Vlookup WOrksheetFunction to ascociate the Integer returned and/or use a simple loop to go through each cell.

  4. #4
    New Member
    Join Date
    Feb 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    TsTom, Dave
    Thanks, I was on to those, just hoping there was a more direct way of returning the text names at runtime

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com