Excel - Convert String to a formula

问题:

I have a formula that is generated via a series of concatenated strings. This is better explained with an example:

open a new Excel workbook

 A1 = 5

 C1 = 5 & "+A1" [A1 is seen as text. C1 will contain "5+A1" ]

I would like E1 to contain the result of C1 evaluated as a formula, so basically in this example 10.

I tried to use INDIRECT(C1) but it did not work.

UPDATE: It must work with Custom cell names. so if for example i assign the name "mycell" to A1, then the field C1 will contain "5+mycell"

UPDATE 2: my real data:

sheet 1, cell Y20 contains this:

-0,0000014369849267*Drehzahl^2 + 0,0401449351560780*Drehzahl - 32,5068828005099000

sheet 2, cell J7 contains this:

= 'Sheet 1'!Y20

sheet 2, cell J8 contains this:

= eval(J7)

sheet 2, cell C7 is renamed as Drezahl

Update 3: a formula that is not working for me:

it requires 4 custom renamed cells: "Drezahl" (value=3550) Kondtemp (45) Saugtemp (-45) Schlitten(100) the result of the calc must be about 91,17

((((-0,0000014369849267*Drehzahl^2 + 0,0401449351560780*Drehzahl - 32,5068828005099000) + ((8,95609756097562+(18,1/2050)*Drehzahl))*(1/25)*(45-Kondtemp))*((0,0000262088992942*Saugtemp^3 + 0,0050210402289170*Saugtemp^2 + 0,3711985619535020*Saugtemp + 9,9227907759641600)*((0,361075074412964)*(3550/Drehzahl)-0,361075074412964+1))*((((100-50)/(91,4-46,3))*((0,014285714285714*Schlitten^2 - 1,262285714285720*Schlitten + 74,214285714286400)-46,3)+50)-50))/50)+((0,0000063790283394*Drehzahl^2 - 0,0103039106823734*Drehzahl + 2,6771661029208000)*(-0,0002159827213823*Kondtemp^2 - 0,0034865782165998*Kondtemp + 1,5954952175254600))*(((0,000000003885345*Saugtemp^6 + 0,000000666998414*Saugtemp^5 + 0,000042907134551*Saugtemp^4 + 0,001268740583850*Saugtemp^3 + 0,020179866978636*Saugtemp^2 + 0,418860651690801*Saugtemp + 9,465861006018070)*((((7,68293017999336)/(-2050)*(Drehzahl-3550)+1)-1)/(45)*(Saugtemp+45)+1)))*(1/-50*((((100-50)/(91,4-46,3))*((0,014285714285714*Schlitten^2 - 1,262285714285720*Schlitten + 74,214285714286400)-46,3)+50)-100))`

回答1:

You can add very simple user defined function to your workbook:

Function eval(str As String)
    Application.Volatile
    eval = Evaluate(Evaluate(Replace(str, ",", ".")))
End Function

and call it in E1 like this: =eval(C1)

Notes:

1) I've used double Evaluate for the case when data in C1 stores like text without = sign : 5 & "A1". If your data stored with = sign: =5 & "A1", you can use eval = Evaluate(str) or leave eval = Evaluate(Evaluate(str)) - it doesen't metter

2) since VBA needs . as delimeter , I've used Replace function

NEW UPDATE:

But, Evaluate can operate only with strings less or equal 255 characters.. In that case you can use following UDF:

Public app As Excel.Application
Public wb As Workbook

Function getValue(formulaString As String)
    Application.Volatile
    Application.DisplayAlerts = False

    'set default falue to #REF..if we'd get normal value - we'll change getValue to it'
    getValue = CVErr(xlErrRef)

    getValue = Evaluate(Evaluate(Replace(formulaString, ",", ".")))
    If Not IsError(getValue) Then
    Exit Function
    End If

    'if we appear here - second WB is closed...'
    On Error GoTo ErrHandler

    If app Is Nothing Then Set app = New Excel.Application
    If wb Is Nothing Then Set wb = app.Workbooks.Open(ThisWorkbook.FullName)
    With wb.Sheets(ThisWorkbook.ActiveSheet.Name)
        With .Range(Application.ThisCell.Address)
            .FormulaLocal = IIf(Left(formulaString, 1) <> "=", "=" & formulaString, formulaString)
            .Calculate
            getValue = .Value
        End With
    End With

ErrHandler:
    Application.DisplayAlerts = True

End Function

and last thing: add following code to the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   If Not wb Is Nothing Then wb.Close False
   If Not app Is Nothing Then app.Quit
   Set app = Nothing
   Set wb = Nothing
End Sub

回答2:

I have almost the exact issue, except mine aren't named cells and its less than 255 chars. going to try the 

Function eval(str As String)
    Application.Volatile
    eval = Evaluate(Evaluate(Replace(str, ",", ".")))
End Function