Een tijd terug heb ik in Excel-VBA een tooltje gemaakt die het DSS resultaat stroom-diagram van de KNLTB volgt mbt rating bij het dubbelspel. Het was een goede oefening om meer gevoel en ervaring met VBA "programmeren" in Excel te krijgen.
Deze code zorgt ervoor dat je alvast kijken wat de impact is op jouw rating als je wint of verliest in een dubbelspel.
Om er ook mee te spelen, kun je de Excel met macro (new window) downloaden.
Maar als je liever geen macro-excel opent van een onbekende bron ;-), hieronder zowel de code alsmede de sheet lay-out:
Sub Score() Dim wsDSS As Worksheet Set wsDSS = ActiveWorkbook.Worksheets("DSS") 'uitvoer vars Dim T1score As Double Dim T2score As Double Dim U1score As Double Dim U2score As Double 'invoer vars Dim T1rating As Double Dim T2rating As Double Dim U1rating As Double Dim U2rating As Double Dim resultaat As String 'inlezen vars resultaat = wsDSS.Range("B6").Value T1rating = wsDSS.Range("B3").Value T2rating = wsDSS.Range("B4").Value U1rating = wsDSS.Range("D3").Value U2rating = wsDSS.Range("D4").Value 'bereken vars Dim TCombi As Double Dim TCombiGem As Double Dim UCombi As Double Dim UCombiGem As Double Dim TVers As Double Dim UVers As Double Dim CombiVers As Double 'Bereken benodigde vars TCombi = T1rating + T2rating UCombi = U1rating + U2rating TCombiGem = TCombi / 2 UCombiGem = UCombi / 2 TVers = Abs(T1rating - T2rating) UVers = Abs(U1rating - U2rating) CombiVers = Abs(TCombi - UCombi) 'check vars Dim TVers25 As Boolean Dim UVers25 As Boolean Dim TVers1525 As Boolean Dim UVers1525 As Boolean Dim CombiVers15 As Boolean Dim Vers1Beide As Boolean 'checks TVers25 = TVers < 2.5 UVers25 = UVers < 2.5 TVers1525 = 1.5 <= TVers And TVers <= 2.5 UVers1525 = 1.5 <= UVers And UVers <= 2.5 CombiVers15 = CombiVers <= 1.5 Vers1Beide = UVers < 1 And TVers < 1 'beslisboom If Not TVers25 Or Not UVers25 Or resultaat = "wo" Then 'meer dan 2.5 verschil of Wo 'geen resultaat T1score = 0 T2score = 0 U1score = 0 U2score = 0 ElseIf TVers1525 Or UVers1525 Then 'Een van beide teams tussen 1.5 en 2.5 verschil If CombiVers15 Then 'verschil tussen team combi's 1,5 of kleiner If resultaat = "Thuis" Then T1score = T1rating - 0.5 T2score = T2rating - 0.5 U1score = U1rating + 0.5 U2score = U2rating + 0.5 ElseIf resultaat = "Uit" Then T1score = T1rating + 0.5 T2score = T2rating + 0.5 U1score = U1rating - 0.5 U2score = U2rating - 0.5 End If Else 'verschil tussen team combi's groter dan 1,5 If TCombi > UCombi And resultaat = "Thuis" Then T1score = T1rating - 1 T2score = T2rating - 1 U1score = U1rating + 1 U2score = U2rating + 1 ElseIf UCombi > TCombi And resultaat = "Uit" Then T1score = T1rating + 1 T2score = T2rating + 1 U1score = U1rating - 1 U2score = U2rating - 1 ElseIf TCombi < UCombi And resultaat = "Thuis" Then 'geen resultaat T1score = 0 T2score = 0 U1score = 0 U2score = 0 ElseIf UCombi < TCombi And resultaat = "Uit" Then 'geen resultaat T1score = 0 T2score = 0 U1score = 0 U2score = 0 End If End If ElseIf CombiVers15 Then 'verschil tussen combinaties <= 1.5 If Vers1Beide And resultaat = "Thuis" Then T1score = UCombiGem - 1 T2score = UCombiGem - 1 U1score = TCombiGem + 1 U2score = TCombiGem + 1 ElseIf Vers1Beide And resultaat = "Uit" Then T1score = UCombiGem + 1 T2score = UCombiGem + 1 U1score = TCombiGem - 1 U2score = TCombiGem - 1 ElseIf Not Vers1Beide And resultaat = "Thuis" Then T1score = T1rating - 0.5 T2score = T2rating - 0.5 U1score = U1rating + 0.5 U2score = U2rating + 0.5 ElseIf Not Vers1Beide And resultaat = "Uit" Then T1score = T1rating + 0.5 T2score = T2rating + 0.5 U1score = U1rating - 0.5 U2score = U2rating - 0.5 End If Else 'verschil tussen combinaties > 1.5 If resultaat = "Thuis" And UCombi < TCombi Then T1score = UCombiGem - 1 T2score = UCombiGem - 1 U1score = TCombiGem + 1 U2score = TCombiGem + 1 ElseIf resultaat = "Uit" And UCombi > TCombi Then T1score = UCombiGem + 1 T2score = UCombiGem + 1 U1score = TCombiGem - 1 U2score = TCombiGem - 1 Else 'geen resultaat T1score = 0 T2score = 0 U1score = 0 U2score = 0 End If End If 'Schrijf naar sheet wsDSS.Range("F3").Value = TCombi wsDSS.Range("G3").Value = UCombi wsDSS.Range("F4").Value = TVers wsDSS.Range("G4").Value = UVers wsDSS.Range("F5").Value = TVers25 wsDSS.Range("G5").Value = UVers25 wsDSS.Range("F6").Value = TVers1525 wsDSS.Range("G6").Value = UVers1525 wsDSS.Range("F7").Value = CombiVers wsDSS.Range("F8").Value = CombiVers15 wsDSS.Range("F9").Value = Vers1Beide wsDSS.Range("B11").Value = T1score wsDSS.Range("B12").Value = T2score wsDSS.Range("D11").Value = U1score wsDSS.Range("D12").Value = U2score End Sub
- 20 views
