VBA Newbie

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
CallumPerry
Posts: 575
Joined: Wed Apr 19, 2017 5:12 pm
Location: Wolverhampton

Hi lads,

I’ve been using Bet Angel a short while now and would like to start using this forum a lot more often. Firstly, nice to meet you all. I wonder whether some of you could help me out with some VBA things i’m working on that I think would benefit the community?

Firstly, i’ve always wondered why people have more than one sheet i.e. a dashboard sheet and a data sheet? Why do you not just start your data flowing from row 100 or further down? How can you have couplets of sheets for say 30 races a day?

I’d like to make a code that has the Open, High, Low and Close numbers move down a row every 200 m/s or less (up to the max 20m/s if possible). Is there a way of making the timer do 1/50 of a second or is there a work around such as each time the market volume box changes the rows shift? Or another sensible work around of that sort with a variable that is guaranteed to change once every (insert chosen refresh rate here). I’d like to do this construct some simple candlesticks and stuff. I’m aware much more sophisticated charts are available but would rather do it in the spreadsheet so I can experiment with a few systems. Just to clarify, OHLC would be cells A100, B100, C100, D100 for example (underneath the standard dashboard area) and then shift to A101, B101, C101, D101 every refresh interval and the first row would show the new OHLC direct from BetFair.

Then I have another simple VBA code I am too stupid to work out. If a certain cell = or is <> a given number determined by a simple formula, then that cell stays as that number forever i.e. 10 minutes before a race (countdown cell) a horse is priced at 4.0 that cell stays as "4.0" forever, 9 minutes to go it is now "3.6" etc. Therefore if triggers and calculations are happening, the cell in question isn’t changing rapidly and further strategies can be deployed. Hope that made sense.

If anybody has something basic like this already set up, could type up some codes or point me in the right direction, I would massively appreciate it and hopefully one day soon I can get involved with some conversation and help out some users even more useless than me (if there are any).

Cheers lads,
Callum :)
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

why people have more than one sheet i.e. a dashboard sheet and a data sheet?
just a matter of personal preference really. Do you need to look at the data or a summary (chart?) of what it represents
is there a work around such as each time the market volume box changes the rows shift
look at the worksheet change events and target the cell/cells you are interested in (put a number in anywhere in row 3, enter something in B2 and you can see the original row moving down). This goes in the worksheet, not a module

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = ("$B$2") Then

Rows("3:3").Insert

End If

End Sub
I have another simple VBA code

you could use a 1 second timer, then say for example:

Code: Select all


If mySeconds=300 then sheets("sheet1").range("A2") =sheets("Sheet2").range("A2")

Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”