Can I write code that can get VBE settings?

[kadeo]

I'm trying to write an add-in that will copy code from an Excel VBProject to Word so I can "Pretty Print" it. Can I query some of the VBE settings? I'll probably need to query more settings but specifically right now I want to query what colours are currently set for the code's keywords, identifiers and comments.

[kadeo]

I haven't found anything on either ExcelForum or Google that tells me I can do this. I'm hoping a bump to the top of the forum will mean someone will see this that didn't before and can help me out with it??

[Norie]

Why do you need to know the VBE settings?

Couldn't you use some standard formatting so you end up with standard, pretty output?

[macropod]

I'm trying to write an add-in that will copy code from an Excel VBProject to Word so I can "Pretty Print" it. Can I query some of the VBE settings?

While it's possible to access the VBE, t you'd need to give trusted access to the VBA project model; something that is not recommended unless there's no other way of achieving the same outcome. Do note that, when you export VBA code to Word, all the character formatting you see in the VBE is lost; only indents are retained. Consequently, you'll have a lot of work ahead of you if you want to generate anything with formatting like you see in the VBE. Even if you could access Keyword colours etc., none of those attributes is carried over and you'd have to recreate them from scratch.

[mikerickson]

This will get the code from an Excel module, open Word, create a new document and put that code into the new document.
Word should not be open when you run this.

Please Login or Register  to view this content.

[macropod]

This will get the code from an Excel module, open Word, create a new document and put that code into the new document.
Word should not be open when you run this.

Only if you have a code module named 'Module1' and only for that code module. If there's no 'Module1' (e.g. because all the code is in the 'ThisWorkbook' module, a sheet module or a userform), all you'll get is an error message.

It shouldn't matter whether Word is open, but what does matter is:
• you also need to give trusted access to the VBA project model or the code won't work;
• your code doesn't save the document; and
• your code leaves an orphaned Word session containing the unsaved document running in the background.

[kadeo]

Thanks for the responses guys. I've been offline for a couple of weeks and only seeing these now.

As it happens, I'm good as far as exporting the actual code to Word is concerned. And while I know I have a lot of work ahead in terms of formatting the output, I'm prepared for it. Also, I know the objective will require access to the VBA project model and I'll make this clear to anyone who runs the resulting add-in.

The only solutions to what I'm doing that I can find online at the moment involve installing some other program to do the work but a lot of companies restrict employees from installing programs. A VBA add-in is a niche I hope to fill.

Back to my OP though...

Why do you need to know the VBE settings?

Couldn't you use some standard formatting so you end up with standard, pretty output?

Thanks, norie I could do that, in fact that is one of the options the add-in provides. But in the interests of giving options to the user, one I was hoping to add is to allow the user to just use the colours they have already set up in the VBE without having to reselect them all in my add-in and for that I need to query the existing settings. I'd also need to do this for the font they use, the point size and so on.

I will at some point need to start a thread about identifying a list of all the keywords or reserved words used in VBA but I will save that for a separate thread. I'm kind of getting the impression that there is no direct way to do what I need here. But, like I said, there are third party programs that can do it. What about using some kind of API to query the contents of a window like I need to do if I want my code to enter the password to open a locked VBA project for instance. Could that be a way to get what I need?