Certificate Authentication

Introduction to VBA

• Introducing the VBA environment
• VB Editor
• Using Project Explorer
• Toolbox
• Modules
• Procedures and Functions
• Comments

Principles and concepts of programming

• Data types, variables and constants
• Program flow control
• If…Then… Else
• Do…Loop, While and Until
• For…Next statements 
• Input / Output, Message boxes, Input boxes
• Code behind forms, Form controls manipulation
• Passing arguments, Returning Values

Debugging

• Run Time, Design Time, and Break Mode
• Breaks and Watches
• Local Window
• Immediate Window

Excel Object Model

• The protection sheet from VBA
• Object Workbook, Workbooks collection
• Worksheet Object, Collection Worksheets
• Validation sheets
• Practical methods of the Range object
• Copy, paste, paste special
• Property CurrentRegion
• Find, replace
• Sorting ranges
• Charts (Object Chart)

Events

• Application-level events

Boards

• Dynamic arrays
• Table arrays Variant
• Optimizing arrays and memory
• Multi-dimensional arrays

Object-Oriented Programming

• Classes and Objects
• Creating classes
• Creating and Destroying Objects
• Create methods
• Create property
• Validation data using property
• The default properties and methods
• Error handling in the class module

Create and manage collections

• Create a collection
• Adding and removing items
• References to the components (using a key and an index)

Advanced structures and functions VBA

• Passing parameters by value and reference (ByRef and ByVal)
• Procedures with a variable number of parameters
• Optional Parameters and Defaults
• Procedures of unknown number of parameters (ParamArray)
• Enumeration, convenient parameter passing
• Type the user (User-defined Type)
• Service Null, Nothing, empty string "", Empty, 0
• Type conversion (Conversion)

File Operations

• Opening and closing text files
• Reading and writing text and binary data
• Processing of records in the CSV file
• Efficient processing of text files

Use VBA functions in other applications

Extras

• Create your own add-ons
• Create a toolbar for addition
• Installing your own add-ons and their protection

Using external libraries

Connecting to external databases (ODBC, OLEDB)

Access - Object-Oriented Programming

• Access object model
• Objects, Collections
• Events
• Methods and properties
• Data Access Object Library

Access User Interface Design

• Programming event procedures
• Dynamic combo boxes
• User inputs
• Interface design considerations
• Menus

Access SQL & Database Design

• Filtering – various Where clause options
• Deriving data - calculated fields
• Inner table joins
• Outer table joins
• Sub-queries – filtering, virtual tables and columns,
• Creating – adding rows directly or via queries
• Updating and Deleting - directly and via sub-queries
• Creating and Dropping tables
• Creating relationships
• Use of primary and foreign keys

Power BI Architecture

Data sources

• On-premises and online data sources
• Data transformations + M language
• Direct connections to selected sources (SQL Server, OLAP)

Modeling

Relationship between tables (single and multidirectional data filtering)

• DAX - Understanding the syntax and best practices
• Introduction to DAX
  • Most commonly used functions and context of calculations
  • Working with the time dimension (including fiscal periods, comparing periods, YTD)
  • Functions for naturalizing a parent-child hierarchy
  • Filter functions
  • DAX templates

Visualizations

• Interactive data analysis
• Select the appropriate visualization
• Filters, grouping, exclusions
• Visualization on maps
• Visualization enhancements (so-called custom visuals)

Data Access Management - Row-Level Security

Team work and mobile with Power BI

• Dashboards and reports
• Q & A mechanism
• Workspaces Mobile applications