How to calculate convert string into formula and calculate in vb

[wedelia]

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!

 

[mikerickson]

What do you mean "pick it up from Excel"?

If the formula string is in a cell, that is very possible.

 

[wedelia]

Yeap picking up the formula string from a cell.

I have tried to pick up the formula string from a cell with variables (x) in the formula. I have attempted to define x in the function but it was not recognised when i did evaluate (formula).

 

[mikerickson]

Select A1.
Type "x+2" (no quotes) and press Enter
Run this Macro

Sub Test()
    Dim formulaString as String
    Dim uiValue as Double

    formulaString = Range("A1").Value

    Do
        uiValue = Application.InputBox("Enter the value to be evaluated", type:=1)
        If uiValue = 0 Then Exit Sub: Rem cancel pressed

        MsgBox "The value of " & formulaString & " when x = " & uiValue _
            & vbCr & "= " & EvaluateFormula(formulaString, "x", uiValue)
    Loop Until False
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]

ok thanks ... i will have a go now.. *fingers crossed

 

[wedelia]

Hi Mike,

I had followed your instructions and enter x+2 in cell A1. The macro does not pick up the formula string. I did a msgbox to see what it has picked up from formulaString = Range("A1").Value . The msgbox gives me duplicate - script file name instead of x+2.

 

[mikerickson]

What does

MsgBox Range("A1").Value

return?

 

[wedelia]

It returns "Duplicate - Script file name"

 

[mikerickson]

Try this, put "cat" in A1 of the active sheet. Then what does

MsgBox Range("A1").Value

return?

 

[wedelia]

It still returns "Duplicate - Script file name".

 

[mikerickson]

Is it possible that the workbook has two Subs with the same name?