Trying to use "PageSetup.PaperSize" with a constant not working

[kelly mort]

So I read stuffs and learned that I can use a message box to find the constant assigned to the current paper size then used that and pass it to the "<code style="box-sizing: inherit; margin: 0px; padding: 0px; border: 0px; font-variant-numeric: inherit; font-variant-east-asian: inherit; font-stretch: inherit; line-height: inherit; vertical-align: baseline; background-color: rgb(240, 240, 240); max-height: 300px; overflow: auto; color: rgb(12, 13, 14);">PageSetup.PaperSize"</code><code style="box-sizing: inherit; margin: 0px; padding: 0px; border: 0px; font-variant-numeric: inherit; font-variant-east-asian: inherit; font-stretch: inherit; line-height: inherit; vertical-align: baseline; background-color: rgb(240, 240, 240); max-height: 300px; overflow: auto; color: rgb(12, 13, 14);"> property.

So I did,

Sub SetPSize()
    <code style="box-sizing: inherit; margin: 0px; padding: 0px; border: 0px; font-variant-numeric: inherit; font-variant-east-asian: inherit; font-stretch: inherit; line-height: inherit; vertical-align: baseline; max-height: 300px; overflow: auto;">PageSetup.PaperSize = &43</code>
End Sub

That's the constant I got from the post card paper size.

Then the system changes it to "&043".

But when I run it, it fails to do the job.

What is it that I did wrong?

Thanks for reading</code>

 

[shknbk2]

It looks like 43 is the Japanese Postcard size. Though it's not in THIS list. Not sure why.

The value you can set the PaperSize to is a number. The ampersand isn't valid. Also, you have to have an object in front of the PageSetup.

This should work for you:

ActiveSheet.PageSetup.PaperSize = 43

You don't have to use ActiveSheet. Other worksheet objects will work like Worksheets("Sheet1") or Worksheets(1), etc.

On another note, another way you can investigate constants or other values without using a MessageBox is to use the Immediate Window. In the code editor, you can choose View->Immediate Window (or you can use the shortcut Ctrl+G). In this window, you can use the code above with a ? to get the value. For example, typing "?ActiveSheet.PageSetup.PaperSize" (remove the quotes) in the immediate window and hitting Enter will put the value on the next line. You can also assign the PaperSize in this window, too, if you don't use the '?'. You can put "ActiveSheet.PageSetup.PaperSize = 43" in the immediate window and hit Enter, and the page size will change accordingly.

 

[Domenic]

&0 is the prefix for an octal literal. So &43 is automatically converted it to its 3 digit octal literal equivalent, which is &043. For more information regarding literals, have a look at the following link...

https://docs.microsoft.com/en-us/do...racters#hexadecimal-binary-and-octal-literals

Also, the decimal value for this octal is 35, which corresponds to the constant xlPaperEnvelopeB6. For a complete list of available constants, go to your object browser (Visual Basic Editor >> View >> Object Browser), and select Excel from the drop down menu for 'Project/Library', and then type xlPaperSize in the 'Search Text' box.

 

[kelly mort]

Great great!!!

I think I misunderstood what I read.

It's working now.

One question:

Supposed I used a user define dimensions like 3.94" x 5.83" to set up, since you can't locate that from most printers, is the constant still 43?

I am trying to test that but currently my printers are offline so I can't seem to access that part of the selection.

Very grateful

 

[shknbk2]

is the constant still 43?

Not sure. Can't help with this one.

 

[kelly mort]

Okay . will dig deeper and see what I can find .

If I find something, will post it here

 

[kelly mort]

Okay so I was able to get the user define paper size set to 3.94" x 5.83" and produced a constant of 141.

Which got me thinking:

Is this value assigned to that dimension?

Whatever the case may be, can I set the paper size directly to 3.94" x 5.83"? If yes how?

If someone has taken up this journey before or knows something about this please help lost coder out of the jungle before the leopard senses his presence