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.
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
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
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:
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!
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.
