Picking your way through the VBA training minefield
We've split our introductory VBA training courses into Excel macros and Access Visual Basic, but our advanced VBA course also applies to Word, PowerPoint and other MS Office applications.
This page gives an idea of what you'll learn on our VBA courses, but you can see far more at our VBA resources page.
Excel VBA course
Our two-day introduction to VBA in Excel course will show you how to create basic macros, including selecting cells and declaring variables. Here's an example:
Sub RecordVote()
'the name of each superhero
Dim HeroName As String
'the rating assigned to them
Dim HeroRating As Long
'go to the votes sheet and get the value of the superhero, and their rating
Worksheets("Votes").Select
HeroName = Range("C4").Value
HeroRating = Range("C6").Value
'go to top of results
Worksheets("Results").Select
Range("B4").Select
'go to first blank cell
ActiveCell.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
'write variable values into this blank row
ActiveCell.Value = HeroName
ActiveCell.Offset(0, 1).Value = HeroRating
'copy formats from cell above (included here just to make macro complete)
Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 1)).Copy
ActiveCell.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End Sub
However, by day two you'll be writing some fairly ambitious macros, including looping over collections:
Sub CloseAllButCodeWorkbooks()
'a reference to each workbook in turn
Dim wb As Workbook
'for each of the open workbooks ...
For Each wb In Workbooks
'if it isn't this one containing the code (note that you can't directly compare two workbooks: instead, you must see if they have the same name)
If wb.Name <> ThisWorkbook.Name Then
'it's not the same - close it down, giving user a chance to save changes (this is the default)
wb.Close
End If
'now go on automatically to the next workbook
Next wb
End Sub
Access VBA course
Our two-day Access Visual Basic course will show you how to create VBA code within Access. Everything from simple event-handlers ...
Private Sub btnCourses_Click()
'open the courses form
DoCmd.OpenForm "frmCourse"
End Sub
... through linking to recordsets ...
Sub CheckLinks()
'this routine checks that all inserted links are valid
rsError.Open "tblCheckError", CurrentProject.Connection, _
adOpenDynamic, adLockOptimistic
Do Until rsError.EOF
rsError.Delete
rsError.MoveNext
Loop
Dim rsField As New ADODB.Recordset
rsField.Open "SELECT * FROM tblCheckField ORDER BY FieldName", _
CurrentProject.Connection
Do Until rsField.EOF
TableName = rsField("TableName")
FieldName = rsField("FieldName")
FieldIdName = rsField("FieldId")
Debug.Print "Checking table " & UCase(TableName)
rsField.MoveNext
Loop
rsField.Close
rsError.Close
End Sub
... to looping over collections and more complex code:
Public Function IsOpen(ThisForm) As Boolean
'Determine if form is open
'Loop variable
Dim f As Form
' Return value
IsOpen = False
'Loop through all open forms until we find one with given name
For Each f In Forms
If f.Name = ThisForm Then
IsOpen = True
Exit For
End If
Next f
End Function
Advanced VBA course
Our two-day advanced VBA course is aimed at people who already know Visual Basic for applications, and want to learn about referencing other applications, using file dialog boxes, creating classes, passing arguments by reference or value, recursive programming and the like. To give you a taste, here's the sort of macro you'll learn from the advanced VBA course:
Sub RequestLetter()
'try consuming your class!
Dim Guess As New clsLetterGuess
'keep guessing until letter guessed or too many goes
Guess.StartGuess
'display what was guessed
If Len(Guess.LetterGuessed) = 0 Then
MsgBox "No letter guessed"
Else
MsgBox "You guessed " & Guess.LetterGuessed
End If
'now get rid of the object (the garbage collector will
'do this anyway, so this line is not strictly
'necessary)
Set Guess = Nothing
End Sub
If you don't think this looks that complicated, bear in mind that clsLetterGuess is an object which we've created!
Further programming training
If your ambitions lie beyond MS Office, you might like to consider our other programming courses:
See our VBA page for more details on all things to do with Visual Basic for Applications training.