Sports Fixtures Generator - A Real-World VBA Application

Рет қаралды 11,639

Күн бұрын

Sports Fixtures Generator - A Real-World VBA Application
In this video, I create a sports fixture generator using Excel VBA code. This application takes a list of teams and generates fixtures
for a league. It works for any number of teams and you can set the number of times the teams meet.
The video covers professional quality VBA techniques which you can use in your own applications.
#excelvba #excelvbacopy #SportsFixtureGenerator #RealWorldVBA
SUBSCRIBE TO THE CHANNEL: bit.ly/36hpTCY
DOWNLOAD THE SOURCE CODE FOR THIS VIDEO: bit.ly/3ANdrfg
(Note: if you have issues downloading the code then make sure to turn off any pop up blockers that you may have running)
(Blog Post)Excel VBA Copy - The Complete Guide to Copying Data: excelmacromastery.com/excel-v...
(Blog Post)VBA Advanced Filter: A Complete Guide: excelmacromastery.com/vba-adv...
(Blog Post)Excel VBA Array - The Complete Guide: excelmacromastery.com/excel-v...
KZfaq Video: How to use ADO and VBA to Read from Worksheets: • How to use ADO and VBA...
KZfaq PlayList: Excel VBA Copying and Filtering Data: • Excel VBA Copying and ...
KZfaq PlayList: Excel VBA Arrays: • Excel VBA Copying and ...
Related Training
The Excel VBA Handbook Course(TheExcelVBAHandbook.com)
Webinar Archives - 60+ Hours of VBA training(excelmacromastery.com/excel-v...)
Free Excel VBA Resources
Excel VBA Articles (excelmacromastery.com/vba-art...)
Useful VBA Shortcut Keys
========================
Debugging:
Compile the code: Alt + D + L OR Alt + D + Enter
Run the code from the current sub: F5
Step into the code line by line: F8
Add a breakpoint to pause the code: F9(or click left margin)
Windows:
View the Immediate Window: Ctrl + G
View the Watch Window: Alt + V + H
View the Properties Window: F4
Switch between Excel and the VBA Editor: Alt + F11
View the Project Explorer Window: Ctrl + R
Writing Code:
Search keyword under cursor: Ctrl + F3
Search the word last searched for: F3
Auto complete word: Ctrl + Space
Get the definition of the item under the cursor: Shift + F2
Go to the last cursor position: Ctrl + Shift + F2
Get the current region on a worksheet: Ctrl + Shift + 8(or Ctrl + *)
To move lines of code to the right(Indent): Tab
To move lines of code to the left(Outdent): Shift + Tab
Delete a Line: Ctrl + Y(note: this clears the clipboard)
Table of Contents:
00:00 - Introduction
00:42 - Application Demo
02:39 - Adding the Click events
05:53 - How to generate fixtures
13:29 - Creating Starting array
15:45 - Create fixtures
17:12 - Rotate Teams
19:12 - Copy the current round
23:35 - Print out the fixtures

Пікірлер: 39

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

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

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

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

@Tesla72PL Жыл бұрын

Thank you Paul, it's amazing how many new things you can learn.

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

@vbaclasses3553 Жыл бұрын

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

@Excelmacromastery Жыл бұрын

You're welcome

@KM-co5mx Жыл бұрын

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

@Excelmacromastery Жыл бұрын

Thanks

@vijaysahal4556 Жыл бұрын

Wow you are always great sir

@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 kzfaq.info/sun/PL7ScsebMq5uU_H6L3yHqedYMmnxUqQfa7

@karthikeyanb6897 Жыл бұрын

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

@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

@scottjones1474 5 ай бұрын

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

@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

@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 KZfaq PlayList: Excel VBA Copying and Filtering Data: kzfaq.info/sun/PL7... KZfaq PlayList: Excel VBA Arrays: kzfaq.info/sun/PL7...

@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 KZfaq :(

@Excelmacromastery Жыл бұрын

Thanks for the feedback Nader.

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