Microsoft Excel VBA Part 2 [SKT_MSEXVBAP2]

OVERVIEW

This course builds on the topics introduced in Microsoft Excel Macros & VBA Part 1, to help delegates gain a deeper understanding of the functionality of Visual Basic for Applications (VBA) within Microsoft Excel. Delegates will learn how to troubleshoot errors within their macros, create code using IF, THEN and ELSE statements, and use loops to automate a series of specific tasks within Excel.

OBJECTIVES

By actively participating in this course, you will learn about the following:

• Creating code that uses If, Then and Else Statements

• Creating Single, Double and Triple Loops

• Creating a ‘Do While’ Loop

• Correcting errors in macros

• Joining String Messages

• Extracting text using Left & Right Functions

• Finding the length of a string using LEN

• Finding the position of a substring using InStr

• Creating a macro for finding the Year, Month and Day of a date

• Using and manipulating Dates using DateAdd & current Date and Time

• Using Hour, Minute and Second Values in the VBA

• Understanding and use the TimeValue

AUDIENCE

This course is perfect for people who wish to gain the skills necessary to create macros and custom functions and understand the terminology and behaviour of the Visual Basic language.

CONTENT

If Then Statements

• Logical Operators

• Select Case

• Tax Rates

• Mod Operator

• Prime Number Checker

• Find Second Highest Value

• Sum by Colour

• Delete Blank Cells

Loops

• Loop through Entire Column

• Do Until Loop

• Step Keyword

• Create a Pattern

• Sort Numbers

• Remove Duplicates

Troubleshooting Macros

• Debugging

• Error Handling

• Err Object

• Interrupt a Macro

• Subscript Out of Range

• Macro Comments

String Manipulation

• Separate Strings

• Reverse Strings

• Convert to Proper Case

• InStr

• Count Words

Date & Time

• Compare Dates and Times

• DateDiff Function

• Weekdays

• Delay a Macro

• Year Occurrences

• Tasks on Schedule/Sort Dates