Excel VBA - programmeren in Excel

Doel
Inzicht te geven in de structuur en onderdelen van VBA binnen Excel. 
Vaardigheden te ontwikkelen in het opzetten van procedures in VBA binnen Excel.

Inhoud

Doelgroep
Iedereen die wil beginnen met programmeren met VBA, zowel ervaren eindgebruikers als bijvoorbeeld helpdesk medewerkers. De cursus VBA is ook geschikt voor personen die inzicht willen krijgen in de opzet van VBA projecten, bijvoorbeeld projectmanagers.

Voorkennis
Een gedegen kennis van en vaardigheid met Excel gebaseerd op een meerjarige ervaring. Programmeerervaring is niet strikt noodzakelijk.


Cursusdata
31 mei, 6 en 13 juni 2012.

Kosten en inschrijving
De kosten van de driedaagse cursus bedragen € 1125. Dit komt overeen met € 375 all in. De cursusprijs is vrijgesteld van BTW (BTW-tarief 0%) en , deelnamecertificaat, koffie / thee en lunch en excl. naslagwerk. 

In-company training
De cursus VBA kan tevens als in-company training (op een locatie van uw keuze) worden gegeven. In overleg wordt het aantal deelnemers van uw organisatie bepaald en past Tridata de cursus aan uw specifieke wensen. Neemt u voor een maatwerkofferte contact op met Tridata.

Locatie
Laan Copes van Cattenburch 62, Den Haag 

Aanmelden | Brochure

-------------------------------------------------------

Voorbeelden

-------------------------------------------------------

Voorbeeld  Do...Loop Statement

Repeats a block of statements while a Boolean condition is True or until the condition becomes True.

Copy
 Do { While | Until } condition
    [ statements ]
    [ Exit Do ]
    [ statements ]
Loop
-or-
Do
    [ statements ]
    [ Exit Do ]
    [ statements ]
Loop { While | Until } condition


-------------------------------------------------------

In the following example, the statements in the loop continue to run until the index variable is greater than 10. The Until clause is at the end of the loop.

VBCopy

Dim index As Integer = 0
Do
    Debug.Write(index.ToString & " ")
    index += 1
Loop Until index > 10

Debug.WriteLine("")
' Output: 0 1 2 3 4 5 6 7 8 9 10


The following example uses a While clause instead of an Until clause, and condition is tested at the start of the loop instead of at the end.

VBCopy

Dim index As Integer = 0
Do While index <= 10
    Debug.Write(index.ToString & " ")
    index += 1
Loop

Debug.WriteLine("")
' Output: 0 1 2 3 4 5 6 7 8 9 10


In the following example, condition stops the loop when the index variable is greater than 100. The If statement in the loop, however, causes the Exit Do statement to stop the loop when the index variable is greater than 10.

VBCopy

Dim index As Integer = 0
Do While index <= 100
    If index > 10 Then
        Exit Do
    End If

    Debug.Write(index.ToString & " ")
    index += 1
Loop

Debug.WriteLine("")
' Output: 0 1 2 3 4 5 6 7 8 9 10

The following example reads all lines in a text file. The OpenText method opens the file and returns a StreamReader that reads the characters. In the Do...Loop condition, the Peek method of the StreamReader determines whether there are any additional characters.

VBCopy

Private Sub ShowText(ByVal textFilePath As String)
    If System.IO.File.Exists(textFilePath) = False Then
        Debug.WriteLine("File Not Found: " & textFilePath)
    Else
        Dim sr As System.IO.StreamReader = System.IO.File.OpenText(textFilePath)

        Do While sr.Peek() >= 0
            Debug.WriteLine(sr.ReadLine())
        Loop

        sr.Close()
    End If
End Sub


If...Then...Else Statement

' Multiple-line syntax:
If condition [ Then ]
    [ statements ]
[ ElseIf elseifcondition [ Then ]
    [ elseifstatements ] ]
[ Else
    [ elsestatements ] ]
End If

' Single-line syntax:
If condition Then [ statements ] [ Else [ elsestatements ] ]


The following example illustrates the use of the multiple-line syntax of the If...Then...Else statement.

VBCopy

        Dim count As Integer = 0
        Dim message As String

        If count = 0 Then
            message = "There are no items."
        ElseIf count = 1 Then
            message = "There is 1 item."
        Else
            message = "There are " & count & " items."
        End If

The following example contains nested If...Then...Else statements.

VBCopy

Private Function CheckIfTime() As Boolean
    ' Determine the current day of week and hour of day.
    Dim dayW As DayOfWeek = DateTime.Now.DayOfWeek
    Dim hour As Integer = DateTime.Now.Hour

    ' Return True if Wednesday from 2 to 4 P.M.,
    ' or if Thursday from noon to 1 P.M.
    If dayW = DayOfWeek.Wednesday Then
        If hour = 14 Or hour = 15 Then
            Return True
        Else
            Return False
        End If
    ElseIf dayW = DayOfWeek.Thursday Then
        If hour = 12 Then
            Return True
        Else
            Return False
        End If
    Else
        Return False
    End If
End Function