MS Excel & VBA

MS Excel & VBA

Overview
VBA works with multiple applications in Excel and perform related tasks such as automation, interactions between Office applications and implementation of business logic in Excel. VBA works to improve workbook performance and enables tasks using complex MS – Excel Formulas.
Curriculum
  • Excel
    • Working with Large Worksheets
    • Viewing options
    • Adjusting zoom
    • Using Freeze Panes
    • Using Split Window
    • Hiding and displaying data
    • Hiding rows and columns
    • Creating an outline
    • Printing large worksheets
    • Page Break preview
    • Working with page breaks
    • Printing multiple sheets
    • Working with Multiple
    • Worksheets and Workbooks
    • Using multiple worksheets
    • Navigating between sheets
    • Moving and copying sheets
    • Linking worksheets
    • Creating 3-D formulas
    • Consolidating data
    • Linking workbooks
    • Creating a formula to link workbooks
    • Using the Paste Link option
    • Maintaining links
    • Working with dates
    • Entering date functions
    • TODAY function
    • NOW function
    • Date and time formats
    • Using dates in formulas
    • Conditional formulas and formatting
    • The IF function
    • Conditional formatting
    • Based on a cell value
    • Based on a formula
    • Identifying cells with conditional
    • formatting
    • Copying conditional formats
    • List Management
    • Working with lists
    • Structure of a list
    • Sorting and filtering lists
    • Simple sorting
    • Sorting by multiple columns
    • Using AutoFilter
    • Advanced filtering
    • Using custom filter
    • Using Advanced Filter
  • Excel VBA
    • Create recorded macros in Excel
    • Use the macro recorder to create a variety of macros
    • Understand the Excel object model and VBA concepts
    • Work with the three main components of the VBA Editor window
    • create procedures in VBA
    • Create and use variables
    • Create and work with user-defined functions
    • Write code to manipulate Excel objects
    • Use a range of common programming techniques
    • Create a custom form complete with controls
    • Code to drive a user form
    • Create procedures that start automatically
    • Write a variety of error handling routines
  • Access
    • Database design concepts.
    • Different types of relationships.
    • How to create tables and determining data types.
    • How to relate tables.
    • How to create different types of Queries and when best to use them.
    • Different criteria to use in queries for data analysis and reporting purposes.
    • Creating and customizing forms for data input.
    • Creating and customizing reports.
    • How to import and export Access data.
    • How analyze data using Queries, Pivot tables and reports.
    • Creating an access application using the switchboard, macro’s and modules.
  • Access VBA
    • Overview
    • Visual Basic Editor
    • Objects
    • Object Browser
    • Collections
    • Programming basics
    • Working with data
    • Scope
    • Control structures
    • Decision structures
    • Loop structures
    • ActiveX Data Objects
    • Getting started with ActiveX Data Objects
    • Working with ActiveX Data Objects
    • VBA debugging tools
    • Errors in code
    • Debugging techniques
    • Handling runtime errors
    • Office integration
    • Automating Office 2007
    • Working with Word from Access
    • Working with Excel from Access
    • Conclusion
Features
Real Life Case Studies

Real Life Case Studies

Projects modeled on select use cases with implementation of diverse technology concepts

Assignments

Assignments

All guided classes and courses are mandatorily followed by useful practical assignments

24x7 Expert Support

24x7 Expert Support

Every technical query is resolved on demand with readily available expert assistance

Instructor-led Sessions

Technical session conducted under the guidance of qualified and certified educationists

Course Info

Estimated Duration 3/4 Weeks
Maximum Students 25
Levels Advanced

Social Share