Thank you for sending your enquiry! One of our team members will contact you shortly.
Thank you for sending your booking! One of our team members will contact you shortly.
Course Outline
Macros
- Recording and editing macros
- Where to store macros.
- Assigning macros to forms, toolbars, keyboard shortcuts
VBA Environment
- Visual Basic Editor and its options
- Keyboard Shortcuts
- Optimizing the environment
Introduction to procedural programming
- Procedures: Function, Sub
- The data types
- The conditional statement If...Then....Elseif....Else....End If
- Instruction Case
- Loop while, until
- Loop for ... next
- Instructions break the loop(exit)
Strings
- Combining strings (concatenation)
- Conversion to other types - implicit and explicit
- Features processing strings
Visual Basic
- Download and upload data to a spreadsheet (Cells, Range)
- Download and upload data to the user (InputBox, MsgBox)
- The declaration of variables
- The extent and lifetime of variables
- Operators and their priorities
- Options modules
- Create your own functions and use them in a sheet
- Objects, classes, methods and properties
- Securing code
- Security code tampering and preview
Debugging
- Processing step
- Locals window
- Immediate window
- Traps - Watches
- Call Stack
Error handling
- Types of errors and ways to avoid
- Capturing and handling run-time errors
- Structures: On Error Resume Next, On Error GoTo label, On Error GoTo 0
Excel Object Model
- The Application object
- Workbook object and a collection of Workbooks
- Worksheet Object and Collection Worksheets
- Objects ThisWorkbook, ActiveWorkbook, ActiveCell ....
- Object Selection
- Collection Range
- Object Cells
- Display data on the statusbar
- Optimization using ScreenUpdating
- The time measurement by the method Timer
The use of external data sources
- Using ADO library
- References to external data sources
- ADO objects:
- Connection
- Command
- Recordset
- Connection string
- Create connections to different databases: Microsoft Access, Oracle, MySQL
Reporting
- Introduction to the SQL language The basic structure of SQL (SELECT, UPDATE, INSERT INTO, DELETE) Calling a Microsoft Access query from Excel Forms to support the use of databases
Requirements
Ability to work with a spreadsheet, basic knowledge (references, ranges, sheets, ...). No knowledge is required to create macros, SQL, or write code in VBA.
21 Hours
Testimonials (7)
I generally enjoyed the practical examples.
Marika Agius
Course - Visual Basic for Applications (VBA) for Analysts
I liked the step by step and hands on realistic data examples.
CARL MIFSUD
Course - Visual Basic for Applications (VBA) for Analysts
I mostly liked the hands-on practice and exercises.
Godwin Spiteri
Course - Visual Basic for Applications (VBA) for Analysts
Tamil has an excellent way of explaining things. He speaks clearly and will take the time to explain things individually when necessary.
Theresa Fenech
Course - Visual Basic for Applications (VBA) for Analysts
The trainer was very well prepared and encouraged interaction with the group. The exercises were very interesting and enabled us to utilize the knowledge on the subject in a practical context.
Tomasz Kolbuszewski
Course - Visual Basic for Applications (VBA) for Analysts
Trainer was calm and very patient to explain in a way that everyone understands, even those with basic knowledge of excel.
Sera Farrugia
Course - Visual Basic for Applications (VBA) for Analysts
I appreciated its applicability to work related issues.
