I have a userform with a list of variables and operators which a user
can press to create a custom formula. The variable text entered by
clicking the button is the same name as that used in the code behind
the scenes. So a user would be able to create a text string looking
like "Field1 * Field2 - (Field3 + Field4)" etc.. Is there a way to
actually calculate a value from the string the userform returns? So for
the above example if all values were 10 you would get the number 80.
All the users are using Excel 2000.

Hi Tatumsa,

'=============
Public Sub Tester00Z1()
Dim Field1 As String
Dim Field2 As String
Dim Field3 As String
Dim Field4 As String

Field1 = "10"
Field2 = "10"
Field3 = "10"
Field4 = "10"

MsgBox CLng(Field1) * CLng(Field2) - (CLng(Field3) + CLng(Field4))

End Sub
'<<=============

In your scenario, the Field values are likely to represent TextBox values.


---
Regards,
Norman



"Tatumsa" wrote in message
oups.com...
I have a userform with a list of variables and operators which a user
can press to create a custom formula. The variable text entered by
clicking the button is the same name as that used in the code behind
the scenes. So a user would be able to create a text string looking
like "Field1 * Field2 - (Field3 + Field4)" etc.. Is there a way to
actually calculate a value from the string the userform returns? So for
the above example if all values were 10 you would get the number 80.
All the users are using Excel 2000.

If I understand you correctly, you want to take the string returned by the
userform, plug in the actual values of Fieldx, and calculate the formula.
You could use the REPLACE function and the EVALUATE function, i.e.:

Dim strFormula As String 'Actually, the formula returned from the userform
Dim Field1 As Double 'Example variables you'd want the original values
Dim Field2 As Double '(also from the userform?)
Dim Field3 As Double
Dim Field4 As Double
Dim Value As Double

Field1 = 10
Field2 = 10
Field3 = 10
Field4 = 10

strFormula = "Field1 * Field2 - (Field3 + Field4)" 'String returned from
userform

'Replace function - Replaces one string with another (even if that is a
numeric value)
strFormula = Replace(strFormula, "Field1", Field1)
strFormula = Replace(strFormula, "Field2", Field2)
strFormula = Replace(strFormula, "Field3", Field3)
strFormula = Replace(strFormula, "Field4", Field4)

'Evaluate the resulting string, now with the values plugged in
Value = Evaluate(strFormula)

The only problem I'd see with this is if you had a variable number of
"fields" returned in the formula. You'd need to determine the largest one
(i.e., Field4 above). Then you could put your REPLACE function in a loop
(For i = 1 to MaxNoOfFields) and go from there.


"Tatumsa" wrote:

I have a userform with a list of variables and operators which a user
can press to create a custom formula. The variable text entered by
clicking the button is the same name as that used in the code behind
the scenes. So a user would be able to create a text string looking
like "Field1 * Field2 - (Field3 + Field4)" etc.. Is there a way to
actually calculate a value from the string the userform returns? So for
the above example if all values were 10 you would get the number 80.
All the users are using Excel 2000.

Hi all. Thanks for your replies. I think Eric's is the one that will
work for me.

In reply to your question, yes, there will be a variable number of
fields. There are 9 buttons which add a static variable name (same
names as the variables in the parent form, as well as an input box to
allow entry of a user number).

I could do a blanket 1 to 9 jobbie replacing all fieldnames with their
equivalent value. I'm guessing that if a value was entered into the
string via the input box it would be processed properly by the
evaluate?

Thanks,

Scott.

Eric White wrote:
If I understand you correctly, you want to take the string returned by the
userform, plug in the actual values of Fieldx, and calculate the formula.
You could use the REPLACE function and the EVALUATE function, i.e.:

Dim strFormula As String 'Actually, the formula returned from the userform
Dim Field1 As Double 'Example variables you'd want the original values
Dim Field2 As Double '(also from the userform?)
Dim Field3 As Double
Dim Field4 As Double
Dim Value As Double

Field1 = 10
Field2 = 10
Field3 = 10
Field4 = 10

strFormula = "Field1 * Field2 - (Field3 + Field4)" 'String returned from
userform

'Replace function - Replaces one string with another (even if that is a
numeric value)
strFormula = Replace(strFormula, "Field1", Field1)
strFormula = Replace(strFormula, "Field2", Field2)
strFormula = Replace(strFormula, "Field3", Field3)
strFormula = Replace(strFormula, "Field4", Field4)

'Evaluate the resulting string, now with the values plugged in
Value = Evaluate(strFormula)

The only problem I'd see with this is if you had a variable number of
"fields" returned in the formula. You'd need to determine the largest one
(i.e., Field4 above). Then you could put your REPLACE function in a loop
(For i = 1 to MaxNoOfFields) and go from there.


"Tatumsa" wrote:

I have a userform with a list of variables and operators which a user
can press to create a custom formula. The variable text entered by
clicking the button is the same name as that used in the code behind
the scenes. So a user would be able to create a text string looking
like "Field1 * Field2 - (Field3 + Field4)" etc.. Is there a way to
actually calculate a value from the string the userform returns? So for
the above example if all values were 10 you would get the number 80.
All the users are using Excel 2000.