Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Speed up the process
hi
I have this code which can plot any formula with two variables, ie x + y = 0 The code takes the x values from A1:A2 and loops till y is found. This takes a while, so is there a way to speed this code. Sub PlotXY() Dim x As Double Dim i As Integer Dim y As Variant Dim f As Double Dim fs As String Dim fsx As String Dim fsy As Double fs = InputBox("enter fs") Range("c1") = fs With Range("A1") For i = 1 To 20 x = Cells(i, 1) y = -5.0001 fsx = Replace(fs, "x", x) Do y = y + 0.0001 If IsError(Evaluate(Replace(fsx, "y", y))) Or Abs(y) 5 Then y = "#N/A" Exit Do Else fsy = Evaluate(Replace(fsx, "y", y)) End If Loop Until Abs(fsy) < 0.001 .Cells(i, 2).Value = y Next i End With End Sub |
#2
|
|||
|
|||
Ali Baba Wrote: hi I have this code which can plot any formula with two variables, ie x + y = 0 The code takes the x values from A1:A2 and loops till y is found. This takes a while, so is there a way to speed this code. Sub PlotXY() Dim x As Double Dim i As Integer Dim y As Variant Dim f As Double Dim fs As String Dim fsx As String Dim fsy As Double fs = InputBox("enter fs") Range("c1") = fs With Range("A1") For i = 1 To 20 x = Cells(i, 1) y = -5.0001 fsx = Replace(fs, "x", x) Do y = y + 0.0001 If IsError(Evaluate(Replace(fsx, "y", y))) Or Abs(y) 5 Then y = "#N/A" Exit Do Else fsy = Evaluate(Replace(fsx, "y", y)) End If Loop Until Abs(fsy) 0.001 .Cells(i, 2).Value = y Next i End With End Sub First blush, without reading through all of your code is to read the cell values into matrices (x(20) and y(20).) Do all your looping in code and then place the value in the spreadsheet. There is too much overhead in writing to the sheet. -- jahoobob |
#3
|
|||
|
|||
well how to do this???
"jahoobob" wrote: Ali Baba Wrote: hi I have this code which can plot any formula with two variables, ie x + y = 0 The code takes the x values from A1:A2 and loops till y is found. This takes a while, so is there a way to speed this code. Sub PlotXY() Dim x As Double Dim i As Integer Dim y As Variant Dim f As Double Dim fs As String Dim fsx As String Dim fsy As Double fs = InputBox("enter fs") Range("c1") = fs With Range("A1") For i = 1 To 20 x = Cells(i, 1) y = -5.0001 fsx = Replace(fs, "x", x) Do y = y + 0.0001 If IsError(Evaluate(Replace(fsx, "y", y))) Or Abs(y) 5 Then y = "#N/A" Exit Do Else fsy = Evaluate(Replace(fsx, "y", y)) End If Loop Until Abs(fsy) 0.001 .Cells(i, 2).Value = y Next i End With End Sub First blush, without reading through all of your code is to read the cell values into matrices (x(20) and y(20).) Do all your looping in code and then place the value in the spreadsheet. There is too much overhead in writing to the sheet. -- jahoobob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculation Speed | Excel Discussion (Misc queries) | |||
Speed up and slow down, the auto-scroll. | Excel Discussion (Misc queries) | |||
How to Reduce Spreadsheet Size and Speed | Excel Worksheet Functions | |||
search multiple worksheets name with common text and process using | Excel Worksheet Functions | |||
Simplify Process with Excel 2003 | New Users to Excel |