CODEWITHSUNDEEP
vbaexcel
canerkorkmaz
canerkorkmaz

Reputation: 39

Calculating strings as a formula in vba

I'm trying to evaluate excel cells which contains data like,

"AAA*2"

and then in vba trying to solve them using,

    dim AAA
    dim equation
    dim result
    AAA=inputbox("?") 'some numeric value
    equation=sheets("sheet1").range("A1").value 'contains "AAA*2"
    result=application.evaluate("=" & equation)

I don't know if something like this possible.If so it's going to make my current project way more simpler.Thanks for any help beforehand.

Upvotes: 0

Views: 1811

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71157

Declare your types, and watch out for implicit assumptions you make with the values involved.

Dim userInput As Variant
userInput = InputBox("?") ' could be numeric, could be a null string pointer, could be anything.

If Not IsNumeric(userInput) Then Exit Sub

Dim AAA As Double
AAA = CDbl(userInput)

Dim source As Worksheet
Set source = ThisWorkbook.Worksheets("Sheet1")

Dim sourceEquation As Variant
sourceEquation = source.Range("A1").Value ' could be an error, an empty variant, a date, whatever

If IsError(sourceEquation) Then Exit Sub

Dim equation As String
'the "AAA" in the string is a string literal, doesn't refer to this local AAA variable.
equation = VBA.Strings.Replace(CStr(sourceEquation), "AAA", AAA)

Dim result As Double
result = Application.Evaluate("=" & equation)

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152450

You cannot mix vba and strings.

Add equation = replace(equation,"AAA", AAA)

dim AAA
dim equation
dim result
AAA=inputbox("?") 'some numeric value
equation=sheets("sheet1").range("A1").value 'contains "AAA*2"
equation = replace(equation,"AAA", AAA)
result=application.evaluate("=" & equation)

Upvotes: 2

Related Questions