Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- soteman2005 ------------------------------------------------------------------------ soteman2005's Profile: http://www.excelforum.com/member.php...o&userid=29078 View this thread: http://www.excelforum.com/showthread...hreadid=489778 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
My VBA Function Won't Recalculate
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
My VBA Function Won't Recalculate
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(NameRangeSh ift(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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
My VBA Function Won't Recalculate
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" wrote in message ... 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(NameRangeSh ift(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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Recalculate in VBA | Excel Discussion (Misc queries) | |||
Refresh/Recalculate a Custom Function | Excel Worksheet Functions | |||
function does not recalculate sum | Excel Worksheet Functions | |||
will not recalculate | Excel Worksheet Functions | |||
Recalculate Custom Function | Excel Programming |