vba help

Please post any questions regarding the program here.

Moderator: 2020vision

vba help

Postby mak » Wed Jul 26, 2017 2:05 pm

can someone post an example how to make some calculations in vba and if conditions are met to place a LAY bet?

for example (I have the a function which calculates the ticks difference between back and lay odds name = getticks
so
how can I have vba to calculate the differnce between f5 h5 cells until the last used row or until row 50 for example and if is less than 10 ticks to place LAY bet in the correct selection?

thanks
mak
 
Posts: 1086
Joined: Tue Jun 30, 2009 8:17 am

Re: vba help

Postby Captain Sensible » Wed Jul 26, 2017 2:49 pm

If statements in VBA aren't too disimilar to excel ones, if anything they're easier to understand when you're writing them down than formulas. Plus you can use all the Or And etc commands to link them together. Here was a condensed version of your switching market routine i did whist looking at the other thread yesterday

Code: Select all
If (.Range("E2").Value = "In Play" And .Range("F2").Value <> "") Or .Range("F2").Value = "Closed" Then
If Not marketChanging Then
marketChanging = True
currentMarket = .Range("A1").Value
.Range("Q2").Value = -1

Else
If .Range("A1").Value <> currentMarket Then marketChanging = False

End If


As you can see it's basically If this do that, otherwise do this.

To find the difference between F5 and H5 using getTicks you'd simply put

Code: Select all
=getTicks(Range("F5").Value, Range("H5").Value)


Then just use an If statement if it's over 10 to fill in Q5, R5 and S5
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: vba help

Postby Captain Sensible » Wed Jul 26, 2017 3:10 pm

What we need now is someway to read and loop all the market data, as you can imagine there are plenty of different ways to acheive this aim but all rely on us finding the last cell with data. In order not to complicate things I'll post the code Osknows gave me, I'm sure he'll be OK with it being posted.

OK so we need a function to find the last cell so we stick the following function into a module so it can be accessed anywhere from the code

Code: Select all
'**PUT THIS IN A MODULE SO IS AVAILABLE TO ALL CODE
Public Function LastRow(ByVal rng As Range, Optional Offset As Long) As Long
'The most accurate method to return last used row in a range.
On Error GoTo blankSheetError
    'Identify next blank row
    LastRow = rng.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row + Offset

    'On Error GoTo 0 'not really needed
    Exit Function

blankSheetError:
    LastRow = 1 'Will produce error if blank sheet so default to row 1 as cannot have row 0
    Resume Next

End Function
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: vba help

Postby mak » Wed Jul 26, 2017 3:28 pm

i tried the following but it does not working. where is the fault?
--------------------------------------------------------------------------------------------------------
Code: Select all
If GetTicks(Range("F5").Value, Range("H5").Value) >= 80 Then
    Cells("Q5").Value = "LAY"
End If


Also is there any easiest way to address all rows? (f6 , f7 ect)
I have seen some code like
for fx 5 to 40
if cells(fx,8)>=80 then
cells(fx,17).value="LAY"

something like this I am trying to get
mak
 
Posts: 1086
Joined: Tue Jun 30, 2009 8:17 am

Re: vba help

Postby Captain Sensible » Wed Jul 26, 2017 3:41 pm

I've no idea where you get Cells("Q5").Value from try using Range("Q5").Value

Once you get the last row of data using
Code: Select all
rowFindLast = LastRow(.Range("A:P"), 0)


you can now loop thru the data as we know the start and end boundaries and just use For that lets us loop like so

Code: Select all
        For i = 5 To rowFindLast
       Range("S" & i ).Value="Something or other"
        Next i
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: vba help

Postby Captain Sensible » Wed Jul 26, 2017 3:44 pm

Might be an idea for you to look up how to reference cells within excel before going further as you seem to have mixed up various ways of accessing them , have a look at https://msdn.microsoft.com/en-us/librar ... =office.11).aspx it'll explain how to refernce cells using index numbers and ranges. You'll need to understand those if you'll be using arrays
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: vba help

Postby Captain Sensible » Wed Jul 26, 2017 3:46 pm

User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: vba help

Postby alrodopial » Wed Jul 26, 2017 4:37 pm

Geia sou Maki

In order to be helped you have to be more accurate in your descriptions
The below does not help
Do you get nothing? Do you get error? What kind of error? etc

mak wrote:i tried the following but it does not working. where is the fault?
alrodopial
 
Posts: 1353
Joined: Wed Dec 06, 2006 9:59 pm

Re: vba help

Postby mak » Wed Jul 26, 2017 4:57 pm

i have the code below this line. captain answered the question.

now I am having a new one :D
I thought to start with the following
When I test it (manual change cell h5, so condition are met) it works ok but the excel freezes :?
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

If Range("F5").Value >= 5 And Range("F5").Value <= 40 Then '( I don't why or how to continue with AND)

If GetTicks(Range("F5").Value, Range("H5").Value) >= 15 Then
Range("R5").Value = minusTicks(Range("h5"), 1)
Range("Q5").Value = "LAY"

End If
End If

End Sub
mak
 
Posts: 1086
Joined: Tue Jun 30, 2009 8:17 am

Re: vba help

Postby Captain Sensible » Wed Jul 26, 2017 5:02 pm

It's because you haven't turned events off during the code, so the code is simply going into a continuous loop, you change a cell that kicks off the macro that changes a cell that kicks off the macro etc etc

