Use VBA to find slope =0

artz

Well-known Member
Joined
Aug 11, 2002
Messages
791
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:

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

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,141
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:

Rich (BB code):
    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

Well-known Member
Joined
Aug 11, 2002
Messages
791
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,798
Office Version
  1. 2010
Platform
  1. Windows
I would instead look for local minima and maxima.
 

artz

Well-known Member
Joined
Aug 11, 2002
Messages
791
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
 

Forum statistics

Threads
1,137,125
Messages
5,679,769
Members
419,855
Latest member
Eddier32

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top