VBA Named Arguments

An easier read

Use named arguments for cleaner VBA code.

Most likely, you use positional arguments when working with VBA functions. For instance, to create a message box, you probably use a statement that adheres to the following syntax:

MsgBox(prompt[, buttons] [, title] [, helpfile, context])

When you work the MsgBox function this way, the order of the arguments can't be changed.

Therefore, if you want to skip an optional argument that's between two arguments you're defining, you need to include a blank argument, such as:

MsgBox "Hello World!", , "My Message Box"

Named arguments allow you to create more descriptive code and define arguments in any order you wish. To use named arguments, simply type the argument name, followed by :=, and then the argument value.

For instance, the previous statement can be rewritten as:

MsgBox Title:="My Message Box", _
Prompt:="Hello World!"

(To find out a function's named arguments, select the function in your code and press [F1].)


See all Topics word

Merge to More Than One Document

Custom content

In the Data Source, include a field for the type of letter the recipient requires.

In the Main merge document, enter IF fields, such as:

{IF {MERGEFIELD "LetterType"=1} {INCLUDETEXT "C:\\Project\\Letter1" \* MERGEFORMAT} ""}
{IF {MERGEFIELD "LetterType"=2} {INCLUDETEXT "C:\\Project\\Letter2" \* MERGEFORMAT} ""}

• The curly brackets { } cannot be entered from the key board. Either use Insert>Field, or Ctrl+F9.
  
• Word uses spaces in the If..Then..Else statement.
  
• The last two quote marks "" are "empty" , so nothing will be entered.
  
• Notice the \\ in the path statement. A path is not needed if the Main document is in the same folder as the letters.
  
• To see the field codes, use Alt+F9 to toggle the view on and off.

Letters 1 and 2 can have completely different texts, formats and layouts. One can be an invitation to a sale, the other can be a dunning letter. (To carry over different formatting, leave out the \* MERGEFORMAT switch)

After setting up the main document for mail merging, insert all of the fields you want to merge.

Copy the individual fields and paste them in the correct locations in Letter 1 and 2.

Go back to the main document and erase all of the text and fields EXCEPT for the IF statements.

Letters 1 and 2 do not have to be set up a merge docs, or connected to a data source. Their text will be inserted in the Main document depending on the field type.


See all Topics word

Office VBA Tips

Video + Free code

"Learn tips and use sample code for several Office applications. These tips can help you to be more productive and can also be a starting point for developing your own tools, utilities and techniques."

• Update Word Document Statistics in the Title Bar
  
• Create Outlook Rules Programmatically
  
• Delete Repeated Text Throughout a Word Document
  
• Run Macros Based on the Value of One or More Excel Spreadsheet Cells
  
• Disable Related Controls on a PowerPoint Slide After a User Clicks an Input Control
  
• Display Reminder Information When a User Opens an Office Document
  
• Synchronize an Access Main Form to a Subform and Vice Versa
  
• Log Worksheet Changes to an XML File
  
• Merge Body Text from Multiple Outlook E-mail Messages to a Word Document
  
• Use the Office Assistant as an Alternative to Displaying and Retrieving User Input

Ten Tips for Office VBA Developers

VBA Tips & Tricks

Getting Started with VBA in Office 2010

Download Office 2013 VBA Documentation


(VBA is VBA and is, in most cases, usable in all versions of Office)


See all Topics word

Indent Code

Realign a bunch

Indenting blocks of VBA code, such as statements within loops or If...Then statements, makes reading a procedure much easier.

You probably indent a code statement using the [Tab] key, and outdent by using [Shift][Tab].

However, you may not be aware that the [Tab] and [Shift][Tab] techniques also work when multiple code lines are selected.

The Visual Basic Editor also provides Indent and Outdent buttons on the Edit toolbar that allow you to easily reposition blocks of code.


See all Topics word

Merge to PDF

Not just MailMerge anymore

Sure, you can create a merged letter and have 30 pages of individualized information.
What now? How do you create separately named documents to, maybe, send as attachments?
Another task might be to convert those 30 docs to PDF. Oh, the hours wasted!

Graham Mayor, a retired newsgroup junkie, offers a solution.
It's a macro that will convert the merged output as individually named doc(x), and/or PDF files.
I've used it, and now wonder what I can do with the time I'm saving.



GMayor.com


See all Topics word

Change Code to Comments

Fast solution

When you're testing procedures, you can temporarily convert a block of VBA code to comments that will be ignored during a trial run.

Doing so manually by inserting an apostrophe before each line of code can be a real chore.

To simplify this task,

1. Open any module in the Visual Basic Editor (VBE)
   
2. Choose View >Toolbars>Edit from the menu bar to display the Edit toolbar.
   
3. Select the lines of code that you want to turn into comments.
   
4. Click the Comment Block button on the Edit toolbar (it's the sixth button in from the right end of the toolbar).

Each line of the selected code is now preceded with an apostrophe. To convert the comments back to executable code, select the appropriate lines and click the Uncomment Block button, which is immediately to the right of the Comment Block button.


See all Topics word

VBA Help

From Microsoft

Here is a Help reference covering the vagaries of VBA.


Office VBA Language Reference


See all Topics word

List All Files

All files in a folder

Here is a macro that will produce a list of all the files in a selected folder.

• The folder name for the listed files
  
• The file names of the files found
  
• The file sizes of the files found
  
• The dates and times of the files found
  
• The total number of files listed

Macro to List All Files in a Folder


See all Topics word

Sequentially Number Invoices

Creating sequentially numbered documents

Use an Autonew macro to add a sequential number to a document and save it with that number.

In the template from which you create the document, insert a bookmark named Order in the location where you want the sequential number to appear and create an AutoNew macro, as follows:

Sub AutoNew()
Order = System.PrivateProfileString("C:\Settings.Txt", "MacroSettings", "Order")
If Order = "" Then
Order = 1
Else
Order = Order + 1
End If
System.PrivateProfileString("C:\Settings.txt", "MacroSettings", "Order") = Order
ActiveDocument.Bookmarks("Order").Range.InsertBefore Format(Order, "00#")
ActiveDocument.SaveAs FileName:="path" & Format(Order, "00#")
End Sub 

If you do not need to display the number in the document, but just want to save it with a sequential number, there is no need to create the bookmark in the template and you should then delete the second last line of the code.

Article contributed by Doug Robbins
Word MVP Site


Microsoft Knowledgebase:
Macro to Increment Invoice Number to New Form Document


See all Topics word

All Fonts

List maker

Here is a macro that will produce a list of all of the installed fonts.

1. Open Word.
   
2. Use Alt+F11 to open the Visual Basic editor.
   
3. Choose Insert>Module from the Menu.
   
4. Copy and Paste this code in the module.
   
5. Return to Word and go to: Tools>Macro>Macros.
6. Select and run "InstalledFonts".

Sub InstalledFonts()

Dim F As Integer
Dim InstalledFonts As Table

 'Open a fresh document

Set FreshDoc = Documents.Add

'Create a table and define the header

Set InstalledFonts = FreshDoc.Tables.Add(Selection.Range, FontNames.Count + 1, 2)
With InstalledFonts
.Borders.Enable = False
.Cell(1, 1).Range.Font.Name = "Arial"
.Cell(1, 1).Range.Font.Bold = 1
.Cell(1, 1).Range.InsertAfter "Font Name"
.Cell(1, 2).Range.Font.Bold = 1
.Cell(1, 2).Range.InsertAfter "Example"
End With

'Loop through all the fonts and add them to the table

For F = 1 To FontNames.Count
With InstalledFonts
.Cell(F + 1, 1).Range.Font.Name = "Arial"
.Cell(F + 1, 1).Range.Font.Size = 10
.Cell(F + 1, 1).Range.InsertAfter FontNames(F)
.Cell(F + 1, 2).Range.Font.Name = FontNames(F)
.Cell(F + 1, 2).Range.Font.Size = 10
.Cell(F + 1, 2).Range.InsertAfter "ABCDEFG abcdefg 1234567890 &$@"
End With
Next F
'Sort the names of the fonts

InstalledFonts.Sort SortOrder:=wdSortOrderAscending

End Sub

Also see a more sophisticated macro using Excel from Erlandsen Data Consulting: Display all installed fonts (Excel)


See all Topics word

Word Ranges

Pre-defined locations

When entries are made in a document, Word creates a Story Range to identify what part of the document is being used. These ranges can be used in macros to search for items , change text, or other actions.

This macro, for instance, changes the text in just the header of the first section:

Sub HeaderFooterObject()
Dim MyText As String
MyHeaderText = "This would be your text"
With ActiveDocument.Sections(1)
.Headers(wdHeaderFooterPrimary).Range.Text = MyHeaderText
End With
End Sub

When you use Edit>Replace in Word, it does a fine job of locating all occurrences of the target in the body of the document or in the header or footer.

Something fails, however, when you record the action and try to run it as a macro. To make it work, you must loop through the built in ranges of a Word document.

The exercise is interesting if only for the exposure to the built in ranges such as:

• wdCommentsStory
  
• wdEndnotesStory
  
• wdEvenPagesFooterStory
  
• wdEvenPagesHeaderStory
  
• wdFirstPageFooterStory
  
• wdFirstPageHeaderStory
  
• wdFootnotesStory
  
• wdMainTextStory
  
• wdPrimaryFooterStory
  
• wdPrimaryHeaderStory
  
  and
  
• wdTextFrameStory.

See this article for more information: Word.MVPS.org:
Find and replace with VBA
 

Also: Microsoft KB
VBA macro examples to insert text into a document


See all Topics word