462 Chapter 10 AUTOMATING MICROSOFT OFFICE APPLICATIONS

If you sort them by rows, which is the default sort order, the columns will be swapped as follows:

1st Quarter	3rd Quarter	4th Quarter	2nd Quarter	Year Total
123.45	376.25	425.75	435.56	1361.01

Using Excel as a Math Parser

Earlier in this chapter, you learned how to borrow the spell-checking capabilities of Word. Now, we’ll do something similar with Excel. Excel is a great tool for doing math. At the same time, Visual Basic doesn’t provide a function or method for calculating math expressions supplied by the user at runtime. If Excel is installed on the host computer, you can contact it from within your VB application and use it to evaluate complicated math expressions.

The simplest method to calculate a math expression is to call the Evaluate method of the Excel.Application object. Assuming you’ve initialized the ExcelApp object variable, you can calculate a math expression like

1/cos(0.335)*cos(12.45)

by calling the ExcelApp object’s Evaluate method and passing the expression as a string argument:

y = ExcelApp.Evaluate(“1/cos(0.335)*cos(12.45)”)

The Calculate Expression button on the ExcelDemo project’s form does exactly that with the statements shown in Listing 10.12.

Listing 10.12: Calculating an Excel Expression

Private Sub Button3_Click(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles Button3.Click Dim mathStr As String

mathStr = InputBox(“Enter math expression to evaluate”, , _ “cos(3.673/4)/exp(-3.333)”)

If mathStr <> “” Then Try

MsgBox(EXL.Evaluate(mathStr).ToString) Catch exc As Exception

MsgBox(exc.Message) End Try

End If End Sub

The code in Listing 10.12 prompts the user to enter any math expression at runtime. Calculating arbitrary math expressions supplied at runtime with straight Visual Basic code is quite difficult.

Another technique to calculate math expressions with Excel is to prefix the expression with the equal sign (=) and assign the entire expression to a cell. Excel will assign the result of the calculation

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com