Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate error when using 2
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate error when using 2
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 "crazybass2" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate error when using 2
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 "Toppers" wrote: 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 "crazybass2" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
evaluate | Excel Worksheet Functions | |||
Evaluate function | Excel Worksheet Functions | |||
Might be a bug in Evaluate Formula? | Excel Discussion (Misc queries) | |||
evaluate #¡VALUE! and #!DIV/0! and other errors.... | Excel Worksheet Functions | |||
evaluate HYPERLINK() | Excel Worksheet Functions |