Troy University → Troy Campus → Academics → College of Arts & Sciences → Chemistry → King → Chemistry Formatter

Chemistry Formatter Add-ins for Microsoft Word, Excel, and PowerPoint

Christopher King

Chemistry Department, Troy University, Troy, AL 36082

E-mail:  cking@troy.edu

Home page:  http://christopherking.name

Here are free, open-source add-ins for Microsoft Word, Excel, and PowerPoint that format chemical equations and exponents.  The source code is released under the GPL v. 3 license.  To illustrate their use, imagine typing the following line in Word:

Cu2+(aq) + SO42-(aq)  CuSO4*5H2O       Heat for 2 hours at 120°C.   ΔH = 1E4 J/mol

(I typed -->, which Word automatically converted to ).  Leave the cursor in this paragraph (or select just the text to format).  Click the  button, which runs the add-in.  The line is converted to this:

Cu²⁺⁽aq) + SO₄^2-(aq)  CuSO₄·5H₂O     Heat for 2 hours at 120°C.   ΔH = 1 × 10⁴ J/mol

Superscripts and subscripts were added; the arrow was replaced with a prettier arrow; and the asterisk was converted to a centered dot to indicate waters of hydration (or radicals).  A plus (or minus) sign with space before it is not superscripted.  Numbers in the text after the equation were unchanged, except that 1E4 was converted to 1 × 10⁴.

In Excel, you could type the following into two cells:

*CH3 + *OH --> CH3OH

2H2(g) + O2(g) --> 2H2O(g)   yield: 3.2E8 kg

Select both cells, click the  button, and the lines are converted to these:

·CH₃ + ·OH  CH₃OH

2H₂(g)  2H₂O(g)   yield: 3.2 × 10⁸ kg

(The centered dots are a little small in Arial, which is Excel’s default font on the PC.)

Here are some other examples.  Square brackets may be present.

Radicals can be entered.

A plus or minus sign before a letter is not treated as a charge.

If more than one digit precedes a charge, the last digit goes with the charge.

A single digit preceding a charge goes with the charge, except for O3.

The program cannot read minds, so check the results.  In the last example, CH3- was probably meant to be CH₃^-, rather than CH^3-. 

These add-ins take a couple of seconds to load the first time they are run; after that, they are quite fast.

The location of the button that runs the formatter can be changed by the user:  from the Tools menu, select Customize…, then drag the button to a new location.  When the add-in restarts, it will remember the new location.

Word Add-in

To download the Word add-in, click on the appropriate link below with your secondary mouse button (i.e., right-click) (Mac users:  hold down the control key, , and click on the link) and select Save Target As … (Internet Explorer) or Save Link As … (Firfox) from the menu.

Internet Explorer 7 changes the file extension to .zip in the Save As dialog.  Change the ending back to .dotm and it should work fine.

The Word 2007 add-in seems to work at least somewhat on the Mac with Office 2011.

Note:  The add-in for Word v. X will not run unless the Office v. X service release 1 is installed.

Save the add-in in the Word startup directory. 

Unless you have Office 2007, do the following.  Otherwise, follow the directions under PowerPoint 2007, below.

Once the file is in the correct directory, it will automatically load every time Word is started.  To start the formatter without restarting Word, go to the Templates and Add-Ins… dialog box (on the Tools menu).

If the list of add-ins does not include “Chemistry Formatter”, use the Add… button to locate it.  Put a check mark next to the entry “ Chemical Formatter for Word.dot” (which should now be present in the add-ins list).  Click the OK button to complete the installation.  You can tell that installation succeeded by the appearance of the  button on Word’s standard toolbar.

Some Details

The Chemistry Formatter for Word will process text in shapes, a “normal” selection of text, a “block” selection (made by holding down the alt key before selecting text), and cells in a table.  The shapes and normal selection are usually processed in less than a second.  Processing a block selection takes about 7 times as long, though this is still only about a second for small selections.  A block of cells in a table is also somewhat slower to process if it extends over more than one row, and doesn’t include all the cells in the row; however, it is not as slow as a block selection.  If the AutoCorrect “Replace text as you type” option is turned off, the formatter will convert -->, but not , to .  If it is on, the formatter will convert , but not -->, to .

Word XP and later versions allows the user to select several regions of text at the same time (select the first region, hold down the control key, and select another region with the mouse).  The Chemistry Formatter will only format the last region selected.  The formatter does not process text in diagrams.  Also, the formatter will produce an error message if hidden text at the very end of a document is selected (by using “Select All” from Word’s Edit menu).

Complete “Undo” capabilities are provided.  (Word provides this automatically; I can’t take credit for it.)

The program was created using Visual Basic for Applications.  It is not password protected.  To view the code, unload the add-in, open it from the file / open menu, then go to the Visual Basic Editor.

Typing Isotopes, pK_a, ΔH°, etc. in Word

Chemists use all kinds of symbols.  Word includes “AutoCorrect”, which makes it easy to use combinations of symbols.  If you type, say, “-->",it is automatically changed to .  (Control-z will undo the change.)  You can add other entries to the AutoCorrect list.  Here are some entries I’ve added: 

