VBA - function doesn't calculate unless clicked on

[dannyg9090]

Hi all

I have written a function in vba (I am pretty new to vba). It takes tw
arrays and finds the gradient. You also have two inputs to remove point
from the beginning or end. It works exactly how I want it to except whe
I changed from the dependant cell (cut min and max) being on the sam
sheet to being on a new sheet and then changed the contents of thes
cells the function returns a #value! error.

when i click on the function within the worksheet (in the formula bar
and press enter the function suddenly works fine again.

Is there an autocalculate property which needs to be enabled o
something?

Thanks

Danny

code below:

Public Function myGradient(lngMinRow As Long, InputColumn As String
lngCutRows As Long)


'declare variables
Dim xArray, yArray, lngMaxRow As Long

'define variables
lngMaxRow = 79 - lngCutRows

ReDim xArray(1, lngMaxRow - lngMinRow)
xArray = Range(InputColumn & lngMinRow, InputColumn & lngMaxRow)

ReDim yArray(1, lngMaxRow - lngMinRow)
yArray = Range("A" & 89, "A" & 89 + lngMaxRow - lngMinRow)


'gradient calculation
myGradient = Application.WorksheetFunction.Slope(xArray, yArray)


End Functio

 

[merjet]

After a quick look, I believe your problem is as follows. Your
function includes Range. As written VBA assumes the Range is on the
active sheet. Either Range needs qualified, e.g.
Worksheets("Sheet1").Range, or make your input arguments type Range.

 

[Toonies]

Hi this might help put you on the right track

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range