|
VBA Function running order
I am very confused, hopefully some kind person can help me?
I have a spreadsheet recording results and various information across a spreadsheet. So day 1 results and info are stored in A3,B3,C3........H3 day 2 in A4,B4,C4.......H4 last day in A1500,B1500,C1500......H1500 I have a function in K3 that uses the info in A3 to H3 to create a result. This function is replicated down to K1500 and works perfectly in that I get the expected results displayed. However when stepping through with the debugger I see that the order of the cells being calculated is very strange in that it goes K3,K4,K1500,K1499,K1498........K5 THis wouldn't be a problem except that I now need to ensure that the cells are filled out sequentially ie K3,K4,K5.....K1500 Anyone have any ideas? Many Thanks Kevin |
VBA Function running order
The ordering is complicated because a change in the spreadsheet causes a
worksheet change event. This event, depending if it is enable, can cause the VBA code to stop executing and the worksheet to update. Therefore if the macro changes cell B4 and cell C5 has a function using cell B4, C5 will also get updated. "KevinTHFC" wrote: I am very confused, hopefully some kind person can help me? I have a spreadsheet recording results and various information across a spreadsheet. So day 1 results and info are stored in A3,B3,C3........H3 day 2 in A4,B4,C4.......H4 last day in A1500,B1500,C1500......H1500 I have a function in K3 that uses the info in A3 to H3 to create a result. This function is replicated down to K1500 and works perfectly in that I get the expected results displayed. However when stepping through with the debugger I see that the order of the cells being calculated is very strange in that it goes K3,K4,K1500,K1499,K1498........K5 THis wouldn't be a problem except that I now need to ensure that the cells are filled out sequentially ie K3,K4,K5.....K1500 Anyone have any ideas? Many Thanks Kevin |
VBA Function running order
|
VBA Function running order
Thanks for the response Joel. I don't think it answers my question but I will
try disabling the worsheet change event to see if it makes a difference. The worksheet is not being changed by anything else apart from the VBA function. Like I say everything has the right answer but it is not until I use the debugger that I can see that the answers are not written from 'top' to 'bottom' as I expected and this will cause me problems later on. Kevin "Joel" wrote: The ordering is complicated because a change in the spreadsheet causes a worksheet change event. This event, depending if it is enable, can cause the VBA code to stop executing and the worksheet to update. Therefore if the macro changes cell B4 and cell C5 has a function using cell B4, C5 will also get updated. "KevinTHFC" wrote: I am very confused, hopefully some kind person can help me? I have a spreadsheet recording results and various information across a spreadsheet. So day 1 results and info are stored in A3,B3,C3........H3 day 2 in A4,B4,C4.......H4 last day in A1500,B1500,C1500......H1500 I have a function in K3 that uses the info in A3 to H3 to create a result. This function is replicated down to K1500 and works perfectly in that I get the expected results displayed. However when stepping through with the debugger I see that the order of the cells being calculated is very strange in that it goes K3,K4,K1500,K1499,K1498........K5 THis wouldn't be a problem except that I now need to ensure that the cells are filled out sequentially ie K3,K4,K5.....K1500 Anyone have any ideas? Many Thanks Kevin |
VBA Function running order
I don't think that the code will help as the fuction works fine, but here it
is anyway Public Function Winnings(iFinish As Integer, dOdds As Double, dSP As Double, iRating As Integer, iRatingCutoff As Integer, dCutoffOddsHigh As Double, dBetfairMarkup As Double, iClumpLevel As Integer, dCutoffOddsLow As Double) As Double Static sClump As Integer Dim dWinnings As Double Dim dStake As Double If (iRating iRatingCutoff) Then If (sClump 0) Then If iFinish = 1 Then If ((dOdds <= dCutoffOddsHigh) And (dOdds = dCutoffOddsLow)) Then sClump = sClump - 1 dWinnings = 0 End If ElseIf ((dSP <= dCutoffOddsHigh) And (dSP = dCutoffOddsLow)) Then sClump = sClump - 1 dWinnings = 0 End If Else If iFinish = 1 Then If ((dOdds <= dCutoffOddsHigh) And (dOdds = dCutoffOddsLow)) Then dStake = 1 dWinnings = -1 * dStake * dOdds * dBetfairMarkup sClump = iClumpLevel End If Else If ((dSP <= dCutoffOddsHigh) And (dSP = dCutoffOddsLow)) Then dStake = 1 dWinnings = dStake * 0.95 End If End If End If Else dWinnings = 0 End If Winnings = (dWinnings) Like I have previously said this function is in every row fro 3 to 1500 but results appear in the following order row3, row4, row1500, row 1499, row 1498....row6, row5 Kevin "Don Guillett" wrote: Post your code -- Don Guillett Microsoft MVP Excel SalesAid Software "KevinTHFC" wrote in message ... I am very confused, hopefully some kind person can help me? I have a spreadsheet recording results and various information across a spreadsheet. So day 1 results and info are stored in A3,B3,C3........H3 day 2 in A4,B4,C4.......H4 last day in A1500,B1500,C1500......H1500 I have a function in K3 that uses the info in A3 to H3 to create a result. This function is replicated down to K1500 and works perfectly in that I get the expected results displayed. However when stepping through with the debugger I see that the order of the cells being calculated is very strange in that it goes K3,K4,K1500,K1499,K1498........K5 THis wouldn't be a problem except that I now need to ensure that the cells are filled out sequentially ie K3,K4,K5.....K1500 Anyone have any ideas? Many Thanks Kevin |
VBA Function running order
The updating has nothing to do with your function. It has to do with the way
excel updates your worksheet. Excel is updating your worksheet in semi-random order which you cannot control. Your function is simply another function on the worksheet like tthe standard spreadsheet functions. Order of function calls cannot make a difference in the result. Excel keeps on updating cells until it is fully updated and creattes an error if any recursive calls are located in the worksheet. the results will always be the same (unless a microsoft bug exists) or use the random function. "KevinTHFC" wrote: I don't think that the code will help as the fuction works fine, but here it is anyway Public Function Winnings(iFinish As Integer, dOdds As Double, dSP As Double, iRating As Integer, iRatingCutoff As Integer, dCutoffOddsHigh As Double, dBetfairMarkup As Double, iClumpLevel As Integer, dCutoffOddsLow As Double) As Double Static sClump As Integer Dim dWinnings As Double Dim dStake As Double If (iRating iRatingCutoff) Then If (sClump 0) Then If iFinish = 1 Then If ((dOdds <= dCutoffOddsHigh) And (dOdds = dCutoffOddsLow)) Then sClump = sClump - 1 dWinnings = 0 End If ElseIf ((dSP <= dCutoffOddsHigh) And (dSP = dCutoffOddsLow)) Then sClump = sClump - 1 dWinnings = 0 End If Else If iFinish = 1 Then If ((dOdds <= dCutoffOddsHigh) And (dOdds = dCutoffOddsLow)) Then dStake = 1 dWinnings = -1 * dStake * dOdds * dBetfairMarkup sClump = iClumpLevel End If Else If ((dSP <= dCutoffOddsHigh) And (dSP = dCutoffOddsLow)) Then dStake = 1 dWinnings = dStake * 0.95 End If End If End If Else dWinnings = 0 End If Winnings = (dWinnings) Like I have previously said this function is in every row fro 3 to 1500 but results appear in the following order row3, row4, row1500, row 1499, row 1498....row6, row5 Kevin "Don Guillett" wrote: Post your code -- Don Guillett Microsoft MVP Excel SalesAid Software "KevinTHFC" wrote in message ... I am very confused, hopefully some kind person can help me? I have a spreadsheet recording results and various information across a spreadsheet. So day 1 results and info are stored in A3,B3,C3........H3 day 2 in A4,B4,C4.......H4 last day in A1500,B1500,C1500......H1500 I have a function in K3 that uses the info in A3 to H3 to create a result. This function is replicated down to K1500 and works perfectly in that I get the expected results displayed. However when stepping through with the debugger I see that the order of the cells being calculated is very strange in that it goes K3,K4,K1500,K1499,K1498........K5 THis wouldn't be a problem except that I now need to ensure that the cells are filled out sequentially ie K3,K4,K5.....K1500 Anyone have any ideas? Many Thanks Kevin |
VBA Function running order
Don: Not sure why Kevin is so concern with order. Do you know what order
EXCEL actually updates the worksheet. It is random, but it should not matter. I know EXCEL keeps on updating until all the cell are updated. I have nevver had any problems. "Don Guillett" wrote: Perhaps a worksheet_change event that only fires when the cell is updated. Ask it to calculate only the same row. target.offset(,2)= target.offset(,3)= -- Don Guillett Microsoft MVP Excel SalesAid Software "KevinTHFC" wrote in message ... I don't think that the code will help as the fuction works fine, but here it is anyway Public Function Winnings(iFinish As Integer, dOdds As Double, dSP As Double, iRating As Integer, iRatingCutoff As Integer, dCutoffOddsHigh As Double, dBetfairMarkup As Double, iClumpLevel As Integer, dCutoffOddsLow As Double) As Double Static sClump As Integer Dim dWinnings As Double Dim dStake As Double If (iRating iRatingCutoff) Then If (sClump 0) Then If iFinish = 1 Then If ((dOdds <= dCutoffOddsHigh) And (dOdds = dCutoffOddsLow)) Then sClump = sClump - 1 dWinnings = 0 End If ElseIf ((dSP <= dCutoffOddsHigh) And (dSP = dCutoffOddsLow)) Then sClump = sClump - 1 dWinnings = 0 End If Else If iFinish = 1 Then If ((dOdds <= dCutoffOddsHigh) And (dOdds = dCutoffOddsLow)) Then dStake = 1 dWinnings = -1 * dStake * dOdds * dBetfairMarkup sClump = iClumpLevel End If Else If ((dSP <= dCutoffOddsHigh) And (dSP = dCutoffOddsLow)) Then dStake = 1 dWinnings = dStake * 0.95 End If End If End If Else dWinnings = 0 End If Winnings = (dWinnings) Like I have previously said this function is in every row fro 3 to 1500 but results appear in the following order row3, row4, row1500, row 1499, row 1498....row6, row5 Kevin "Don Guillett" wrote: Post your code -- Don Guillett Microsoft MVP Excel SalesAid Software "KevinTHFC" wrote in message ... I am very confused, hopefully some kind person can help me? I have a spreadsheet recording results and various information across a spreadsheet. So day 1 results and info are stored in A3,B3,C3........H3 day 2 in A4,B4,C4.......H4 last day in A1500,B1500,C1500......H1500 I have a function in K3 that uses the info in A3 to H3 to create a result. This function is replicated down to K1500 and works perfectly in that I get the expected results displayed. However when stepping through with the debugger I see that the order of the cells being calculated is very strange in that it goes K3,K4,K1500,K1499,K1498........K5 THis wouldn't be a problem except that I now need to ensure that the cells are filled out sequentially ie K3,K4,K5.....K1500 Anyone have any ideas? Many Thanks Kevin |
| All times are GMT +1. The time now is 12:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com