Automating Tasks in MS Excel Using VBA 2400-SP-EXCEL-AP
The goal of this course is to automate everyday tasks in MS Excel using macros. The entire course is devoted to learning the Visual Basic for Applications (VBA) programming language.
Course topics:
✓ Structure of the VBE Editor
✓ Recording Macros
Developer tab, recording macros, relative and absolute references, running macros.
✓ Code Structure and Optimization
Structure of the VBA Editor, Excel file elements, code syntax, procedure syntax, reading, describing, and organizing code recorded by the recorder.
✓ Cell Range References
ActiveCell, Selection, Cells, Range.
✓ VBA objects
Properties and methods, classes, collections, hierarchy.
✓ VBA variables
Numeric, text, and logical variables; object variables.
✓ Functions and Formulas in VBA
Formulas executed in VBA, formulas executed in Excel, functions executed in VBA, worksheet functions executed in VBA, MsgBox and InputBox functions (dialog boxes).
✓ Loops
With … End With, For Each … Next.
✓ Conditional Statements
If … Then, Select Case.
✓ Commands
GoTo, Dir.
Course coordinators
Type of course
Mode
Learning outcomes
Participants learned how to automate tasks in MS Excel using the Visual Basic for Applications (VBA) programming language. Thanks to the course, they are now able to automatically, among other things: create consolidated databases, dynamically define the scope of procedures, edit and format ranges of cells and entire worksheets, prepare periodic sales reports, print specified areas, and export results to separate Excel and PDF files.
Assessment criteria
An independent assessment task to be completed by the participant upon course completion. A minimum score of 50% is required to pass.
Bibliography
Materials prepared by the lecturer and made available to participants on e-learning platform.