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.
TARGET 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.
COURSE PREREQUISITES:
To be successful in this course, you should have a good working knowledge of Excel. We recommend you first attend the following courses or have equivalent knowledge:
•
Microsoft Excel Level 1
•
Microsoft Excel Level 2
•
Microsoft Excel Level 3 or Microsoft Excel Level 4
•
Microsoft Excel VBA Part 1
Software Requirements for Virtual Classroom
If you are attending one of our Virtual Classroom courses, you will need to have Microsoft Excel 2013, 2016, 2019 or 365 (desktop version) installed on your device. You may notice differences in software if using a MAC. For the best experience we also recommend that you have two screens and a working microphone. A webcam is optional, but very much appreciated by our trainers!
COURSE 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
COURSE OBJECTIVE:
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
FOLLOW ON COURSES:
Not available. Please contact.