Finding LineStyle options

[lastnn30]

Hi
Range-->Borders-->LineStyle is a property and it takes several options (numbers) like XLdash, XLdotdash etc

My question, using object browser, how can I know what LineStyle options are?

I know there is Enum under "Classes" list called XLLineStyle and if I open that I will see all the options for lineStyle but my question

How can I know there is XlineStyle? In the object browser, when I clicked on Range-->Borders-->LineStyle, I only read this "Property LineStyle As Variant"

See attached screen shot. How am I supposed to know LineStyle options are in Enum called XLLineStyle. What is the rule or the logic I should use to search for LineStyle options once I see it defined as property, not only LineStyle but any other properties. For example Range-->Borders-->Weight ? Thank you very much.

[Bernie Deitrick]

Look for it in Class rather than member:

Capture.JPG

[lastnn30]

Thank you. I found it but my question how do I know there is a Enum called XLLineStyle and that Enum has all the value for the LineStyle property of object Borders?
The same thing apply on XLborderWeight. How do I know there is such one?

[Bernie Deitrick]

That's just how object oriented languages work (which is what VBA is) - if there is a property that exists, it will have assigned values that are allowed, even if they are just True and False. Just use F2 to open the object browser and search for the class of interest. If you are searching for a value that gets assigned to a property, it will be show as a member of that class.

[lastnn30]

Thanks. if you see my screenshot above, Borders has a property called LineStyle. Now when I click on LineStyle, I will read at the bottom of the windows that LineStyle is a property of type Variant. So far so good. Now for a new vba user like me. I can not go further than that. Object Browser stop right there. How can I know (using object browser) that I can assign any member of XLLineStyle Enum to be a value of the LineStyle. Let me say it in another word, how can I find the link between LineStyle and XLLineStyle? I found it by google etc but how about other properties in different objects etc. That is my question. I was hoping if I click on LineStyle, somehow a text would be appear and say: "you need to go to XLLineStyle and to find which value you can assign" That is my question actually. Thank you very much.

[Bernie Deitrick]

When you search for a term - in the VBE, press F2 - look for a class that includes the word that you are searching on that that starts with Xl. For LineStyle, there is one, called XlLineStyle - that is the naming convention used by Excel. So if you want to see all the class that have members just search on Xl - there are a lot of them, but it is instructive to see. VBA was not developed for hand-holding new programmers, and it is a hard thing to learn - I learn new things all the time, and I started VBA with Excel 95 - so over 25 years ago.

[lastnn30]

Thank you for your help. I updated the post and I attached a screen shot of the Object Browser. Please see above. Thanks once again

[rorya]

To your question, there is no direct way for you to know that (and Excel doesn't expect you to know anything). You have to either do what Bernie suggested, or perhaps record a macro assigning a specific linestyle or weight and then search for those items in the OB. The issue arises in situations like this because a range can be multiple cells and therefore have different borders applied to the cells within it. That means that the property has to be able to return Null and to do that it needs to return a Variant type rather than the Enum normally associated with it.

[Bernie Deitrick]

When you run into that last section "LineStyle As Variant" just do another, more specific search for that - you start with Borders and end up at LineStyle, then progress to LineStyle and find XlLineStyle.... It's all a process.

[lastnn30]

Thank you all. yes that is what I want to do actually

"then progress to LineStyle and find XlLineStyle"

How can I progress when I do not see any hint in the Object Browser telling me I need to assign one value from the Enum XLLineStyle?

Same thing with

Range("a1").VerticalAlignment = xlTop

How can I know that VerticalAlignment takes one value of XLTopBottom Enum? Object Browser does not suggest I need to go to XLToptBottom to find out these values. That is my question. Does Microsoft really want people to guess and trial and error to find the right values? Thank you again.

[rorya]

Search for xlTop and you should find that it is part of an Enumeration...

[Bernie Deitrick]

Welcome to the Excel Object Model. It's a very long learning process made simpler by recording macros and editing them to contain the core element that you want - in this case, the vertical alignment set to xlTop - and removing the stuff that you don't need. Go from this:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

OR, more generally, to one of these:

Please Login or Register  to view this content.

[lastnn30]

Search for xlTop and you should find that it is part of an Enumeration...

That is my problem or my question.. How do I know I need to search for XLTop?

[rorya]

It’s in the code line you posted.