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!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
For the formula string, is it possible to get vb to pick it up from Excel?

Thanks for your help.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

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
Back
Top