Calling and Excel Constant by A Variable

[diakonos1984]

Very simple question:
Given a userform with ComboBoxHorizontalAlign populated with the values "Left", "Right", and "Center", and a current Value of "Left"

Please Login or Register  to view this content.

What am I missing?

[shg]

Please Login or Register  to view this content.

[diakonos1984]

Sigh..... I know I can do that..... is that really the only way? I have to trade out one clean line of code for a lengthy Case statement?

I appreciate the help, shg.

[diakonos1984]

It gets worse in another place that I hadn't mentioned before: Paper Sizes.
There are Excel constants for approximately 40 different paper sizes. I have a function that queries my printer and checks to see what page sizes are available. It would sure be nice to dump an item selected from that list straight to the .PaperSize item, instead of having to write a Select with 40 Cases.

I know... I'm whining... and hoping someone has a better way.

[shg]

In VBA, name binding of constants and simple variables to the identifiers that reference them occurs at compile time; i.e., they are statically bound.

You could put them in a collection, using left/right/center/whatever as the key and the enumeration value as the item:

Please Login or Register  to view this content.

[Richard Buttrey]

....There are Excel constants......

I think with that statement you've probably answered your own question. A constant is not a string of text that can be built up by combining other strings, it's an integral and indivisible reference to an object's property.

[Leith Ross]

Hello diakonos1984,

Maybe this option would be more appealing...

Please Login or Register  to view this content.

[diakonos1984]

Thank you for those helpful suggestions.
I may use Leith's concise Switch for the alignments and shg's Collection item for the paper sizes.

[romperstomper]

The paper size constants range from 1 to 41 (excluding xlPaperUser which is 256) so if you load the combobox in the correct order, you could simply add 1 to the ListIndex to get the correct xlPaperSize value.