change it to the following and it turns off the events until the code has fully run

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Range("F5").Value >= 5 And Range("F5").Value <= 40 Then '( I don't why or how to continue with AND)

If GetTicks(Range("F5").Value, Range("H5").Value) >= 15 Then
Range("R5").Value = minusTicks(Range("h5"), 1)
Range("Q5").Value = "LAY"

End If
Application.EnableEvents = True
End If
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: vba help

Postby Captain Sensible » Wed Jul 26, 2017 5:08 pm

Here's some code so you can add all the bits together and use arrays, remember to add the rowFindLast function in the 3rd post to your module otherwie it won't work. It'll show you how to grab an array and use your getTicks function

Code: Select all
    Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rowFindLast As Long
    Dim readArray() As Variant
    Dim i As Long
 Application.EnableEvents = True
        If Target.Columns.Count <> 16 Then Exit Sub
        Application.EnableEvents = False

        With Target.Parent
               
            rowFindLast = LastRow(.Range("A:S"), 0) 'get the last row
            readArray = .Range("A1:S" & rowFindLast).Value 'put our data into an array
            .Range("S1").Value = 6
           
            For i = 5 To UBound(readArray, 1) 'using 5 rather than LBound(readArray, 1) because we only want price data now
               
               
            If getTicks(CCur(readArray(i, 6)), CCur(readArray(i, 8))) > 1 Then
            readArray(i, 19) = "over 1 tick"
            readArray(i, 18) = getTicks(CCur(readArray(i, 6)), CCur(readArray(i, 8))) 'display ticks
            Else
            readArray(i, 19) = "under 1 tick"
            readArray(i, 18) = "" ' we clear cell incase it still holds data from previous refresh
            End If
   
           
            Next i
           
           .Range("A1:S" & rowFindLast).Value = readArray 'dump our amended arrayback to the sheet
       
        End With
        Application.EnableEvents = True
    End Sub
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: vba help

Postby mak » Wed Jul 26, 2017 5:26 pm

Captain
what CCur is?
mak
 
Posts: 1086
Joined: Tue Jun 30, 2009 8:17 am

Re: vba help

Postby mak » Wed Jul 26, 2017 5:29 pm

and why "S" range? Just to understand

For the time being I understand just half of the code you post it. and If I test it it does not alter anything in the excel
mak
 
Posts: 1086
Joined: Tue Jun 30, 2009 8:17 am

Re: vba help

Postby Captain Sensible » Wed Jul 26, 2017 5:36 pm

mak wrote:Captain
what CCur is?


CCur changes the array value to a currency type because your getTicks function expects the value to be currency not variant

I grabbed the data to the "S" range as that covers your trigger cells.

I have no idea why it's not working for you as I'm not there. Do you have it linked to Betting Assistant ? If not comment out the line


Code: Select all
If Target.Columns.Count <> 16 Then Exit Sub


and you'll be able to play with things manually.

Do you have the functions in your modules?

Are events turned off on your instance of excel due to an error?
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: vba help

Postby Captain Sensible » Wed Jul 26, 2017 5:41 pm

Try using this code as it will show you the state of your events to confirm if the worksheet change rouitine is working in cell S1, it'll show as TRUE or FALSE. It should be displaying the ticks in columns R and S if working

Code: Select all
  Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rowFindLast As Long
    Dim readArray() As Variant
    Dim i As Long

        If Target.Columns.Count <> 16 Then Exit Sub
        Application.EnableEvents = False
         Range("S1").Value = Application.EnableEvents
        With Target.Parent
               
            rowFindLast = LastRow(.Range("A:S"), 0) 'get the last row
            readArray = .Range("A1:S" & rowFindLast).Value 'put our data into an array
         
           
            For i = 5 To UBound(readArray, 1) 'using 5 rather than LBound(readArray, 1) because we only want price data now
               
               
            If getTicks(CCur(readArray(i, 6)), CCur(readArray(i, 8))) > 1 Then
            readArray(i, 19) = "over 1 tick"
            readArray(i, 18) = getTicks(CCur(readArray(i, 6)), CCur(readArray(i, 8))) 'display ticks
            Else
            readArray(i, 19) = " 1 tick"
            readArray(i, 18) = "" ' we clear cell incase it still holds data from previous refresh
            End If
   
           
            Next i
           
           .Range("A1:S" & rowFindLast).Value = readArray 'dump our amended arrayback to the sheet
       
        End With
        Application.EnableEvents = True
       Range("S1").Value = Application.EnableEvents
    End Sub
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Next

Return to Help

Who is online

Users browsing this forum: No registered users and 28 guests

Sports betting software from Gruss Software


The strength of Gruss Software is that it’s been designed by one of you, a frustrated sports punter, and then developed by listening to dozens of like-minded enthusiasts.

Gruss is owned and run by brothers Gary and Mark Russell. Gary discovered Betfair in 2004 and soon realised that using bespoke software to place bets was much more efficient than merely placing them through the website.

Gary built his own software and then enhanced its features after trialling it through other Betfair users and reacting to their improvement ideas, something that still happens today.

He started making a small monthly charge so he could work on it full-time and then recruited Mark to help develop the products and Gruss Software was born.

We think it’s the best of its kind and so do a lot of our customers. But you can never stand still in this game and we’ll continue to improve the software if any more great ideas emerge.

cron