VBA for Beginners: An Introduction to Learn VBA Programming with Tutorials and Hands-On Examples by Metzler Nathan

Author:Metzler, Nathan [Metzler, Nathan]
Language: eng
Format: epub
Published: 2020-09-12T00:00:00+00:00

13.1 String Manipulation

String manipulation is done using several inbuilt functions offered by VBA. We will take a look at the important ones .

13.1.1 Length of a string

The Len function is used to find the length of a string. Syntax:

​ <variable> = Len(<string variable>)

​ Eg:

​ x = Len(name)

This function accepts one parameter in the form of a string and returns the length of that string.

13.1.2 Reversing a string

You can reverse a string using a function called StrReverse . Syntax:

​ <variable> = StrReverse(<string variable>)

​ Eg:

​ Var1 = StrReverse(name)

This function accepts one string as a parameter and returns its reverse.

13.1.3 Compare two strings

In order to compare two strings, you can use the StrComp function. This function accepts two strings as parameters and another optional parameter called Compare which sets the mode of comparison. By default, the mode of comparison is binary. This is a slightly advanced concept and hence we will not cover it. Syntax:

​ <variable> = StrComp(<string 1>, <string 2>)

​ Eg:

​ Result = StrComp(str1, str2)

Here is how the function works:

​ If str1 < str2, the function will return -1.

​ If str1 = str2, the function will return 0.

​ If str1 > str2, the function will return 1.

13.1.4 Case conversion

A string can be converted to lower case or upper case using Lcase and Ucase functions respectively. Syntax:

​ ‘Convert all characters to lower case

​ <variable> = Lcase(<string variable>)

​ ‘Convert all characters to upper case

​ <variable> = Ucase(<string variable>)

​ Eg:

​ Name = Lcase (Name)

​ Name = Ucase(Name)

13.1.5 Search for a string

A string can be searched for inside another string using InStr and InStrRev functions. InStr function searches from left to right and InStrRev function searches from right to left.

InStr Syntax:

​ <variable> = InStr([start,] <string 1>,<string 2> [,compare])

This function requires two mandatory parameters – <string 1> and <string 2>. <string 2> is the string to be searched inside <string 1> . In addition to these mandatory parameters, there are two optional parameters – <start> and <compare> . The <start> parameter specifies the location from where the search should begin while <compare> specifies the mode of comparison. If you do not specify where to begin search from, it will start from the beginning of the string. If the given string is found, the function returns the location of the first occurrence of the string. If not, 0 is returned.

InStrRev Syntax:

​ <variable> = InStr(<string 1>,<string 2> [,start] [,compare])

This function requires two mandatory parameters – <string 1> and <string 2>. <string 2> is the string to be searched inside <string 1> . In addition to these mandatory parameters, there are two optional parameters – <start> and <compare> . The <start> parameter specifies the location from where the search should begin from the end of the string while <compare> specifies the mode of comparison.

Here is a script that accepts one string from the user and performs various operations on it:

Sub StringDemo ()

Dim str , u_str , l_str , rev_str As String

'Ask the user to enter a string

str = InputBox ( "Enter a string: " , "Input" )

'Perform various string operations

Dim

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.