Sports Fixtures Generator - A Real-World VBA Application

[@Excelmacromastery]

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.

[@hammeedabdo.82]

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]

Thanks. Glad you like it.

[@hammeedabdo.82]

@@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.

[@amonmutesi2481]

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!

[@scouter-xn6zi]

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.

[@lollandz]

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.

[@Excelmacromastery]

Thanks

[@Lykoskia]

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!

[@Excelmacromastery]

Great to hear Alan!

[@bobbyterupo3522]

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.

[@Excelmacromastery]

Thanks Bobby. Glad it helped.

[@bakwoludovic117]

may God adds you many more years on earth.

[@KM-co5mx]

I like the way you combined the passion of Football with the power of VBA! Very Cool 😎

[@Excelmacromastery]

Thanks

[@vbaclasses3553]

Excellent video and application, as always Paul. Thank you for sharing.

[@Excelmacromastery]

You're welcome

[@scottjones1474]

Hi, This is great !!, i have a request, any chance of implementing 'point deductions ..?, i cannot figure this bit out :/ Thank you

[@chrisolearymusic]

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?

[@Excelmacromastery]

Check out this playlist kzbin.info/aero/PL7ScsebMq5uU_H6L3yHqedYMmnxUqQfa7

[@karthikeyanb6897]

Can you post video about how to allocate work equally using excel formula or VBA?

[@vijaysahal4556]

Wow you are always great sir

[@jayknox-crichton3130]

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 !

[@Excelmacromastery]

Thanks Jay

[@jatinnandwani6678]

Thank you so much

[@Excelmacromastery]

You're welcome

[@BenjaminHouot]

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 :)

[@BenjaminHouot]

and thank you for Ctrl + Shift + F2, i have forgotten this shortcut

[@Excelmacromastery]

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.

[@cabbaaz]

You have done great job but your code is really mixing and complicated to understand. Hope you'll make it clear next time

[@nadermounir8228]

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 KZbin :(

[@Excelmacromastery]

Thanks for the feedback Nader.

[@jerryjones8836]

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 KZbin PlayList: Excel VBA Copying and Filtering Data: kzbin.info/aero/PL7... KZbin PlayList: Excel VBA Arrays: kzbin.info/aero/PL7...

[@mdcs1992]

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

[@Excelmacromastery]

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.

[@noviceprogrammer2011]

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?

[@freeliberalminds]

Awesome.

[@Excelmacromastery]

Thanks

[@dwaindean1730]

𝕡𝕣𝕠𝕞𝕠𝕤𝕞 💦