Thread: 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

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

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
>