My VBA Function Won't Recalculate

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

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]...
>
> 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
>
>
> --
> soteman2005
> ------------------------------------------------------------------------
> soteman2005's Profile:
> http://www.excelforum.com/member.php...o&userid=29078
> View this thread: http://www.excelforum.com/showthread...hreadid=489778
>

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


--------------------------------------------------------------------------------

Thanks

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]...
>
> 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
>
>
> --------------------------------------------------------------------------------
>
> Thanks
>
>
> --
> soteman2005
> ------------------------------------------------------------------------
> soteman2005's Profile:
> http://www.excelforum.com/member.php...o&userid=29078
> View this thread: http://www.excelforum.com/showthread...hreadid=489778
>