Option Explicit
Sub Test1()
Range("A1").Value = "(25 + 5) * 1,5"
MsgBox BerZelle(Range("A1"))
End Sub
Sub Test2()
Dim x As Double, y As Double
Range("A1").Value = "(25 + 5) * x + 2 * y"
For x = 1# To 1.9 Step 0.1
For y = 1.9 To 2.1
MsgBox BerTerm(Range("A1"), Array("x", "y"), Array(x, y))
Next y
Next x
End Sub
Sub Test3()
Dim x As Double, y As Double, z As Double
Range("A1").Value = "2 * x + y"
Range("A2").Value = "x + z"
Range("A3").Value = "2 * z^y"
Range("B1").Value = 1.25
Range("B2").Value = 0.25
Range("B3").Value = 2
Range("D1").Value = 2.25
Range("D2").Value = 1.25
Range("D3").Value = 0.5
Range("E1").Value = 4
Range("E2").Value = 5
Range("E3").Value = 6
For x = 1 To 3
For y = 4 To 5
z = Cells(x, 2).Value
MsgBox BerTerm(Cells(x, 1), Array("x", "y", "z"), _
Array(Cells(x, y).Value, Cells(x, y).Value, z))
Next y
Next x
End Sub
Public Function BerZelle(Zellbezug)
Rem Text einer benannten Zelle als Wert
Dim ZellText As String
ZellText = Zellbezug
ZellText = Replace(ZellText, ",", ".")
BerZelle = Application.Evaluate(ZellText)
End Function
Public Function BerTerm(Zellbezug As Range, VarrText As Variant, VarrVal As Variant)
Rem Text einer benannten Zelle als Wert mit Variablen
Dim ZellText As String
Dim i As Long
ZellText = Zellbezug
If UBound(VarrText) <> UBound(VarrVal) Then Exit Function
For i = LBound(VarrText) To UBound(VarrText)
ZellText = Replace(ZellText, VarrText(i), VarrVal(i))
Next i
ZellText = Replace(ZellText, ",", ".")
BerTerm = Application.Evaluate(ZellText)
End Function