Training videos for learning BUILT-IN VBA FUNCTIONS

Built-in VBA functions - 9 videos

Excel VBA Part 53.1 - Working with Strings

Posted by Andrew Gould on 20 February 2017

This video explains the basics on working with text, or strings, in VBA. You'll learn about the String data type, how to use the $ type-declaration character and how to create variable and fixed-length strings. You'll see how to concatenate multiple values into a single string, including some string constants for special characters such as tab spaces and new lines - ever wondered why there are so many ways to create a new line in VBA? This video explains why! The final part of the video talks about comparing strings, including how to deal with case-sensitive comparisons and how to use wildcard characters to match paterns of text.

Excel VBA Part 53.2 - Splitting Strings

Posted by Andrew Gould on 21 February 2017

This video explains how to use VBA functions to split strings into their constituent parts. You'll learn about the Left, Right and Mid functions for extracting parts of a string, how to identify the position of one string within another using the InStr and InstrRev functions and how to use the Split function to break a single string into multiple parts and store them in an array.

Excel VBA Part 53.3 - The Join Function and Concatenating Strings

Posted by Andrew Gould on 22 February 2017

This video explains how to join or concatenate strings in VBA. You'll learn about to different operators you can use to do this, and why one is better than the other. You'll also learn how to use the Join function to take the contents of an array and concatenate them into a single string. The final part of the video shows a couple of practical uses for these techniques with an example that writes data to text files.

Excel VBA Part 53.4 - Replacing Strings

Posted by Andrew Gould on 27 February 2017

In this video you'll learn how to use the Replace function to substitute characters within a string. As a practical example, we'll look at how to generate file names from cell contents and replace the illegal characters.

Excel VBA Part 53.5 - Converting the Case of Text

Posted by Andrew Gould on 27 February 2017

This video explains how to change the case of text using VBA. You'll see how to make string comparisons case insensitive using the Option Compare statement as well as the UCase, LCase and StrComp functions. You'll learn how to convert strings to Proper Case or Title Case. The video also explains how to create custom functions for converting text into Sentence Case and to toggle the case of individual characters in a string.

Excel VBA Part 53.6 - The Asc and Chr Functions

Posted by Andrew Gould on 06 March 2017

This video explains how to use the Asc and Chr functions in VBA.

Excel VBA Part 54.1 - The Iif Function

Posted by Andrew Gould on 27 February 2017

The IIF function in VBA is, apart from the spelling, exactly like the If function used in Excel worksheets. This video provides a quick overview of how the function works, comparing it with the If statement and creating a practical example to separate a list of data into different worksheets.

Excel VBA Part 54.2 - The Switch Function

Posted by Andrew Gould on 27 February 2017

The Switch function allows you to perform multiple logical tests in a single, succinct expression in VBA. For simple conditions, you can use Switch as an elegant replacement for long-winded Select Case statements. This video shows how the Switch function works, inluding how to use it to create a user-defined function to help separate a list of data into different sheets.

Excel VBA Part 55.1 - Working with Dates

Posted by Andrew Gould on 03 May 2017

This video explains the basics of working with dates in VBA. You'll learn about the Date data type and how to write unambiguous dates in your VBA code. You'll also learn about Excel's leap year bug and why some VBA dates don't match those in Excel. You'll see how to return the current date and time, as well as various techniques to format dates. The final part of the video describes a variety of date calculations and functions including the difference between DateDiff and DateDif, calculating working days using NetWorkDays and how to calculate age in years accurately.

This page has 0 threads Add post