CODEWITHSUNDEEP
excelvbams-office
sascha10000
sascha10000

Reputation: 1230

Excel VBA - Calculate in VBA

simple problem. I have a String representing a Formula.

Like

Dim formula As String
formula = Replace(processFormula(2016, 9000, constants, formulaRow), "##", 9000)
'formula has the value "(1007,27*((9000 - 8820)/10000)+1400)*((9000 - 8820)/10000)"

MsgBox (formula)

'Evaluate(formula) not working sadly

Now I want to evaluate it inside VBA and return the result. I don't get Evaluate to work for the calculation.

processFormula is a function generating a formula and it returns a String. So a String is put into the replace function which replaces '##' by a number. Nothing magical here. Regarding to the Debugger formula is a String with the value stated above (it's copied directly from the debugger).

Is there a simple and easy way?

Thanks, I hope it's not a duplicate.

Upvotes: 0

Views: 261

Answers (3)

h2so4
h2so4

Reputation: 1577

you should replace the decimal comma by a decimal point

Dim formula As String
formula = replace(Replace(processFormula(2016, 9000, constants, formulaRow), "##", 9000),",",".")
'formula has the value "(1007,27*((9000 - 8820)/10000)+1400)*((9000 - 8820)/10000)"

MsgBox (formula)

Evaluate(formula) 'should work now ;o)

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

There should be no problem:

Sub eval()
    MsgBox Evaluate((100 * 20) / 30 * 500)
End Sub

enter image description here

which matches the equivalent cell formula. Note this is not the same as:

   (100*20)/(30*500)

EDIT#1:

This also works:

Sub eval2()
    Dim s As String
    s = "((100 * 20) / 30 * 500)"
    MsgBox Evaluate(s)
End Sub

Upvotes: 1

Harun24hr
Harun24hr

Reputation: 36750

Use following sub.

Sub Calculate()
Dim x As Double
    x = ((100 * 20) / 30 * 500)
    Range("A1") = x
    MsgBox x
End Sub

Upvotes: 0

Related Questions