Use VBA to find slope =0

[artz]

Hi,

I would like to WorksheetFunction to find where my data has zero slope using VBA. Data are arranged in columns starting at A2 for the X values and B2 for the Y values. The data length is dynamic. The outputs will be Slope =0 (or blank) in column C starting at C2. Column D will output the corresponding value of column B when column C = 0, starting at D2.

My attempt at some VBA code for this compiles but doesn't run. Code is:

Sub Slope()
    Dim xL As Range
    Dim yL As Range
    Dim intSlopeL As Single
    Set xL = Range("A2:A" & Range("A1000").End(xlUp).Row)
    Set yL = Range("B2:B" & Range("B1000").End(xlUp).Row)
    intSlopeL = Application.WorksheetFunction.Slope(yL, xL)
    Sheets("Sheet4").Range("C2:C").Value = intSlopeL
    Sheets("Sheet4").Range("D2:D") = Range("C2:C").Value
End Sub

A sample of the data is shown below:

Time	Voltage
0	1.03
0.1	1.08
0.2	1.14
0.3	1.17
0.4	1.21
0.5	1.24
0.6	1.25
0.7	1.25
0.8	1.24
0.9	1.21
1	1.18
1.1	1.14
1.2	1.08
1.3	1
1.4	0.89
1.5	0.81
1.6	0.75
1.7	0.71
1.8	0.66
1.9	0.6
2	0.53
2.1	0.48
2.2	0.46
2.3	0.47
2.4	0.48
2.5	0.5
2.6	0.51
2.7	0.54
2.8	0.59
2.9	0.63
3	0.69
3.1	0.74
3.2	0.79
3.3	0.84
3.4	0.88
3.5	0.93
3.6	0.97
3.7	1.02
3.8	1.05
3.9	1.08
4	1.1
4.1	1.11
4.2	1.1
4.3	1.09
4.4	1.07
4.5	1.05
4.6	1.02
4.7	0.97
4.8	0.92
4.9	0.84
5	0.78
5.1	0.75
5.2	0.73
5.3	0.69
5.4	0.65
5.5	0.6
5.6	0.59
5.7	0.61
5.8	0.65
5.9	0.69
6	0.74
6.1	0.78
6.2	0.83
6.3	0.88
6.4	0.94
6.5	0.99
6.6	1.05
6.7	1.1
6.8	1.14
6.9	1.19
7	1.22
7.1	1.25
7.2	1.29
7.3	1.32
7.4	1.34
7.5	1.36
7.6	1.37
7.7	1.36
7.8	1.35
7.9	1.33
8	1.3
8.1	1.24
8.2	1.19
8.3	1.15
8.4	1.12
8.5	1.08
8.6	1.03
8.7	0.95
8.8	0.91
8.9	0.88
9	0.88
9.1	0.89
9.2	0.88
9.3	0.88
9.4	0.9
9.5	0.93
9.6	0.97
9.7	1.02
9.8	1.06
9.9	1.11
10	1.15
10.1	1.19
10.2	1.23
10.3	1.26
10.4	1.29
10.5	1.32
10.6	1.35
10.7	1.36
10.8	1.36
10.9	1.35
11	1.33
11.1	1.3
11.2	1.26
11.3	1.23
11.4	1.18
11.5	1.12
11.6	1.05
11.7	0.96
11.8	0.9
11.9	0.87
12	0.84
12.1	0.79
12.2	0.73
12.3	0.69
12.4	0.68
12.5	0.71
12.6	0.74
12.7	0.76
12.8	0.79
12.9	0.82
13	0.87
13.1	0.92
13.2	0.97
13.3	1.02
13.4	1.07
13.5	1.12
13.6	1.16
13.7	1.2
13.8	1.23
13.9	1.25
14	1.28
14.1	1.29
14.2	1.29
14.3	1.28
14.4	1.25
14.5	1.22
14.6	1.19
14.7	1.16
14.8	1.09
14.9	1.01
15	0.92
15.1	0.86
15.2	0.82
15.3	0.77
15.4	0.71
15.5	0.64
15.6	0.58
15.7	0.56
15.8	0.55

Any help to get my code working would greatly appreciated.

Thanks,

-Art

 

[Eric W]

The slope will be zero when consecutive y-values are equal. In your example, that happens at time .6, 8.9, 9.2, 10.7, 14.1, and several other places are close. You can do this without VBA with a basic formula:

=IF(B2=B3,0,"")

The problem with your macro is that you're using the Slope function on the entire range. This assumes you're trying to create a best-fit line through your entire data and returns the slope for that line. It won't return the locations where the slope is 0. To do that, you'd need to successively apply the Slope function to smaller ranges. Something like:

    For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row - 1
         intSlopeL = Application.WorksheetFunction.Slope(Cells(i, "B").Resize(2), Cells(i, "A").Resize(2))
         If intSlopeL = 0 Then Cells(i, "C") = 0
    Next i

2-cell ranges if you just want a small comparison, equivalent to the formula I posted, or you could use slightly larger ranges, say 3-6 data points. Then you could check for a slope less than .1 and display the midpoint of that range to show sections of your data that are nearly flat for several tenths.

Hope this gives you some ideas.

 

[artz]

Hi Eric,

Thanks for your response and suggestions. How would I modify your VBA code to find a zero or close to zero value over say 3 or 5 data points?

Your help is appreciated.

Thanks,

-Art

 

[shg]

I would instead look for local minima and maxima.

 

[artz]

Hi Eric,

If you have a reliable code set to do that in VBA (works with my posted data), could you please post the code?

Thanks,

-Art