Recently, I promised a post containing the code for my some of favorite basic document production macros. After lots of deliberation, it became clear that this wasn't going to fit into a single post. So, this first one deals with use of the ever-fabulous immediate window.
This post is going to be about VBA one-liners for getting information from or acting on a document, presentation, workbook, etc. Note that this series of posts is going to be targeted to the new VBA user - so if you're experienced at using the Immediate Window, don't be surprised if you find nothing earth-shattering in this post. But if you aren't yet cozy with this VBA feature, you'll probably find the info here mighty handy...
I do a lot of troubleshooting of evil-bad, problematic Word, Excel, or PowerPoint documents -- so many of my favorite everyday macros -- such as using the Immediate Window to exchange information with the document -- are those I use as a rule when troubleshooting. So, keep in mind that the examples given here are just examples -- you can apply the same syntax and logic to the type of information you might need to get from or add to your file.
Okay, if you haven't used the immediate window before ... then you might be like I was for my first year of using VBA, when I would write a macro to pop up a message box whenever I needed a piece of information from a document. If that sounds familiar -- boy, is there ever an easier way :) The immediate window is fantastic.
In the VB editor for whatever application you're using, open the Immediate window from the View menu or by pressing CTRL+G. Note, however, that the shortcut key isn't a toggle -- so CTRL+G won't shut the Immediate window if it's already open.
The immediate window runs one line of code at a time ... so it's perfect for getting information from a file or for executing a simple action, like naming shapes in PowerPoint or placing hidden bookmarks in Word.
Pressing ENTER in the Immediate window executes the line of code you're in ... and you can press Enter from anywhere in the line. You can also put your insertion point back in a line and press ENTER from it again to get the new answer. You can type multiple single lines of code in the immediate window at the same time, but you can only ever run one at a time from this window -- you can not execute subroutines from here. Note also that whatever you do in the immediate window will be retained there for the current session of the application only... this is not a place to store code.
To get information, start the line of code with a question mark ... to execute an action, no prefix is needed. For example:
?ActiveDocument.Comments.Count
... will return the number of comments in the active Word document when you press ENTER
ActiveWindow.Selection.ShapeRange.Name = "myshape"
... in PowerPoint, will name the selected shape "myshape" so that your code can act on the shape by name
As you probably noticed in the above examples, you can substitute many different lines of codes in Word, Excel, or PowerPoint, to do an awful lot in the immediate window. For the most part, I use it to get information or to name things ... so here are a few general examples:
To count objects, such as ...
?ActiveWorkbook.Charts.Count
... number of charts on their own sheet in the active Excel workbook
To find out the index of something, or the way to identify a constant, such as ...
?Selection.StoryType
... the index of the story type where your insertion point is currently located in Word (1 is the main story, for example, 7 is the primary header story, and 4 is the comments story).
You'll notice when you get the name of a constant in this way, you'll often get an index instead of the name. Both will work. In the case of Word story types, as shown here ... if, instead, you were writing a conditional statement, such as:
If Selection.StoryType = .... Then
...
The autocomplete list you'd get after typing the equal sign would list constant names instead of index numbers (such as wdMainTextStory, wdPrimaryHeaderStory, and wdCommentsStory). In any of this type of instance, either the constant name or its index number will work equally well. Experienced developers often use the index number just because it's less code to write.
To act on things in the file, such as ...
ActiveDocument.Shapes(1).Select
... to select the first floating object in the document -- which you might want to do when troubleshooting a document if you know it contains shapes and you can't find them. For example, say that
?ActiveDocument.Shapes.Count tells me that there is 1 shape in the document, but I don't see any floating objects -- this might happen if the graphic is outside of the page area, or has no fill or line color (which might happen if a document was imported from a source that Word couldn't entirely translate).
In terms of adjusting these examples for your own purposes ... notice that many of these examples work the same for any number of objects
(such as, you can count any VBA object ... so you might need
?ActiveDocument.bookmarks.count, or
?ActivePresentation.designs.count, or
?Range("rangename").Cells.Count, etc.)
... or work similarly when the same object exists in multiple programs
(such as naming a selected floating object in Word or Excel could be written as:
Selection.ShapeRange.Name = "myshape" (similar to the code ActiveWindow.Selection.ShapeRange.Name = "myshape" for PowerPoint that was given earlier in this post).)
To add things to the document that can only be done from the VB editor, such as ...
Selection.Bookmarks.Add("_mybook")
... adding hidden bookmarks can only be done through the VB editor. A hidden bookmark starts with the underscore character and can be really handy when you need to programmatically place content in a document at certain places, but don't want the bookmarks to be easily visible to users.
...or...
Activedocument.Variables.Add "myVar","test"
... to add a document variable to a Word document. Keep in mind that, with things like this, you can change the name and value and then press ENTER again to add an additional variable without retyping the rest of the code ...
Or things that might be quicker to add or modify from the VB editor, particularly if you have lots of them, such as ...
ActiveWorkbook.CustomDocumentProperties.Add "MyProp1",False,msoPropertyTypeString,"test value1"
adding customdocument properties is the same for Word, Excel, and PowerPoint except for the object (document object in Word, workbook object in Excel (shown here), presentation object in PowerPoint). Note that the variables in that code are, in order, Name, LinktoContent, Type, and Value.
********************************************
Okay, I fear this post is getting out of hand. Hopefully, if you're new to the immediate window, this gives you enough of an introduction that you can start playing around with it for yourself and see what kind of info you can exchange with your documents through this quick and simple method.
If I left questions unanswered on this topic - please post a comment. The next installment of favorite everyday macros will revisit the topic of loops that I've posted on before, with some specific everyday document production examples.
Happy Monday everybody!