My VBA Function Won't Recalculate

[soteman2005]

Hi,

I have created a function in VBA to use in my excel worksheets. It is
an "if" function referring to a number on a different sheet i.e.
=if(sheet2!H15=1,do this,do that). It works fine except that it won't
recalculate when I change the number on the other sheet. I have to
press shift + F9 in order for it to give me the correct value. I have
tried application.volatile but it doesn;t have any effect. Any
suggestions?

Thanks

Adam

 

[Niek Otten]

Hi Adam,

All input to a function should be in the argument list, that is, both in the
definition of the function and in the function call in a formula. That is
the only way Excel knows there is a dependency and so knows when to
recalculate.
Application.Volatile is often suggested as a solution, but
a. it may slow down calculation because it always recalculates, also when
not necessary
b. There is no guarantee that cells are recalculated in the correct order.

--
Kind regards,

Niek Otten

"soteman2005" <[email protected]>
wrote in message
news:[email protected]...

 

[soteman2005]

I'm not quite sure as I follow but the cell it refers to isn't
referenced in the actual function but the function I have used is part
of an =if() statement in excel, which uses the number outside the
worksheet to select whether to use the function or not.

Here is my code....namerange shift is another function that I have used
to select cells in a named range that are to the left or right of the
current column as for example, when using Excel's SUM function, excel
tried to SUM the entire name range.

the function is being used inside an IF statement in Excel

=if(DC_Options_Subscriber_Number=1, LRP,
CalculateAverageSubscribersEOP(DC_Subscribers_eop_Column()))

code:
--------------------------------------------------------------------------------


Function CalculateAverageSubscribersEOP(ByRef EOPSubscriberRange As
Range, ByVal ThisCol As Double) As Double

Dim TestNumber As Boolean
Dim SubRangeValMinus1
Dim SubRangeVal
Dim SubRangeValPlus1

SubRangeValMinus1 = Range(EOPSubscriberRange.Address).Cells(1, ThisCol
- 1).Value
SubRangeVal = Range(EOPSubscriberRange.Address).Cells(1, ThisCol).Value

SubRangeValPlus1 = Range(EOPSubscriberRange.Address).Cells(1, ThisCol +
1).Value

TestNumber =
Application.WorksheetFunction.IsNumber(NameRangeShift(EOPSubscriberRange,
-1, ThisCol))

If TestNumber Then
CalculateAverageSubscribersEOP = (SubRangeVal + SubRangeValMinus1) / 2

Else
CalculateAverageSubscribersEOP = (SubRangeVal * SubRangeVal /
SubRangeValPlus1 + SubRangeVal) / 2
End If

End Function

 

[Niek Otten]

You do indeed access cells directly from within the function, not via the
argument list; to the left or right of the "current" column. You should
include those cells in the argument list.
BTW in your worksheet formula I don't see you supplying the column number,
just the range. Don't you get an error because the number of arguments is
incorrect?

--
Kind regards,

Niek Otten

"soteman2005" <[email protected]>
wrote in message
news:[email protected]...