Advanced VBA Excel Course Singapore - Advanced VBA Excel 2010

Course details

• This 3-day course is a follow-up course to our Visual Basic Excel 2007/2010/2013 Course. Participant who wishes to learn howto automate Excel advance features like PivotTables, Pivot Charts, Dashboard and Window File Dialogs. This course alsoteaches automatic sending email within Excel, automate printing, set timer to run VBA macros, retrieve data from Accessand import/export data to Microsoft Office like Word and Outlook Address Book 
• Participants will be able to extend your VBA excel macros to PivotTable, Sorting, Filtering, create Dashboard, PivotCharts and learn how to retrieve data from Access and import/export data to Microsoft Word and Outlook Address Book

Prerequisites:

• Prior knowledge in VBA Excel 2007/2010/2013 is mandatory - Branches and Conditional Looping, How to manage errors in VBA Excel and Procedures & Functions in VBA Excel.

Certificate Of Attendance:

• Certificate Of Attendance will be awarded to participants completing at least 75% of the course.

Who Should Attend This Course?

• Participants who are keen to extend their VBA Excel Programming knowledge further in the area of PivotTable, PivotCharts, filters and sorting, automated email sending of forms/excel spreadsheet, importing/export from database software like SQL Server, SAP to perform data analysis,etc

Course Outline:

1. VBA Worksheet Data with Events

• Introduction to Event Procedure
• Enabling and Disable Events
• Event Sequence
• Worksheet Events
• Workbook Events
• PivotTable Events
• Charts Events
• Other Excel Events like onTime, OnKey method

2. Programming PivotTables and PivotCharts

• Building a PivotTable in Excel VBA
• Creating and Configuring PivotTable
• Creating a PivotTable Report from an Access Database
• Adding Fields to the Data Area
• Formatting, Grouping and Sorting a PivotTable Report
• Adding Calculated Data Fields and Items to a PivotTable
• Filtering a Data Set in Pivot Tables
• Creating a PivotChart Report Using VBA
• Understanding Slicers (Excel 2010)
• Working with Slicers using VBA

3. Data Verification and Conditional Formatting

• Adding Data Bars to a Range
• Adding Color Scales to a Range
• Adding Icons sets to a Range
• Conditional Formatting Using VBA
• Conditional Formatting Rule Precedence

4. File and Folder Manipulation with VBA

• Manipulating File and Folders
• Changing the name of a file or folder
• Checking and Existence of a File or Folder
• Creating and Deleting Folders
• Copy Files and Deleting Files

5. Excel Dialog Boxes

• Excel Dialog Boxes
• File Open and File Save As Dialog Boxes
• Filtering Files
• Selecting Files
• GetOpenFile and GetSaveAsFile method

6. Creating Dashboard Excel 2010

• Creating Sparklines
• Scaling the Sparklines
• Formatting Sparklines
• Formatting a Win/Loss Charts
• Creating a Dashboard
• Creating Inpidual Sparklines in a Dashboard

7. Printing from Excel Using VBA

• Controlling Page Setup
• Controlling Page Layout, Margins, Header/Footer and Sheet Tools
• Retrieving Current Values from Page Setup Dialog Box
• Print Previewing a Worksheet
• Changing the Active Printer
• Disabling Printing and Print Preview
• Using Printing Events

8. Emailing from Excel Using VBA

• Configuring VBA Excel to automate sending Email even if Outlook is closed
• Using VBA Excel to email Ranges
• Using VBA Excel to email Worksheets or Selected Worksheets
• Using VBA Excel to email Workbooks
• Using VBA Excel to send attachment even if Outlook is closed
• Mass email Using VBA Excel

9. Using Excel VBA to Interact with Office Applications

• Using the CreateObject Function
• Creating a New Word Document Using VBA
• Opening an Existing Word Document
• Using the New Keyboard
• Using VBA Excel to automate Importing Microsoft Outlook Contacts, Address & Particulars

10. Using Excel VBA to retrieve Access Database

• Connecting to an Access Database
• Opening an Access Database
• Performing Access Tasks from Excel
• Retrieving Access Data into an Excel Worksheet
• Transferring the Excel spreadsheet to an Access Database
• Linking an Excel Spreadsheet to an Access Database
• Placing Excel Data in an Access Database