- Course overview
- Macro BASIC

o   Developer Tab
o   Code window
- Record Macro
- Visual Basic
- Macro
- Macro security

o   Controls window
- Insert -Form Controls
- Insert ActiveX controls

- Executing macros-macro modules
- Maintaining Macros
- VBA Programming
- Comments in VBA
- VBA Variables
- Declaring Variables
- Assigning Values to the Variables
- Scope of the Variables
- Declaring Constants
- Operators
- What is an Operator?
- The Arithmetic Operators
- The Comparison Operators
- The Logical Operators
- The Concatenation Operators
- Decision Making
- If Statements
- If…Else Statements
- If..ElseIf..Else Statements
- Nested If Statement
- Select Case Statements
- Loops
- For Loops
- For...Each Loops
- While...Wend Loop
- Do..While statement
- Do..Until Loops
- Loop Control Statements
- Exit For statement
- Exit Do statement
- Events
- What is an Event ?
- VBScript Numbers
- Number Conversion Functions
- Number Formatting Functions
- Mathematical Functions
- String Functions
- InStr Function
- InStrRev Function
- LCase Function
- UCase Function
- Left Function
- Right Function
- Mid Function
- LTrim Function
- RTrim Function
- Trim Function
- Len Function
- Replace Function
- Space Function
- StrComp Function
- Arrays
- What is an Array?
- Array Declaration
- Assigning Values to an Array
- Multi-Dimension Arrays
- ReDim Statement
- Array Methods
- Worksheet function
- LBound Function
- UBound Function
- Split Function
- Join Function
- Filter Function
- IsArray Function
- Erase Function
- Date and Time Functions
- Date Functions
- Date Function
- CDate Function
- DateAdd Function
- DateDiff Function
- DatePart Function
- DateSerial Function
- FormatDateTime Function
- IsDate Function
- Day Function
- Month Function
- Year Function
- MonthName Function
- WeekDay Function
- WeekDayName Function
- Time Functions
- Now Function
- Hour Function
- Minute Function
- Second Function
- Time Function
- Timer Function
- TimeValue Function
- Functions
- What is a Function?
- Function Definition
- Calling a Function
- Function Parameters
- Returning a Value from a Function
- Sub-Procedures
- Calling Procedures..
- Advanced Concepts for Functions
- ByVal Parameters
- ByRef Parameters
- Dialog Boxes
- What is a Dialog Box ?
- MsgBox Function
- InputBox Function
- Class Variables
- Class Properties
- Class Methods
- Class Events
- TextStream
- Option Explicit
- IsEmpty
- IsNull
- IsObject
- IsNumeric
- With..End With
- User Forms
- ADO
- FSO
- Error Handling
- Data handling functions
- Data Dictionary
Excel Automation
Automating Excel with VB Script

Introduction to Advanced Excel
- Get a head start with templates and set up an Excel template
- Create new default workbook and worksheet templates
- Learn how modular templates can make your spreadsheets more consistent and much quicker to set up
- Customizing the Excel interface to put the tools that you need at your fingertips.
- How to build in checks and controls from the outset
- Key techniques that reduce risk and increase automation and efficiency
- Documentation and review tools

Module 2: Data Validation
- Extended uses of Data Validation
- Working with validation formulae
- Other methods of tracking down invalid entries

Module 3: Preparing your data for analysis
- Mastering lookup functions (INDEX, MATCH)
- Creating helper columns using DATE and TEXT functions
- Applying NESTED-IF, AND, OR to organize data

Module 4: Methods of Summarizing Data
- Using SUMIFS and COUNTIFS
- Advanced uses of PIVOT-TABLE features like Value Field Settings, Grouping Data and Slicers among others
- Identify Major Customers, Top Products, Top/Bottom Sales reps…LARGE, SMALL, MAX, MIN
- Advanced Range Names and Formula in Names
- Calculations and reporting in Power Pivot – an introduction to Data Analysis Expressions (DAX)

Module 5: Report Visualization Techniques in Excel
- Dynamic charts (using CHOOSE & OFFSET functions) & Sparklines for trends
- Report Interactivity -Using Pick-Lists, Form Controls like combo box & Excel Camera
- Effectively using Conditional Formatting (formula-driven) for reporting
- Exchanging information with VB code
- Calculations in Power Pivot – an introduction to Data Analysis Expressions (DAX)

Module 6: Decision Making with Excel
- Applications of Financial functions (Amortization table, FV, NPV, IRR, etc.)
- Sensitivity (“What-if”) analysis on models using Data Tables, Goal Seek , Scenarios
- Laying out a model in Excel (Budget Model case study)
- Using Excel to help you make decisions
- Create and manage alternative scenarios
- Make more profit or incur less expense by using Excel Solver to identify the best solution
- Use of External Data tools

COURSE OUTCOMES:
At the end of the course, participants should be able to
1. Apply advanced formulas to lay data in readiness for analysis
2. Use advanced techniques for report visualizations
3. Leverage on various methodologies of summarizing data
4. Understand and apply basic principles of laying out Excel models for decision making

WHAT YOU REQUIRE:
Interested organizations/institutions/individuals are expected to have laptop(s) while attending the workshop and should at least have some background in using Excel.

📊 What is Data Analytics?

Data Analytics is the process of examining large datasets to uncover patterns, correlations, trends, and useful insights. It involves using statistical, computational, and machine learning techniques to analyze data and transform it into actionable insights for business decision-making, problem-solving, or predictions. Data analytics plays a vital role in a wide variety of industries, including finance, healthcare, marketing, and technology.

🛠️ Types of Data Analytics:

  1. Descriptive Analytics:

    • Purpose: To describe and summarize the characteristics of a dataset.
    • Example: Reporting on sales performance over the last quarter.
    • Techniques Used: Data aggregation, summarization, and basic statistics (mean, median, mode, standard deviation).

  2. Diagnostic Analytics:

    • Purpose: To understand the causes behind past trends or behaviors.
    • Example: Investigating why sales dropped last month.
    • Techniques Used: Root cause analysis, correlation analysis, and data exploration.

  3. Predictive Analytics:

    • Purpose: To predict future trends and outcomes based on historical data.
    • Example: Forecasting next month’s sales based on past data.
    • Techniques Used: Machine learning models (e.g., regression, time-series forecasting), statistical modeling, and decision trees.

  4. Prescriptive Analytics:

    • Purpose: To recommend actions that can optimize outcomes or solve problems.
    • Example: Recommending marketing strategies to boost customer engagement.
    • Techniques Used: Optimization algorithms, simulations, decision models.

  5. Cognitive Analytics:

    • Purpose: To use AI-driven insights that mimic human decision-making.
    • Example: Using AI to recognize customer sentiment from text data (e.g., social media).
    • Techniques Used: Natural Language Processing (NLP), deep learning, and AI algorithms.

💡 Key Steps in Data Analytics Process:

  1. Data Collection:
    Gather data from various sources like databases, APIs, spreadsheets, or sensors.

  2. Data Cleaning:
    Handle missing data, remove duplicates, and correct errors to ensure the data is accurate and usable.

  3. Data Exploration:
    Analyze and visualize the data to understand its structure, relationships, and distributions.

  4. Data Analysis:
    Apply statistical methods, machine learning, or business intelligence tools to extract meaningful patterns.

  5. Data Interpretation:
    Interpret the results in the context of the problem or business question being addressed.

  6. Reporting & Visualization:
    Create reports and visualizations (charts, dashboards) to communicate insights to stakeholders.