Excel - Create your own functions with VBA

Wanneer de functies van Excel niet meer voldoende functionaliteit geven kan je met macro’s eigen functies gaan definiëren. In versie 5 of 95 moet je hiervoor een nieuw moduleblad aanmaken. In Excel 97, 2000, XP of 2003 ga je naar Extra >> Macro’s >> Visual Basic Editor. Nu moet je nog een nieuwe module aanmaken met Invoegen >> Module. Je kan nu beginnen met het schrijven van kleine Visual Basic for Applications-programmetjes.

Meestgebruikte datatypes

Retourwaarde

De retourwaarde van een functie kan slechts 1 keer in de functie toegekend worden. Dit gebeurt meestal als laatste actie. Achter de functienaam, tussen haakjes, staat de meegegeven parameter.

Voorbeeld 1

Function BTW(Bedrag As Variant) As Variant
  BTW = Bedrag * 0.21
End Function

Declareren van variabelen (Dim)

Dim varnaam As type Declareert nieuwe variabelen en wijst geheugenruimte toe. Type is het gekozen datatype.

Voorbeeld 2

Function Fak(Getal As Integer) As Integer
  Dim I, J As Integer
  J = 1
  For I = 2 To Getal
    J = J * I
  Next I
  Fak = J
End Function

Voorbeeld 3

Function Winst(Bedrag As Variant) As Variant
  Dim Marge As Variant
  Dim Kosten As Variant
  Marge = Bedrag * 0.3
  Select Case Bedrag
    Case Is <= 50
      Kosten = Bedrag * 0.12
    Case Is <= 100
      Kosten = Bedrag * 0.09
    Case Is <= 500
      Kosten = Bedrag * 0.07
    Case Else
      Kosten = Bedrag * 0.05
  End Select
  Winst = Bedrag + Marge + Kosten
End Function

VarType

VarType geeft een waarde die het subtype van een variabele aangeeft. Hiermee kan bv. gecontroleerd worden of er numerieke gegevens meegegeven worden.

Voorbeeld 4

Function Winst2(Bedrag As Variant) As Variant
  Dim Marge As Variant
  Dim Kosten As Variant
  If VarType(Bedrag) > 6 Then
    Winst = "Deze functie accepteert alleen getallen"
  Else
    Marge = Bedrag * 0.3
    Select Case Bedrag
      Case Is <= 50
        Kosten = Bedrag * 0.12
      Case Is <= 100
        Kosten = Bedrag * 0.09
      Case Is <
      = 500
        Kosten = Bedrag * 0.07
      Case Else
        Kosten = Bedrag * 0.05
    End Select
    Winst = Bedrag + Marge + Kosten
  End If
End Function

Bereik meegeven als parameter

Je kan ook een reeks/range van cellen meegeven aan een functie. Bv. cel A10 = Optellen(A1:B5) Deze functie gaat alle cellen uit de opgegeven range optellen. Een range moet van het gegevenstype Object zijn.

Voorbeeld 5

Function Optellen(Bereik As Object) As Single
  Dim Som As Single
  Dim Waarde As Variant
  Som = 0
  For Each Waarde In Bereik
    Som = Som + Waarde.Value
  Next Waarde
  Optellen = Som
End Function

Voorbeeld 6

Volgende functie gaat de tekst in de cellen vergelijken en naargelang de waarde bij de som een bepaalde getal bijtellen.

Function Optellen(Bereik As Object) As Single
  Dim Som As Single
  Dim Waarde As Variant
  Som = 0
  For Each Waarde In Bereik
    Select Case Waarde.Value
    Case Is = "A", "a"
      Som = Som + 10
    Case Is = "B", "b"
      Som = Som + 15
    Case Is = "C", "c"
      Som = Som + 20
    End Select
  Next Waarde
  Optellen = Som
End Function

Voorbeeld 7

Deze functie krijgt als parameters 2 ranges mee. Bij het tweede bereik moet in de eerste kolom de te zoeken waarden staan. In de tweede kolom staan de bijhorende getallen. Al naargelang de tekst in de cellen van de eerste range zal het bijhorende getal in de 2de range opgeteld worden bij de som. Dit werkt ongeveer zoals Vert.Zoeken.

Function Optellen(Bereik1 As Object, Bereik2 As Object) As Single
  Dim Som As Single
  Dim Waarde As Variant
  Dim i As Integer
  Som = 0
  For Each Waarde In Bereik1
    For i = 1 To Bereik2.Rows.Count
      If Waarde.Value = Bereik2.Cells(i, 1).Value Then
        Som = Som + Bereik2.Cells(i, 2).Value
      End If
    Next i
  Next Waarde
  Optellen = Som
End Function