Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VBA - function doesn't calculate unless clicked on
Hi all
I have written a function in vba (I am pretty new to vba). It takes two arrays and finds the gradient. You also have two inputs to remove points from the beginning or end. It works exactly how I want it to except when I changed from the dependant cell (cut min and max) being on the same sheet to being on a new sheet and then changed the contents of these 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 or 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 Function |
#2
|
|||
|
|||
Hi this might help put you on the right track
Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Clicked NO to save, should have clicked YES | Excel Discussion (Misc queries) | |||
Function Arguments dialog vanishes when clicked an inserted shape | Excel Worksheet Functions | |||
function doesn't calculate | Excel Worksheet Functions | |||
Change cell value when right-clicked/double-clicked | Excel Programming | |||
Function Won't Calculate -- Sometimes | Excel Discussion (Misc queries) |