Serfaus djingthuis ontbijt rivier serfauspiste vecht

Old school VBA coding

DSS Dubbel beslisboom

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

Sheet layout

Theme by Danetsoft and Danang Probo Sayekti inspired by Maksimer