Macros in Excel & VBA

Introduction to VBA (Visual Basic for Application)

• Introduction
  to Object-oriented programming
• Examining
  the Excel object model
• Working
  in the VB Editor
• Setting
  VBA project properties
• Code
  Modules – Create, export and delete
• Creating
  Subroutine or Function
• Controlling
  procedure scope
• Adding
  comments to the code
• Running
  a VBA routine

Variables, Constants and calculations

• Excel
  VBA Data types
• Declaring
  variables & why it is needed?
• Managing
  variable scope
• What
  are static variables and constants?
• Calculations
  using mathematical operations
• Defining
  arrays
• Defining
  and using object variables

Adding logic to VBA Code

• Using With End with statement
• Repeating a task using a For Next loop
• Stepping through all items of a collection using For Each loop
• Repeating a task using a Do loop
• Using If Then statement to perform a task
• Using Cast statements to decide an action

Debugging VBA code

• Managing
  errors with “On Error” statements
• Stepping
  through a subroutine or function
• Setting
  breakpoints in the code
• Verifying
  the output in the immediate window
• Watching
  a value in a routine

Managing workbook elements and data in VBA

• Writing
  a value to a cell
• Cutting,
  copying and pasting cell data
• Finding
  values in cells
• Referring
  to cells using the “Offset” function
• Concatenating
  text strings
• Returning
  part of a string
• Managing
  worksheets with VBA
• Managing
  workbooks with VBA

Advance VBA

• Screen
  updating on/off
• Using
  worksheet functions in a macro
• Using
  input box or message box to receive user input values
• Calling
  a subroutine from another subroutine

Excel events in VBA

• Running
  a procedure when you open, save or close a workbook
• Running
  a procedure when a cell range changes
• Triggering
  a procedure using a specific key sequence

Recording and enhancing Macro

• Recording
  a Macro to drill down data
• Improving
  the macro by writing our own code
• Enhancing
  the macro to work with different files

Managing files using VBA

• Determine
  if a workbook exits
• Determine
  if a folder (directory) exits
• Detecting
  whether a file is open
• Opening
  a workbook
• Closing
  a workbook
• Saving
  a workbook under a new name
• Saving
  a workbook in different formats

Managing worksheets using VBA

• Checking
  if a worksheet exists
• Creating
  and renaming a worksheet
• Copying
  a worksheet within the active workbook
• Copying
  a worksheet to a new workbook
• Copying
  a worksheet to an existing workbook
• Moving
  worksheets

Using Built-in Functions in VBA

• Using
  the built-in Open dialog box
• Managing
  alerts (Suppress and restore)
• Calculating
  data using Excel Worksheet functions
• Using
  the current date and time
• Removing
  spaces in a string

Working with charts via VBA

• Creating
  a chart
• Moving
  a chart to a chart sheet
• Adding
  a new data series to a chart
• Export
  a chart as an image

Creating and managing User Forms with VBA

• Creating
  a UserForm
• Adding
  a TextBox to a UserForm
• Adding
  a ListBox to a UserForm
• Adding
  a ComboBox to a UserForm
• Adding
  an option button/spin button to a UserForm
• Adding
  graphics to a UserForm
• Creating
  a multitab UserForm
• Writing
  UserForm data to a worksheet
• Running
  a UserForm

Interactive Pivot table dashboard with VBA

• Creating
  a dashboard with Pivot Tables
• Using
  form controls and active controls in dashboards
• Simple
  macro codes to create an interactive pivot dashboard
• Using
  Radio Buttons in a dashboard
• Creating
  a Top/Bottom data view in dashboard

User Defined functions and Add-ins

• Creating
  User Defined Functions
• Saving
  UDF as Excel Add-ins
• Distributing
  Excel Add-in files
• Using
  an existing add-in file