Overview
The course is designed for users of Excel for creating analyzes and reports.
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.
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