can anyone help with this spreadsheet. i posted before with another spreadsheet and after advice i got from everyone on here i got it cut down to what it is now only the bare essentials, it now updates at 8 times per second but once i add my formulas it slows right down to only once per second sometimes slower and thats without my charts. my formulas compared to others i have seen on here are pretty simple and my formulas only use HS4-JU1300 looking at the top 3 horses. any help would be much appreciated or point me to someone who can help or where i could hire someone to do the work. the person that made it cant make it any faster either. my internet speed is 310Mbps, i have a i7-6700 (3.4GHz) and the speed doesn't change if i am using 1 or 8 cores so i know its not my computer.
cheers in advance
https://www.dropbox.com/s/julvmye6joxqd ... .xlsm?dl=0
Can anyone help me with my speed problem?
Hi Michael
This part won't necessarly speed it up but it will help to make the coding easier.
1. The same worksheet calculate code doesn't need to go in every sheet
If you click on "this workbook" and select "Private Sub Workbook_SheetCalculate(ByVal Sh As Object)"
the Sh returns the name of the sheet being calculated at the time, so you could use something along the lines of
Similarly, all those modules ( one for each sheet) aren't necessary, you can use one and access it via the above by passing the sheet name as shown above, so instead of "Call Main1", "CallMain2", "CallMain3" etc, etc, you can just use
This may help in speeding it up a bit.
As you are looking at each sheet in turn, you don't need the whole workbook to calculate all the time.
You can turn it off and get the sheet you are processing to calculate independently. (Might have to move this command to suit)
Can't quite see the reason for stepping through the code:
If it's just a case of converting a range of formulas to values ? something along these lines would be quicker (got the range just by adding 49 to 137). Change to suit.
NOTE: not quite sure where is getting its retained value from? That would need to be changed.
Finally
If you only trade pre-off you could add the proviso to only process the sheet if not in-play
If you only trade ## seconds before the off you could add a countdown timer and only process if within the allocated time.
Once a sheet has been finished with, clear any data that won't be used anymore.
This part won't necessarly speed it up but it will help to make the coding easier.
1. The same worksheet calculate code doesn't need to go in every sheet
If you click on "this workbook" and select "Private Sub Workbook_SheetCalculate(ByVal Sh As Object)"
the Sh returns the name of the sheet being calculated at the time, so you could use something along the lines of
Code: Select all
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
If Left(Sh.Name, 9) <> "Bet Angel" Then Exit Sub
Dim shtName As String
shtName = Sh.Name
'When traded volume changes
If Worksheets(shtName).Range("DD2").Value <> Worksheets(shtName).Range("DD3").Value Then
Worksheets(shtName).Range("DD3").Value = Worksheets(shtName).Range("DD2").Value
If Worksheets(shtName).Range("DD1").Value = 0 Then
Application.Calculation = xlCalculationManual
Call Main(shtName)
If Worksheets(shtName).Range("EF1").Value = "ON" Or Worksheets(shtName).Range("EF1").Value = "on" Then Call AdjustVerticalAxis
End If
End If
Application.Calculation = xlCalculationAutomatic
End Sub
Code: Select all
Call Main(shtName)
As you are looking at each sheet in turn, you don't need the whole workbook to calculate all the time.
You can turn it off and get the sheet you are processing to calculate independently. (Might have to move this command to suit)
Code: Select all
Sheets(shtName).Calculate
Code: Select all
For i = 0 To 49
Worksheets(shtName).Cells(5 + counter4, 137 + i).Value = Worksheets(shtName).Cells(3, 137 + i).Value
Next i
Code: Select all
With Worksheets(shtName)
.Range(.Cells(5 + counter4, 137), .Cells(5 + counter4, 186)) = .Range(.Cells(3, 137), .Cells(3, 186)).Value
End With
Code: Select all
counter4 = counter4 + 1
Finally
If you only trade pre-off you could add the proviso to only process the sheet if not in-play
If you only trade ## seconds before the off you could add a countdown timer and only process if within the allocated time.
Once a sheet has been finished with, clear any data that won't be used anymore.
You do not have the required permissions to view the files attached to this post.
-
- Posts: 20
- Joined: Fri Sep 02, 2016 3:08 pm
nigelk wrote: ↑Wed Jan 23, 2019 1:45 amHi Michael
This part won't necessarly speed it up but it will help to make the coding easier.
1. The same worksheet calculate code doesn't need to go in every sheet
If you click on "this workbook" and select "Private Sub Workbook_SheetCalculate(ByVal Sh As Object)"
the Sh returns the name of the sheet being calculated at the time, so you could use something along the lines of
Similarly, all those modules ( one for each sheet) aren't necessary, you can use one and access it via the above by passing the sheet name as shown above, so instead of "Call Main1", "CallMain2", "CallMain3" etc, etc, you can just useCode: Select all
Private Sub Workbook_SheetCalculate(ByVal Sh As Object) If Left(Sh.Name, 9) <> "Bet Angel" Then Exit Sub Dim shtName As String shtName = Sh.Name 'When traded volume changes If Worksheets(shtName).Range("DD2").Value <> Worksheets(shtName).Range("DD3").Value Then Worksheets(shtName).Range("DD3").Value = Worksheets(shtName).Range("DD2").Value If Worksheets(shtName).Range("DD1").Value = 0 Then Application.Calculation = xlCalculationManual Call Main(shtName) If Worksheets(shtName).Range("EF1").Value = "ON" Or Worksheets(shtName).Range("EF1").Value = "on" Then Call AdjustVerticalAxis End If End If Application.Calculation = xlCalculationAutomatic End Sub
This may help in speeding it up a bit.Code: Select all
Call Main(shtName)
As you are looking at each sheet in turn, you don't need the whole workbook to calculate all the time.
You can turn it off and get the sheet you are processing to calculate independently. (Might have to move this command to suit)
Can't quite see the reason for stepping through the code:Code: Select all
Sheets(shtName).Calculate
If it's just a case of converting a range of formulas to values ? something along these lines would be quicker (got the range just by adding 49 to 137). Change to suit.Code: Select all
For i = 0 To 49 Worksheets(shtName).Cells(5 + counter4, 137 + i).Value = Worksheets(shtName).Cells(3, 137 + i).Value Next i
NOTE: not quite sure whereCode: Select all
With Worksheets(shtName) .Range(.Cells(5 + counter4, 137), .Cells(5 + counter4, 186)) = .Range(.Cells(3, 137), .Cells(3, 186)).Value End With
is getting its retained value from? That would need to be changed.Code: Select all
counter4 = counter4 + 1
Finally
If you only trade pre-off you could add the proviso to only process the sheet if not in-play
If you only trade ## seconds before the off you could add a countdown timer and only process if within the allocated time.
Once a sheet has been finished with, clear any data that won't be used anymore.
thanks for your reply. i am completely clueless with vba i wouldn't even know where to start putting that code in. would you be up for giving me a price to amalgamate my spreadsheet with this new code?