How to do for this kind of formula?

  • Thread starter Thread starter Guest
  • Start date Start date
Copy this UDF to a genral module.

Function EvalCell(RefCell As String)
Application.Volatile
EvalCell = Evaluate(RefCell)
End Function

=EvalCell(A1) in A2 returns 6

First off.........save a backup of your workbook.

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there.

Save the workbook and hit ALT + Q to return to Excel window.

Enter the formula in a helper cell as explained above.


Gord Dibben MS Excel MVP
 
Suppose Cell: A1 = "1+2+3"
and any method to get its result(6) in Cell A2
Click to expand...

You can use a UDF.

How involved depends on the variety of what might be in A1.

If just quote marks, numbers and operators, then you can use something simple:

<alt-F11> opens the VB Editor.

Ensure your project is highlighted in the project explorer window, then
Insert/Module and paste the code below into the window that opens:

=============================
Function Eval(rg)
Dim str As String
str = Replace(rg.Text, """", "")
Eval = Evaluate(str)
End Function
=============================

A2: =Eval(A1)

However, if there might be characters other than quote marks and the various
operators, then you need to strip them all out:

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

Function Eval(rg)
Dim str As String

'ensure contains only numbers and operators
Dim oRegex As Object
Set oRegex = CreateObject("VBScript.RegExp")

With oRegex
.Global = True
.Pattern = "[^-0-9+/*^\()]"
End With

str = oRegex.Replace(rg.Text, "")
Eval = Evaluate(str)

End Function
=========================

--ron
 
Back
Top