Evaluate error?

[adulador]

Hi,

I'm running the evaluate vba function.

Sub Test1()
Dim dbValue As Double
dbValue = 3.1415
MsgBox Evaluate("SIN(" & dbValue & ")")
End Sub

Sub Test2()
Dim dbValue As Double
dbValue = 3
MsgBox Evaluate("SIN(" & dbValue & ")")
End Sub

The first does not work, but the second does. It's not because of Pi, it's because of floating point. If dbValue, instead of 3.1415, has a value of 1, it works, but 1.1 is not working, neither 1.2, nor 1.2345678,... only integers.

Any idea?

Kind Regards

 

[Norie]

They both work for me.

 

[adulador]

I knew they should work both, and both the same, but it halts me an:
Execution Error, 13, Type Mismatch.

So when you posted, I went to try in my other PC. It happens again... so I start to consider i'ts because of spanish keyboard, spanish configuration... and Excel decimal delimiter is not the same as VBA decimal delimiter...

Just have to make the English configuration the default.

Thank you, I'm sure I will never get to this if did not have an English test.

Best Regards

 

[Norie]

I think you might be right about the separator thing, didn't think of that.

 

[mikerickson]

Have you tried
Evaluate("SIN(" & CStr(dbValue) & ")")

 

[adulador]

Thanks Mike, but

Evaluate("SIN(" & CStr(dbValue) & ")")

didn't work.

The problem is the internal configuration. Changing it solves the problem.

With the latinoamerican system configuration the decimal separator is a ",". Internally, when VBA reads the dbValue, converts it to a number, with the System configuration format. From there, the number has a "," which is kept after the CStr result.

Regards.

P.D. I don't know how to rename this thread to [SOLVED]

 

[Colin Legg]

When you use evaluate like that, think of it as evaluating a formula in a worksheet. It's going to evaluate the SIN() worksheet function and it will expect decimal separators etc to be how they would look in a formula.

VBA has its own SIN() function. If you use it instead, not only will the calculation be fractionally faster than evaluating its worksheet function equivalent, but you will also avoid this international compatability issue.

Sub example()
    Const dbPI As Double = 3.1415
 
    Debug.Print Sin(dbPI)
 
    'which is equivalent of:
 
    Debug.Print VBA.Sin(dbPI)
 
End Sub

Edit: Just as a side note, its worth noting that SIN() expects radians, not degrees.

 

[adulador]

Hi Colin,

I posted this question because I'm creating an Excel Soft to handle construction budgets. In my country (Spain) there is an association of software companies dealing with that branch of soft. They had an ASCII format in order to store all the information. The case is that, when dealing with dimensions, their format let represent four variables (a,b,c,d) renaming the tipical unities x lenght x width x height with those (a,b,c,d), and their store the formula to apply in a commentary field.

So when, translating this to VBA I need to read a particular formula which can vary from one line to another (so it's not programmable), and I need to operate this trought the Eval function. And there was where all my problems started... XDD

Anyway, very pleased you for your kindness with the answers.

 

[adulador]

I mean, tipically they use the fields stored in unities, widths, lenghts, heights as any person will do normally to get a volume/Area.
But if they need to represent an strange formula to get that volume/Area/measurement that is not a function of unities, widths, lenghts, heights, they use that little trick to pass the formula (with up to four variables) to their specific programs. In VBA I can only imagine using the Eval function for this.

Any other ideas will be wellcomed.

 

[xladept]

Hi adulador,

Your code works on my machine. The sine of 3.1415 radians is 9.250245 x 10^-5.