In the earlier section “Spell-Checking Documents,” 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. If Excel is installed on the host computer, you can contact it from within your Visual Basic 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 the following one:
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 ExcelVBA Form does exactly that. Its listing is shown next.
The code 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 method to calculate math expressions with Excel ~ to prefix the expression with the equals sign (“=”) and assign the entire expression to a cell. Excel will assign the result of the calculation to the cell, and if you read back the value of the same cell, it will be a number and not the actual expression you supplied.