Here is a Word document that makes it easy to add entries to the AutoCorrect list.  Just select the entries that you want to add from a table and click the button.  17 entries are included; you can add others.  The table makes a convenient storage area for the entries you’ve added so that you can find them when you need them.

ChemSpell

Nghê Quốc Khải has created a nice Word add-in called ChemSpell. You can get it at http://www.esnips.com/web/ChemSpellDEMO.

Excel Add-in

To download the Excel add-in, click on the appropriate link below with your secondary mouse button (i.e., right-click) (Mac users:  hold down the control key, , and click on the link) and select Save Target As … (Internet Explorer) or Save Link As … (Netscape) from the menu.

Internet Explorer 7 changes the file extension to .zip in the Save As dialog.  Change the ending back to .xlam and it should work fine.

Note:  The Excel 97 version may work with Excel v. X on a Mac running OS 10.3.  On OS 10.0, the Excel 97/98/2001 add-in crashes Excel if anything other than superscripting or subscripting is attempted.  I’ve received a report that this is not the case with OS 10.3.  I’d appreciate knowing how it works for you, if you decide to try it.

Save the add-in in the existing Excel AddIns directory.  (If the directory is hidden, make it visible by following the procedure given above for the Word Add-in.)

Note for non-English users:

On computers using a different language, the directory names may be slightly different than shown below.

For Excel 2007 and 2010 for the PC, the default path to this folder follows.  These directories are not visible by default, so the procedure above must be followed to make them visible. The Excel 2007 add-in seems to work at least somewhat on the Mac with Office 2011.

For Windows XP:

C:\Documents and Settings\USERNAME\Application Data\Microsoft\AddIns

For Windows 2007, and Vista:

C:\Users\USERNAME\AppData\Roaming\Microsoft\AddIns

For Excel 2003, XP and 2000 for the PC, the default path to this folder is

for Windows 2000 or Windows XP,

C:\Documents and Settings\USERNAME\Application Data\Microsoft\AddIns

or, if user profiles are used on your computer,

C:\Windows\Profiles\UserName\Application Data\Microsoft\AddIns

or, for older Windows operating systems,

C:\Windows\Application Data\Microsoft\AddIns

For Excel 97 for the PC, the default path is

C:\Program Files\Microsoft Office\Office\Library

For Excel 98 and 2001 for the Mac, the default path is similar to

Macintosh HD:Microsoft Office 98:Office:Excel Add-ins

However, on the Mac you can place the add-in on the desktop, then select the file as described below.  Excel will ask if you want to copy it to the add-ins folder.  Tell it yes, then you can move the file from the desktop to the trash.

Unless you have Office 2007, do the following. Otherwise, follow the directions under PowerPoint 2007, below.

Once the file is in the correct directory, go to the Add-Ins… dialog box (on the Tools menu).

Put a check mark next to the entry “Chemistry Formatter” and you are done.  (If you are using Excel 97, you will have to use the Browse button to find the file; if you are using Excel 98 or 2001 for the Mac, you will have to use the Select… button.)  You can tell that the installation succeeded by the appearance of the  button on Excel’s formatting toolbar.

Some Details

The Chemistry Formatter for Excel will process text in cells, in chart titles and axis labels, in text boxes, and in “AutoShapes”.  The entire cell, label, textbox, or shape must be selected; if only part of the text is selected, the formatter will not run.  To format a comment, shape, or textbox, select the border of the object, not the text.  A shortcut can be used in a chart:  if you select something in the chart besides the title or an axis label, then all titles and axis labels will be formatted. 

Cells containing numbers or formulas are ignored.  Hidden and protected cells are unchanged, as are linked textboxes.  Blank cells are ignored, so you can run the formatter with an entire column or row (or the entire sheet) selected.  The formatter is unable to format textboxes and other “AutoShapes” if they are on charts, so these will have to be formatted manually.

“Undo” is provided for cells.  It is not available for other objects, such as comments and text labels.  A drawback to adding undo capabilities is that it erases the contents of the clipboard.

The arrow,  and multiplication sign, ×, are operating system dependent in Excel (Word doesn’t have this problem).  If a Mac is used to create a spreadsheet containing these characters, the symbols will not make sense to someone viewing the spreadsheet on a PC, and vice versa.  Although both operating systems have some fonts in common, the character sets used by these fonts are different.  If you send a spreadsheet to someone using a different operating system, you may want to first use Edit / Replace… to convert  to --> and x to ×.

The Excel chemistry formatter was created using Visual Basic for Applications.  The code originated from a macro provided by Dr. E. J. Billo, of the chemistry department at Boston College in Chestnut Hill, MA.  His macro is also in his book, Excel for Chemists, 2^nd ed, which came out in March, 2001.

Comments Applying to Both Add-ins

The XP versions of the add-ins will convert 1e6 or 1E6 to exponential notation; the other versions only convert a capital E (searching for a lower case e, the most-used letter in English, significantly slowed down the formatter on older machines).

