Certificate Authentication

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