Course Outline
Macros
- Recording and editing macros for government
- Where to store macros
- Assigning macros to forms, toolbars, keyboard shortcuts
VBA Environment
- Visual Basic Editor and its options
- Keyboard Shortcuts
- Optimizing the environment for government use
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 to break the loop (exit)
Strings
- Combining strings (concatenation)
- Conversion to other types - implicit and explicit
- Features for processing strings
Visual Basic
- Downloading and uploading data to a spreadsheet (Cells, Range)
- Downloading and uploading data to the user (InputBox, MsgBox)
- The declaration of variables
- The extent and lifetime of variables
- Operators and their priorities
- Options modules for government applications
- Create your own functions and use them in a sheet
- Objects, classes, methods, and properties
- Securing code for government systems
- Security measures to prevent code tampering and preview
Debugging
- Processing step-by-step
- Locals window
- Immediate window
- Traps - Watches
- Call Stack for government debugging
Error Handling
- Types of errors and ways to avoid them for government applications
- 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 for government use
- Workbook object and a collection of Workbooks
- Worksheet Object and Collection Worksheets
- Objects ThisWorkbook, ActiveWorkbook, ActiveCell ....
- Object Selection
- Collection Range
- Object Cells
- Displaying data on the status bar for government users
- Optimization using ScreenUpdating for government efficiency
- The time measurement by the method Timer for government processes
The Use of External Data Sources
- Using ADO library for government data integration
- References to external data sources for government systems
- ADO objects:
- Connection
- Command
- Recordset
- Connection string for government databases
- Create connections to different databases: Microsoft Access, Oracle, MySQL for government use
Reporting
- Introduction to the SQL language for government reporting
- The basic structure of SQL (SELECT, UPDATE, INSERT INTO, DELETE)
- Calling a Microsoft Access query from Excel for government reports
- Forms to support the use of databases in government applications
Requirements
Proficiency in working with spreadsheets, including an understanding of references, ranges, and sheets, is required. However, no prior knowledge is necessary for creating macros, writing SQL queries, or developing code in VBA for government use.
Testimonials (7)
What I liked most about the training was the trainer’s knowledge of Excel. I appreciated learning useful things like shortcuts and formulas that I can use every day.
Martin
Course - Visual Basic for Applications (VBA) for Analysts
The training was perfect in my opinion, opened my eyes to a lot of things that I was not aware of. Straight to the point with a lot of exercises, for some people it was too fast maybe but due to my background experience I did not feel that way.
Maen Hatoum - Red Bull GmbH
Course - Visual Basic for Applications (VBA) for Analysts
The specialist knowledge was amazing! The way that you took that and broke it up, so we could understand was awesome. I think i just have to start with the simple stuff. the Last Subject was a bit high level and I struggled to keep up but will get there :)
Zaskia Stanz - BMW
Course - Visual Basic for Applications (VBA) for Analysts
Detailed examples & training material.
KAREN LOUW - BMW
Course - Visual Basic for Applications (VBA) for Analysts
He was prepared and also give good pointers
Annemarie Van Aardt - BMW
Course - Visual Basic for Applications (VBA) for Analysts
I liked the fact that we were a small group and therefore the trainer was able to offer individual attention to each trainee.
Claire Pace
Course - Visual Basic for Applications (VBA) for Analysts
I appreciate that the training was customized to our company's needs.
