Evaluate error when using 2

[Guest]

This is the weirdest thing I've ever seen. I have several strings that
contain either a number or a mathmatical expression. I set variables equal
to the solution of the expression or the number using the Evaluate method on
each string.

When the string evaluated is "2" the code bombs.

Here's the section of code:

Sub quad()
Dim a, b, c, d
Dim temp(3) As String
Set rng4 = rng1.Cells.Find(what:="p1", LookIn:=xlValues, lookat:=xlPart)
If Not rng4 Is Nothing Then
For i = 0 To 3
temp(i) = Mid(rng4.Offset(i, 0), InStr(1, rng4.Offset(i, 0), """", 1) + 1)
temp(i) = Left(temp(i), Len(temp(i)) - 1)
Next
a = Split(temp(0), """, """, -1)
b = Split(temp(1), """, """, -1)
c = Split(temp(2), """, """, -1)
d = Split(temp(3), """, """, -1)
Cells(2, 5).Value = a(0) + a(1) + a(2)
For j = 0 To 2
a(j) = Evaluate(a(j))
b(j) = Evaluate(b(j))
c(j) = Evaluate(c(j))
d(j) = Evaluate(d(j))
a(j) = CDbl(a(j))
b(j) = CDbl(b(j))
c(j) = CDbl(c(j))
d(j) = CDbl(d(j))
Next
If a(2) = b(2) And a(2) = c(2) And a(2) = d(2) Then
NodeArea = ((c(0) - a(0)) * (d(1) - b(1)) - (d(0) - b(0)) * (c(1) -
a(1))) / 2
Else
NodeArea = "This quadrilateral is not 2-Dimensional"
End If
Else
End If
nodes 'Calls the funtion that determines the first node
output
End Sub


The code finds a cell that starts with 'p1' and then pulls the data out of
the next three cells as well.

Here is a sample of the text it reads:

quadrilateral quadrilateral.1
units = meters
p1 = "0.0", "0.0", "0.0"
p2 = "2", "0.0", "0.0"
p3 = "1.0", "1.0", "0.0"
p4 = "0.0", "1.0", "0.0"
active = BOTH sides = SINGLE submodel = MAIN include = RADK,CC
initial_id = "4"
iconductor = "1"
ngamma = "1"
rot1 = x,"0.000000" rot2 = y,"0.000000" rot3 = z,"0.000000" tx = "-1.000000"
ty = "-0.500000" tz = "0.000000"
optics = "default_prop", "default_prop"
optics_angles = "0.0", "0.0"
material = "default_mat" thickness = "0.1"
color = Pink
initial_temp = "21.111111", Celsius

For the above text, the code bombs at p2 with the following error message:
"run-time error '438': object doesn't support this property or method"

If I change the p2 text to read any of the following, the code works fine:
p2 = "2.0", "0.0", "0.0"
p2 = "2.", "0.0", "0.0"
p2 = "+2", "0.0", "0.0"
p2 = "2+0", "0.0", "0.0"

This only happens when the string is "2". No other number causes this error.

Anyone have any ideas?

 

[Guest]

Hi,
Your code as posted runs OK for me (XL2003) with a p2 value of 2.
You say it bombs at "p2" but which code statement failed, as p2 is a
reference to data. Does it fail at the Split/Evaluate code?

It would appear the error is in code not supplied e.g. node? Or I have
misunderstood the problem?

FYI, I used the following data in cells b1 to b4 (rng1):

p1 "0.0", "0.0", "0.0"
p2 "2", "0.0", "0.0"
p3 "1.0", "1.0", "0.0"
p4 "1.0", "1.0", "0.0"

It worked if I omitted the p2 to p4 (as these seem redundant) or changed 2
to 2.0

HTH

 

[Guest]

Toppers,

I have run the code on a blank spreadsheet with the same text for the p1-p4
lines and it works with no problem, just like you experienced. It seems to
be just in this workbook that the problem occurs. The debugger takes me to
the line 'b(j) = Evaluate(b(j))' as the error source. If I change the text
input so that the p1 line has the '2' in it then the debugger stops at 'a(j)
= Evaluate(a(j))' instead. As you mentioned the error could be in code I
didn't supply. But if this is the case wouldn't the debugger take me to that
code snippet instead of 'b(j)...'?

Thanks for the response, let me know if you have any other thoughts. For
reference I have included the code for 'nodes' and 'output' below.

Sub nodes()
sStr = "initial_id"
Set rng2 = rng1.Cells.Find(what:=sStr, LookIn:=xlValues, lookat:=xlPart)
If Not rng2 Is Nothing Then
Set rng1 = rng.CurrentRegion
node = Mid(rng2, InStr(1, rng2, """", 1) + 1)
node = Left(node, Len(node) - 1)
Else
node = 10000000
End If
End Sub

Sub output()
Sheets("Sheet1").Cells(entrycell, 1).Value = node
Sheets("Sheet1").Cells(entrycell, 2).Value = ShapeName
Sheets("Sheet1").Cells(entrycell, 3).Value = Round(NodeArea, 5) 'shptype
entrycell = entrycell + 1
End Sub

Mike