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!

 

[wedelia]

Hi,

I think I know what's wrong. I have placed the code in under Microsoft Excel Object > Sheet1 (Sheet1) instead of a module. That's why I keep getting the error.