Writing Chemical Equations in Excel: The Easy Way (no ions)

I find it very tedious to write a chemical formulas and equations because of the subscripts, so I decided to create a VBA code in Excel which handles just that. It worked fine, until I realized that it won’t have any benefit since I’ve done it in Excel, and lesson plans and the likes are almost always done in Word.

Well, recently, I showed this to my colleague and he suggested pasting the transformed equation from Excel to Word with the hope that the right format will be preserved. Guess what, it worked. That’s why I’m sharing it now.

My code however, won’t work with equations that involve ions, and fractional coefficients but I’m already working on it. Hopefully, I can post another set of code in the future which could be used for both ionic and non-ionic chemical formula.

Here is an example of a chemical equation that can be formatted automatically. Just take note that the spaces between the compounds, + and -> are required to be formatted properly.

CH4 + 2O2 -> CO2 + 2H2O

Here are the steps (MS Excel 2003).

  1. Open a blank worksheet.
  2. If the Visual Basic Toolbar is not visible do the following: View ->Toolbars->Visual Basic
  3. On the Visual Basic Toolbar, click on Visual Basic Editor
  4. On the Menubar of Visual Basic, click Insert ->Module
  5. Copy the code below and paste inside the module.
  6. Sub write_chem()

    For Each rng In Selection
    mystring = rng.Value
    Length = Len(mystring)
    For i = 1 To Length
    char = Mid(mystring, i, 1)
    If i <> 1 Then
    charfound = found(Mid(mystring, i – 1, 1))
    charfound = True
    End If

    On Error Resume Next
    x = Int(char)
    If Err <> 13 And charfound = False Then
    rng.Characters(i, 1).Font.Subscript = True
    rng.Font.Subscript = False
    End If
    Next i
    Next rng

    End Sub

    Function found(char As String) As Boolean
    Dim mylist As Variant

    mylist = Array(" + " , " . " , " – ", Space(1))

    For i = 0 To 4
    If mylist(i) = char Then
    found = True
    Exit For
    found = False
    End If
    Next i
    End Function

  7. Close the Visual Basic Editor
  8. On the Visual Basic Toolbar, click Control Toolbox -> Command button
  9. Draw a command button on the worksheet
  10. Double-click the command button to open again the Visual Basic Editor.
  11. Within the Private Sub and End Sub, type write_chem. Your code should look something like the one below
  12. Private Sub CommandButton1_Click()
    End Sub

  13. Close the Visual Basic Editor
  14. On the Visual Basic Toolbar, click Exit Design Mode
  15. Type  Chemical Equations or formula in Excel.
  16. Select the cell/s and click the command button to format it correctly. Try H2SO4 and 2CCl4 as examples
  17. For the 2CCl4, notice that the number 4 became a subscript while the number 2 was preserved.
  18. You can now paste the formatted equations or formula in MS Word.
  19. When you are using this code, write all the chemical formulas/equations first in Excel then format everything before you paste them to Word.

I’ll be working on the code for the superscripts next time. For now, please enjoy.

By the way and as always, please feel free to comment. And if you need the worksheet please give me your email address so I can send it to you. Cheers!!!


~ by experibiz on September 15, 2008.

5 Responses to “Writing Chemical Equations in Excel: The Easy Way (no ions)”

  1. Thank you so much. I never thought that it is possible to write equations in Excel. I am a Chemistry teacher and I have proven this to be very helpful. You are a Genius! ExperiBiz Guru.


  3. Thank you very much, I’ll try to write more codes in vba and hope I can reach more people. Thanks again.

  4. it is not working, when I click on com button nothing appears on excel highlghted sheet

    • Please check if you enabled the vba macros.

      Here are the steps:
      Save your file first and close. Open the file again and you will be prompted if you want to enable the macros (Office 2003), enable it.

      For Office 2007, a Security Warning will appear. Click Options and Enable the content.

      I hope this solves it.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: