Pretty printing VBA code

[tfurnivall]

For some time I have wanted to create a utility (CodePrint) that would print the code in a given module (or modules) in a useful format (with index, line numbers, etc). So as part of defining the requirements and user interface, I have a requirement to be able to open any Office document (SourceDoc), and offer the code modules that are in it,from which I can select one or many, for printing.

The selected modules are then copied to a separate Word document (TargetDoc) where the formatting is carried out. This leads to a potential problem if the selected SourceDoc is in fact a previous TargetDoc! Obviously in this case there will be no code modules associated with TargetDoc, but I want to find a way of learning that this is a TargetDoc. Hence an earlier post about CustomProperties. The idea is to create for the SourceDoc a set of CustomProperties that would include the Version of CodePrint, the date of the CodePrint run, and the name of the TargetFile created. The Target file would have a similar set of properties, plus an indication that this is an output file, and so should not be used as a source file.

OK - this research is under way - and already it seems that the various Office Apps have different ways of dealing with Document Properties. Well - consistency is perhaps not Microsoft's strongest point. The second area where I'm running into problems is getting access to the VBE. Before I go too far down this path (which I have trodden once before, but some time ago), I would be interested in learning if the VBE is common and consistent across all the Office apps, or whether this is yet another area where different apps have different approaches.

Of course - someone may already have done this (if so, a pointer would be helpful), which would make this exercise of limited interest except as a research project.

So, bottom line, I have two questions:

1) Is there any good source for learning how the different Office apps can handle a common way of dealing with Custom Properties, and if not, what is the best way of adding these few properties to an Office file?
2) Are there any known inconsistencies between the VBE for different Office apps? I'm assuming (dangerous..) that the structure of the VBE is pretty consistent over different versions, but how about between applications?

Any pointers will be gratefully welcomed!

Tony

[macropod]

The following code shows how you might identify and open a target document, then select and process a folder full of source documents without the risk of re-opening the target document.

Please Login or Register  to view this content.

[tfurnivall]

Thanks, Paul, for the quick response. Here is the procedure I'm using to take a filename, and (try to) get access to the VBE.
Most of the variables are defined globally to the form module (which is used for getting the source file name, and displaying the list of code modules that are available for any given SourceFile.
I'm doing initial work using Excel, because that's the environment with which I'm most familiar. (The actual CodePrint work is, naturally, in Word).

Please Login or Register  to view this content.

The line that causes problems at the moment is:
Set SourceVBE = SourceApp.ActiveWorkbook.vbe

The message is that "Object doesn't support this property or method".

Questions:
1) Is the VBE under the application, or under the open document? (I may have answered this, because I seem to recall that when I have two excel workbooks open at the same time, that their modules, etc get scrambled up in one interface. Leading me to the supposition that the VBE resides in the Application, not the WOrkbook. Good to have this validated somehow!)

2) A slightly different approach gave an error 1004, along with the cryptic message Access to VBE is not trusted. Any idea what this means?

Thanks for your patience,
Tony

[macropod]

To extract the code modules, instead of:
Set SourceVBE = SourceApp.ActiveWorkbook.vbe
you might use something like:

Please Login or Register  to view this content.

where you pass both the file to be processed and its full name to the sub.

As for your questions, I wonder if you're really up to a project of this nature if you have to ask them or questions like those in your previous post. The VBE is an application-level environment and nothing is any more 'scrambled' than your workbooks might be. Just as you can have multiple workbooks open, each with multiple worksheets, so to can the VBE have multiple projects open. It's up to you to pay attention to which one you're working in. As for the 'cryptic message' that's because programmatic access to code modules requires trusted access to the VBA project object model. See comments in the code.