Excel and VBA Programming for Audit and Finance Professionals Training Course

Part I. Squeeze more from Excel

Overview of tools on the Data tab

• Access to external data - do you really need to visit the bank's website every day to get to know the current exchange rate CHF?
• Defining connections to external data (Access, Web, Text, XML, ...)
• Sorting multi-level - the rules and the proper sorting options
• Efficient Advanced filtering - how to create Strainers having access to the filter criteria
• Fast text-to-column
• Delete duplicate data
• Forcing input the correct data - how to ensure that data were specific format
• Simulation Analysis - how to prepare a professional presentation of possible scenarios
• Simulation Analysis - how to estimate the result of the formula
• Grouping and autokonspekty - how to roll up the rows and columns and show different levels of detail

PivotTable and PivotChart

• Calculated fields - how to add to the PivotTable field that is not on the sheet
• Computational elements in the table
• Grouping data and create professional-looking statements

Part II. Automation ie VBA.

Macros

• Recording and editing macros: Silence on the set - is recording
• Where to store macros - where best to write macros

Introduction to procedural programming - the necessary basis

• Sub and Function - how to invoke them and what they are
• Data Types - what variables are needed and whether it is worth it to declare
• The conditional statement If ... Then .... ElseIf .... Else .... End If
• Case statement and the accompanying trap
• Loop for ... next, loop ... each
• Loops for ... loop while, until
• Instructions loop break (exit)

Visual Basic in action

• Download and upload data to a spreadsheet (Cells, Range)
• Download and upload data to the user (InputBox, MsgBox)
• The extent and lifetime of variables
• Operators and their priorities
• Useful module options
• Securing code - code protection from tampering and preview
• Key Excel objects: Application, Workbook, Workbooks, Worksheet, Worksheets,
• ThisWorkbook, ActiveWorkbook, ActiveCell, Selection, Range, Cells, ...

Debugging

• Immediate window
• Locals window
• The processing step - but what to do when something has stopped working
• Watches
• Call Stack

Error handling

• Types of errors and ways to avoid
• Capturing and handling run-time errors, which is why properly written code can sometimes not work
• Construction: On Error Resume Next, On Error GoTo label, On Error GoTo 0