String and Numeric Functions

You are likely already familiar with many functions–such as ROUND or TRIM–that can be applied to cell values within a worksheet. VBA also contains a number of string and numeric functions that can be applied to variables.

Overview

This post within the Getting Started with VBA series finishes the focus on Variables in VBA.

In many cases, string and numeric functions in VBA are identical to the equivalent function in an Excel worksheet. This isn’t always the case, however, as some functions will vary in name and syntax. Regardless, there is the opportunity–whether through a VBA equivalent, or a work around–to mimic all worksheet functions within VBA.

This post will not provide a comprehensive list of all functions. The lists below presents some commonly used functions and techniques for manipulating string and numeric variables.

String Functions

String functions are functions which can read or manipulate a string variable. A string variable is any variable that contains alpha characters. String functions are useful, for example, if your script is working with a predefined string, but needs to extract a specific part of the string.

2016-11-28-1More specifically, say a user selects a column letter from a dropdown list, and for the sake of user friendliness, each column value in the dropdown list is preceded by the word “Column”, appearing as “Column A”, “Column B”, “Column C”, etc.

Meanwhile, in the script, you simply want to extract the column letter to be used as part of a range value. This can be accomplished using the Right function, which extracts a specific number of characters from the right side of the string.

In the above example, because the letter “A” was extracted as a substring from the original string “Column A”, the new substring variable can be used within the range object. If the substring was not extracted, VBA would read the range as “Range(Column A:1).Select”, which would return an out-of-range error.

Commonly Used String Functions

LCase

The LCase function will return a string in all lowercase letters.

Left

The Left function will return a substring of characters starting from the left of the string. The function requires two parameters: (1) string variable name, and (2) number of characters (including spaces) from the left starting point of the string.

Len

The Len function returns a numeric value of the total characters (including spaces) in a string.

Mid

The Mid function returns a substring anywhere within an existing string. The function requires two parameters and permits one optional parameter: (1) string variable name, (2) number of characters (including spaces) from the left of the string to the starting point of the substring, and (3) optional: the number of characters in the substring, counting left from the starting point of the substring.

If the optional third “length” parameter is omitted, the function will create a substring from the starting point, to the end of the string.

Replace

The Replace function can be used to replace specific characters of a string with a new set of characters. The function requires three parameters and permits three optional parameters: (1) string variable name, (2) character or sequence of characters to look for, (3) characters to replace the sequence specified in 2, (4) optional: start character, from left, to begin character search, (5) optional: number of instances of the sequence to replace, and (6) specify a binary or text search.

Right

Similar to the Left function, the Right function will return a substring of characters, but from the right side of the string. The function requires two parameters: (1) string variable name, and (2) number of characters (including spaces) from the right ending point of the string.

Split

The Split function will split a string into an array of multiple substrings based on a delimiter. The function requires two parameters and permits one option parameter: (1) string variable name, (2) delimiter search value, and (3) optional: limit to number of splits.

StrComp

The StrComp function will compare two strings. The function requires two parameters and permits one option parameter: (1) first string variable name, (2) second string variable name, and (3) optional: comparison type.

  • If str1 is equal to str2, function will return 0.
  • If str1 is greater than str2, function will return 1.
  • If str1 is less than str2, function will return -1.

UCase

The UCase function will return a string in all uppercase letters.

Concatenating Strings

As the complexity of a spreadsheet application increases, you are likely to encounter a need to concatenate strings, especially when the application needs to provide feedback to the user. The ampersand (&) character can be used to easily concatenate strings.

As shown in the example above, the ampersand character will combine the 4 assigned strings into one string. This example is not very useful as it does not separate the words with spaces. It is possible to include a space value as another variable, or alternatively, you can create a space as a constant value by including it in quotes between ampersands, such as: & ” ” &.

Numeric (Mathematical) Functions

Commonly Used Mathematical Functions

Abs

The Abs function will return the absolute value of a number.

Round

The Round function will return a rounded numeric value. The function requires two parameters: (1) numeric variable name, and (2) number of decimal places, to the right of the decimal, to round to.

Sqr

The Sqr function will return the square root of a number.

Other Resources

Leave a Reply