I hope you enjoy this video and make sure to download the source code from the description below the video. Let me know what you think of the video in the comments below.
Thank you so much for this video! I have just begun my journey into VBA programming and this has really inspired me. The challenge I am having is adapting this knowledge to create a generator for knockout competitions that involves the group stage. Hopefully I can get it right!!! Thank you for pointing me in the right direction!
Excellent video. So much information here, i have to go through this video numerous times. You are the best VBA teacher i have come across online. No question that my VBA skill is from your videos.
The tutorial is good and it seems even more interesting for sports fans (like me). And that doesn't change from the comments below. What I would have preferred is the "structure" (the planning of the process) to be reversed ; start from what we have in mind, what we need to do and how we access it and materialize it. The mindset behind this tutorial is rather how you made it, which actually (sort of) skips the pattern. However, just like I mentioned, I too did enjoy it.
your project for trading simulator was great. We are still missing a project for a trading system. It's great and deserved. Thank you for your beautiful projects.
@@Excelmacromastery Thnanks Mr. Paul, we are still waiting for your awesome trading system. how to stream real time data of Forex or Stock in Excel from many brokers and how to manage and set multi orders types. how to close or edit the all orders or a part of the orders.
Omg, mind = blown!! Great work as always Paul, you helped me a lot in my (thus far short) vba developer career, lost for words! You are truly an inspiration!
This is another Great tutorial on encapsulation, Paul. Keep up the great work. I've adopted a great deal of your concepts in coding to my own project that greatly reduced the downtime it usually takes to complete/update. Thank you for sharing you knowledge on Excel VBA.
Hi Paul, excellent video. Please can you/someone recommend some videos on understanding how and why to use classes instead of modules and the passing through of variables between them?
love the vid's and this is a handy way to organise matches compared to the clunky way I was generating them. I prefer find & offset to find numbers or strings that users have reference to. I would have liked to see all of the code (calculations) instead of just an import otherwise thumbs up !
You do a great job, plz continue to make more videos. But i hate variable i, j, ... in For loop, please name it (meetingNumber for Example). Also i prefered to have Named range if possible instead of constant. And i understand it's for the example, but i try to keep the code in another wbk. so that the code could be use for different wbk. And i can update the code for user easily :)
For actual use this is fundamentally flawed. I play Sports Replay games. For instance, I used it to create fixtures for the 1971-72 English 1st Division season (22 teams). The code does create Home and Away Games for every team but...(and it's a BIG but), it ignores the "Real World" where teams as much as possible, play alternate fixtures Home then Away, then Home etc. So the problem is your code ignores this completely and e.g. Arsenal play their first 21 of their games at Home consecutively and then all their 21 Away games in the next half of the season. With Chelsea it's about 18 consecutive matches at Home. This doesn't and wouldn't happen in real-life for a myriad of reasons. Too many to list here. These days, teams do sometimes play a few games consecutively at Home or Away but not months worth of fixtures Home or Away in a row. Wish I'd found out before building an entire Excel Workbook with fixtures created from this code. Not so much Excel Mastery as mystery why the uploader didn't take real life into account. This is code I discovered years ago. It works for any number of teams (just change the value of N). All that's needed then is to substitute the numbers eg. 1 to 20 with team names. Sub all_play_all() Dim c() As Variant, u() As Variant, flg As Byte Dim i As Long, j As Long, n As Long n = 20 If n Mod 2 = 1 Then n = n + 1: flg = 1 ReDim c(1 To n, 1 To n / 2 + 1), u(1 To n) For i = 1 To n - 1 If flg = 1 Then u(n) = "bye" Else u(n) = n For j = 1 To n - 1 u(j) = i + j - 1 If u(j) > n - 1 Then u(j) = u(j) - n + 1 Next j For j = 1 To n / 2 c(i, j + 1) = u(j) & " " & u(n - j + 1) Next j c(i, 1) = "Week " & i Next i Range("A1").Resize(n, n / 2 + 1) = c End Sub
With the code you supplied the last team matches are all away. if you want alternate fixtures with the sports fixtures code simply read through the full fixtures and swap every second home fixture with it's corresponding away fixture.
Really enjoying these videos and the referenced material. Thank you for creating, posting and sharing. FYI Two playlists are in the "More" section, but they are the same RUclips PlayList: Excel VBA Copying and Filtering Data: ruclips.net/p/PL7... RUclips PlayList: Excel VBA Arrays: ruclips.net/p/PL7...
I understand you are demonstrating object oriented VBA. To clear the range of fixtures, your code goes through these stages: Public Sub ClearFixtures()
With New clsFixtureWriter .SetRange GetStartRange() .Clear RGB(255, 242, 204) End With End Sub Private Function GetStartRange() As Range Set GetStartRange = shFixtures.Range(FIXTURE_RANGE) End Function Public Sub SetRange(ByVal startRange As Range) Set m_startRange = startRange End Sub Then finally to do the actual clearing: Public Sub Clear(Optional backgroundColor As Long = 0) With m_startRange.CurrentRegion .Offset(1).Clear If backgroundColor 0 Then .Offset(2).Interior.color = backgroundColor End If End With
End Sub But it's much simpler instead to write: Public Sub ClearFixtures() shFixtures.Range(FIXTURE_RANGE).CurrentRegion.Offset(1, 0).Clear End Sub Can you explain the benefits of your way?
I hope you enjoy this video and make sure to download the source code from the description below the video. Let me know what you think of the video in the comments below.
Thank you so much for this video! I have just begun my journey into VBA programming and this has really inspired me. The challenge I am having is adapting this knowledge to create a generator for knockout competitions that involves the group stage. Hopefully I can get it right!!! Thank you for pointing me in the right direction!
Excellent video.
So much information here, i have to go through this video numerous times.
You are the best VBA teacher i have come across online.
No question that my VBA skill is from your videos.
Thanks
The tutorial is good and it seems even more interesting for sports fans (like me). And that doesn't change from the comments below.
What I would have preferred is the "structure" (the planning of the process) to be reversed ; start from what we have in mind, what we need to do and how we access it and materialize it. The mindset behind this tutorial is rather how you made it, which actually (sort of) skips the pattern.
However, just like I mentioned, I too did enjoy it.
your project for trading simulator was great.
We are still missing a project for a trading system.
It's great and deserved.
Thank you for your beautiful projects.
Thanks. Glad you like it.
@@Excelmacromastery Thnanks Mr. Paul, we are still waiting for your awesome trading system.
how to stream real time data of Forex or Stock in Excel from many brokers and how to manage and set multi orders types.
how to close or edit the all orders or a part of the orders.
Omg, mind = blown!! Great work as always Paul, you helped me a lot in my (thus far short) vba developer career, lost for words! You are truly an inspiration!
Great to hear Alan!
This is another Great tutorial on encapsulation, Paul. Keep up the great work. I've adopted a great deal of your concepts in coding to my own project that greatly reduced the downtime it usually takes to complete/update. Thank you for sharing you knowledge on Excel VBA.
Thanks Bobby. Glad it helped.
Excellent video and application, as always Paul. Thank you for sharing.
You're welcome
may God adds you many more years on earth.
I like the way you combined the passion of Football with the power of VBA!
Very Cool 😎
Thanks
Wow you are always great sir
Hi, This is great !!, i have a request, any chance of implementing 'point deductions ..?, i cannot figure this bit out :/ Thank you
Hi Paul, excellent video.
Please can you/someone recommend some videos on understanding how and why to use classes instead of modules and the passing through of variables between them?
Check out this playlist ruclips.net/p/PL7ScsebMq5uU_H6L3yHqedYMmnxUqQfa7
Can you post video about how to allocate work equally using excel formula or VBA?
love the vid's and this is a handy way to organise matches compared to the clunky way I was generating them. I prefer find & offset to find numbers or strings that users have reference to. I would have liked to see all of the code (calculations) instead of just an import otherwise thumbs up !
Thanks Jay
Thank you so much
You're welcome
You do a great job, plz continue to make more videos.
But i hate variable i, j, ... in For loop, please name it (meetingNumber for Example).
Also i prefered to have Named range if possible instead of constant.
And i understand it's for the example, but i try to keep the code in another wbk. so that the code could be use for different wbk. And i can update the code for user easily :)
and thank you for Ctrl + Shift + F2, i have forgotten this shortcut
Thanks Benjamin. i and j are standard in loops so changing them will make the code confusing. I do use proper naming for others though.
You have done great job but your code is really mixing and complicated to understand.
Hope you'll make it clear next time
I am not good at class modules and it is very difficult to understand it. It is not common to find videos on class modules on RUclips :(
Thanks for the feedback Nader.
For actual use this is fundamentally flawed. I play Sports Replay games. For instance, I used it to create fixtures for the 1971-72 English 1st Division season (22 teams). The code does create Home and Away Games for every team but...(and it's a BIG but), it ignores the "Real World" where teams as much as possible, play alternate fixtures Home then Away, then Home etc.
So the problem is your code ignores this completely and e.g. Arsenal play their first 21 of their games at Home consecutively and then all their 21 Away games in the next half of the season. With Chelsea it's about 18 consecutive matches at Home.
This doesn't and wouldn't happen in real-life for a myriad of reasons. Too many to list here.
These days, teams do sometimes play a few games consecutively at Home or Away but not months worth of fixtures Home or Away in a row.
Wish I'd found out before building an entire Excel Workbook with fixtures created from this code. Not so much Excel Mastery as mystery why the uploader didn't take real life into account.
This is code I discovered years ago. It works for any number of teams (just change the value of N).
All that's needed then is to substitute the numbers eg. 1 to 20 with team names.
Sub all_play_all()
Dim c() As Variant, u() As Variant, flg As Byte
Dim i As Long, j As Long, n As Long
n = 20
If n Mod 2 = 1 Then n = n + 1: flg = 1
ReDim c(1 To n, 1 To n / 2 + 1), u(1 To n)
For i = 1 To n - 1
If flg = 1 Then u(n) = "bye" Else u(n) = n
For j = 1 To n - 1
u(j) = i + j - 1
If u(j) > n - 1 Then u(j) = u(j) - n + 1
Next j
For j = 1 To n / 2
c(i, j + 1) = u(j) & " " & u(n - j + 1)
Next j
c(i, 1) = "Week " & i
Next i
Range("A1").Resize(n, n / 2 + 1) = c
End Sub
With the code you supplied the last team matches are all away.
if you want alternate fixtures with the sports fixtures code simply read through the full fixtures and swap every second home fixture with it's corresponding away fixture.
Really enjoying these videos and the referenced material. Thank you for creating, posting and sharing.
FYI
Two playlists are in the "More" section, but they are the same
RUclips PlayList: Excel VBA Copying and Filtering Data: ruclips.net/p/PL7...
RUclips PlayList: Excel VBA Arrays: ruclips.net/p/PL7...
Awesome.
Thanks
I understand you are demonstrating object oriented VBA.
To clear the range of fixtures, your code goes through these stages:
Public Sub ClearFixtures()
With New clsFixtureWriter
.SetRange GetStartRange()
.Clear RGB(255, 242, 204)
End With
End Sub
Private Function GetStartRange() As Range
Set GetStartRange = shFixtures.Range(FIXTURE_RANGE)
End Function
Public Sub SetRange(ByVal startRange As Range)
Set m_startRange = startRange
End Sub
Then finally to do the actual clearing:
Public Sub Clear(Optional backgroundColor As Long = 0)
With m_startRange.CurrentRegion
.Offset(1).Clear
If backgroundColor 0 Then
.Offset(2).Interior.color = backgroundColor
End If
End With
End Sub
But it's much simpler instead to write:
Public Sub ClearFixtures()
shFixtures.Range(FIXTURE_RANGE).CurrentRegion.Offset(1, 0).Clear
End Sub
Can you explain the benefits of your way?
𝕡𝕣𝕠𝕞𝕠𝕤𝕞 💦