VBA Function running order

[KevinTHFC]

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

 

[Joel]

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.

 

[Don Guillett]

Post your code

 

[KevinTHFC]

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

 

[KevinTHFC]

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

 

[Joel]

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.

 

[Don Guillett]

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)=

 

[Joel]

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.