HOWTO: Evaluate Numeric Expressions Stored as a String

Last reviewed: September 30, 1997
Article ID: Q159974
The information in this article applies to:
  • Standard, Professional, and Enterprise Editions of Microsoft Visual Basic, 16-bit and 32-bit, for Windows, version 4.0

SUMMARY

Given a string expression, the Val function returns the number contained in that string. However, if it encounters a character that is not a number or part of a number, it ignores the rest of the string. For example, Val("2") returns the number 2, but Val("2+2") also returns the number 2, and Val("(2+2)") returns the number 0.

This article describes how to obtain the actual value of a numeric expression that is stored as a String by using the existing features in the Excel Object Model.

MORE INFORMATION

In some instances, you might want to allow users to type in their own numeric expression in a TextBox and then evaluate it. In other cases, it might be necessary to read an ASCII file that contains a series of numeric expressions and then produce another ASCII file containing the results of those expressions. If this is the only purpose of your Visual Basic application, it is probably best, for speed and efficiency, to write a parser. However, for a simple and quick solution, you can use OLE Automation and the Excel Object Library to accomplish these tasks.

Step-by-Step Example

  1. Start a new project in Visual Basic. Form1 is created by default.

  2. Add a TextBox and a CommandButton to Form1.

  3. Set the Text property of the TextBox to "".

  4. Set the Caption property of the CommandButton to "Evaluate."

  5. Add the following code to the Click event of the CommandButton:

          Private Sub Command1_Click()
    
              MsgBox MyVal(Text1.Text)
          End Sub
    
    

  6. Add the following code to the General Declarations section of Form1:

          Private Function MyVal(s As String) As Double
          Dim xl As Object
    

              Set xl = CreateObject("excel.application")
              xl.Workbooks.Add
    
              xl.ActiveCell.Formula = "=" & s
              MyVal = xl.ActiveCell.Value
    
              xl.ActiveWorkbook.Close savechanges:=False
              xl.Quit
              Set xl = Nothing
    
          End Function
    
    

  7. Run the project by pressing the F5 key. Type a valid numeric expression in the TextBox and click the CommandButton to evaluate the expression.
Keywords          : VB4ALL VB4WIN vbwin GnrlVb kbprg
Technology        : kbole kbvba
Version           : WINDOWS:4.0
Platform          : NT WINDOWS
Issue type        : kbhowto


================================================================================


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 30, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.