How to calculate convert string into formula and calculate in vb

wedelia

New Member
Joined
Sep 27, 2007
Messages
12
Hi all,

I'm setting up a spreadsheet at the moment and would like to offer the users to input the equation (for example: "=(Log10((delta / 324.33) ^ (1 / -0.0451)))") that was derived from other sources in Excel.

*delta is a range of values

I'm trying to get vb to get the equation and calculate using the equation for different delta values.

What I have done is:

Static Function XXValue(byVal delta As Single, byVal Type As String)

Dim UserEquation As String
Dim UserDefinedEqn As Single

If Type = "user-defined" Then

UserEquation = Range("UserdefinedEqn").Value
'UserdefinedEqn is the name for cell containing the equation that user has input

CalculatedEqn = Evaluate(UserEquation)

If CalculatedEqn > 2 Then
No = CalculatedEqn
If Calculated Eqn > Range("Limit").Value Then
'Limit the name for cell containing the limit of the calculation
No = Range("Limit").Value
End If

Number = 10 ^ No

End If

XXValue = Number

End Function

Not sure where I have gone wrong in this set of code. It doesn't seem to calculate at all. Could someone please offer some advice?

Thanks!
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

craig.penny

Well-known Member
Joined
May 8, 2009
Messages
656
Try inserting a breakpoint at line

Code:
If Type = "user-defined" Then

and then run your program. When the code stops on that line start stepping through your code with F8. Hover the pointer over the variables as you go through and figure out when the problem starts.

It may not affect anything but using a variable name "Type" probably isn't a great idea. I'd suggest you make a minor change to it like maybe "sType" or something similar.
 

wedelia

New Member
Joined
Sep 27, 2007
Messages
12
I have changed the name from "Type" to "TypeSelected" but it still doesn't work. I have tried to trace the problem using F8 and find that it stopped working at this line

CalculatedEqn = Evaluate(UserEquation)

The error that comes out is Run time error '13': Type mismatch.
 

wedelia

New Member
Joined
Sep 27, 2007
Messages
12

ADVERTISEMENT

CalculatedEqn is a Single
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,901

ADVERTISEMENT

Try this conceptual model

Code:
Sub test()
Dim formulaString as String
Dim rangeOfValues as Variant
Dim singleValue as Variant, oneResult as Double

    formulaString = "=(Log10((delta / 324.33) ^ (1 / -0.0451)))"
    rangeOfValues = Array(0.23, 1.45, 300.4)

    For Each singleValue in rangeOfValues

        oneResult = EvaluateFormualaAt(formualString, "delta", singleValue)

        MsgBox "Formula evaluated at " & singleValue & " is " & oneResult
    Next singleValue
End Sub

Function EvaluateFormulaAt(formulaStr as String, variableName as String, evalValue as Double) As Double

    EvaluateFormulaAt = Evaluate(Replace(formulaStr, variableName, evalValue))

End Function
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,901
Here's an approach that works with more than one variable.
Code:
Sub test()
    Dim formulaString As String
    
    formulaString = "x+2*y+3"
    MsgBox EvaluateFormula(formulaString, "x", 1, "y", 3)
End Sub

Function EvaluateFormula(formulaStr, ParamArray Variables_Values() As Variant) As Variant
    Dim i As Long
    
    For i = 0 To UBound(Variables_Values) Step 2
        formulaStr = Replace(formulaStr, Variables_Values(i), Variables_Values(i + 1))
    Next i
    
    EvaluateFormula = Evaluate(formulaStr)
    If IsError(EvaluateFormula) Then EvaluateFormula = formulaStr
End Function
 

wedelia

New Member
Joined
Sep 27, 2007
Messages
12
For the formula string, is it possible to get vb to pick it up from Excel?

Thanks for your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,387
Messages
5,595,884
Members
414,029
Latest member
mrwilker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top