Speed up the process

[Ali Baba]

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

 

[jahoobob]

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.

 

[Ali Baba]

well how to do this???

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.