Why three versions?  The add-ins were developed for Office XP.  Some changes were needed when the add-ins were run on Office 2000, which didn’t support the drawing canvas, or the new worksheet protection model used in Excel.  More changes were needed then they were run on Office 97, which uses an older version of Visual Basic for Applications.  Neither Office 98 nor Office 2001 for the Mac uses the newer version of Visual Basic for Applications.

If you do some programming using Visual Basic for Excel or Word, you may have changed “Error Trapping” (Tools/Options… in the Visual Basic editor) to “Break on All Errors”.  This setting may occasionally cause the add-ins to stop running.  The problem will go away if one of the other error trapping settings is used.

PowerPoint Add-in

To download the PowerPoint add-in, click on the appropriate link below with your secondary mouse button (i.e., right-click) and select Save Target As … (Internet Explorer) or Save Link As … (Netscape) from the menu.

This requires service pack 1 (which you may already have, if you’ve got Microsoft Update turned on).

Internet Explorer 7 changes the file extension to .zip in the Save As dialog. Change the ending back to .ppam and it should work fine.

Internet Explorer 7 changes the file extension to .zip in the Save As dialog. Change the ending back to .ppam and it should work fine.

The PowerPoint 2003 version crashes PowerPoint XP.

Save the add-in in the existing AddIns directory.  The default path to this folder follows.  These directories are not visible by default, so the prodecure above must be followed to make them visible.

In Windows 2000 or Windows XP, the default path to this folder (in English) is

C:\Documents and Settings\USERNAME\Application Data\Microsoft\AddIns

In Windows 7 or Vista, the default path to this folder (in English) is

C:\Users\USERNAME\AppData\Roaming\Microsoft\AddIns

If your computer uses a language besides English, the directory name will probably not be exactly as indicated above.

To install in PowerPoint 2003: Start PowerPoint 2003.  Change the security level from high to medium (on the Tools menu, select Macro, then Security….  On the “Security Level” tab, select Medium.  Go to the Add-Ins… dialog box (on the Tools menu).  Use the Browse button to find the file.  Put a check mark next to the entry “Chemistry Formatter for PowerPoint 2003” and you are done.  You can tell that the installation succeeded by the appearance of a button with “H2O” on it on the formatting toolbar.  If you like, you can reset the security level to high.

To install in PowerPoint 2007 or 2010: Start PowerPoint.  Click the Office Button, then select PowerPoint Options (located at the bottom, not side, of the Office Button box). The PowerPoint Options box appears. On the menus on the left frame, select Add-Ins. At the bottom of the right frame is Manage, followed by a drop-down list. From the list, select PowerPoint Add-ins. Click the Go button. Up pops a dialog box. Use the Add New… button to find the file.  Finally, put a check mark next to the entry "Chemistry Formatter for PowerPoint 2007 (or 2010)” and you are done.  You can tell that the installation succeeded by the appearance of a  button on the Home tab of the ribbon, after the Font group:

Some Details, 2003

The programming interface in PowerPoint is not as complete as it is in Word or Excel, so this add-in isn’t quite as sophisticated.  The button that runs the formatter isn’t as fancy:  it is just the label H2O.  Here’s what it looks like on the formatting toolbar:   If you move it, the new position won’t be remembered when PowerPoint is restarted. 

If “->” is typed, the formatter will convert it into a prettier arrow.  This is different from Word and Excel, where “-->” is what gets changed to an arrow.  In Word, if no text is selected, the paragraph containing the cursor is formatted.  In PowerPoint, the paragraph containing the cursor cannot always be determined.  So, if no text is selected in a title box, the entire contents of the box will be formatted.  In a table or organization chart or note pane (in “Normal” view), if no text is selected, nothing will be formatted, though if the entire table or chart is selected, then the entire table or chart will be formatted.  For other shapes, the current paragraph will be formatted, if no text is selected.  That includes notes in “Notes” view.  If multiple shapes or slides are selected, they will all be formatted.  Comments cannot be formatted.  No part of a chart can be formatted. 

“Undo” is available; Cntl +z, or the undo button or menu will restore the changes made by the add-in.

Other Details, 2007

This version will replace either “->” or “” with a prettier arrow. SmartArt shapes, charts, and comments cannot be formatted. Most selections of part of a paragraph can now be formatted. The PowerPoint 2010 code, for curious VBA programmers, may be downloaded.

Programming Help Needed

The next version of Office for the Mac, “Microsoft Office 2008 for Mac”, will not include Visual Basic. Microsoft recommends writing macros in AppleScript. Also, OpenOffice now supports macros. Both of these could use a chemistry formatter. If you’d like to get the chemistry formatters to work on other platforms, I’d be happy to add a link to your site, or make your product available here. If you’d like, we could work together on the project. Getting the formatters to format text is the easy part. Getting them to handle any situation the user throws at them is the time consuming part. That requires learning the software’s object model. To produce a quality product requires testing all the possibilities, which takes time.

Let me know if you have problems or suggestions, or just like the formatters.  I’m at cking@troy.edu. 

--Chris

Page last updated:  February 9, 2011