Problems with vba

[pwatson04]

Hi i am new to ba and i was trying to create a system via excel but i didn't meet all the requirements that i wanted so i thought i would have a bash at using vba. ive never used vba before and im still learning it. below is my code that i have written so far:

Private Sub Worksheet_Calculate()

Call Betrow1
End Sub

Sub Betrow1()
'Decides whether to back or lay according to calculations in excel'
If Range("L13") = "Back" Then
Call Back_Offsetrow1
ElseIf Range("L13") = "Lay" Then
Call Lay_Offsetrow1
End If

End Sub

Sub Back_Offsetrow1()
'If a back is triggered then this selects the odds and the stake'
Range("Q5") = "BACK"
Range("R5") = "=F5"
Range("S5") = "2"
'triggers for the matched check procedure to be carried out'
Call Matchedrow1

End Sub

Sub Matchedrow1()
'to check if matched stake = stake, using counter to try and create a loop until it is matched'
Dim Counter As Integer
Counter = 0

If Range("W5") = Range("S5") Then Counter = Counter + 1
'if matched then moves onto clear row procedure'
If Counter = 1 Then Call clearlayrow1


End Sub

Sub clearlayrow1()
'triggers for clear to appear in trigger cell and clear the odds and stake box'
Range("Q5") = "CLEAR"
Range("R5:S5").Clear
'triggers for the lay part to be carried out'
Call layrow1
End Sub

Sub layrow1()
'lay procedure where takes the back stake - 0.1 and then works out the stake'
Range("Q5") = "LAY"
Range("R5") = "=F5-0.1"
Range("S5") = "=(F5/R5])* 2"
'calls for matched lay bet procedure to be carried out'
Call matchedlayrow1

End Sub

Sub matchedlayrow1()

Dim Counter As Integer
Counter = 0

If Range("W5") = Range("S5") Then Counter = Counter + 1
'if matched then moves onto clear row procedure'
If Counter = 1 Then Call clearrow1


End Sub

Sub clearrow1()
'triggers for clear trigger to begin and clear odds and stake'
Range("Q5") = "CLEAR"
Range("R5:S5").Clear

End Sub

So far it goes okay until the first check to see if the matched back stake has been matched. but the problem is that i dont know how to loop until the matched stake has been matched.

there are a few other problems as you can probably see but ive never used vba before so any tips on how to do the above or any other errors that you can see would be much appreciated

cheers

paul

[phrenetic]

Paul

You can get the spreadsheet to indicate when a bet has been matched without having to loop within your VBA.

What I do is pick a cell in column V underneath all of the entries (I use cell V45) and put in the following formula:
=COUNTA(V5:V44)-COUNTIF(V5:V44,"0")

When a bet gets matched, cell V45 is updated to 1. If you place multiple bets (on different lines) then cell V45 shows the number of bets matched.

In your VBA, you can just check for V45 having a value > 0.

Hope this helps

Alistair

[pwatson04]

Cheers mate i think thats worked but now i just keep getting an out of stack space on this line:

If Range("L13") = "Back" Then
Call Back_Offsetrow1

and then it crashes. i makes no sense

[GeorgeUK]

I think your code is continually looping.

If L13 = Back then
Q5 = Back
R5 = "=F5"
S5 = 2

But if nothing is changing L13, then it will just keep putting that data in repeatedly. I can't see anything that is removing the "Back" from L13

[pwatson04]

Yeah thats what it keeps doing. i will explain where L13 comes from first though. At the bottom of the spreadsheet i have a few excel formulas that checks odds and it will either flag up with a "BACK" of a "LAY". i want the vba code to constantly check for one of these triggers to appear. then when one does i want it too carry out the "back_offset" or "lay_offset" shown above. if you can give me any advice on this then that would be great.

cheers

[phrenetic]

Paul

Once it has placed the bet, what do you want it to do?

At the moment, it will continually try to place a bet, but you would be better off setting a flag once the bet has been placed and checking the flag each time you call the routine. If the flag is set, then don't try to place a bet again.

Of course, you will need to reset the flag once you get a new race.

Alistair

[GeorgeUK]

As phrenetic has said, you need to tell the code what to do after it has blaced a bet.

If L13 = BACK
place a bet

If L13 doesn't change then each time the code is run, it will see L13 is Back and place another bet.

If L13 = BACK AND Z13 = ""
place a bet
Z13 = bet made

This would allow the code to see that a bet has been made each time it checks Z13 (or whatever cell you want). If Z13 isn't empty, then a bet won't be made. Each time a new race is loaded just check that Z13 is being deleted.