Scribd
Upload
27 views2 pages

Essential String Functions in VBA

String functions allow manipulation of strings in Visual Basic without modifying the original string. Some key string functions include Len() to get string length, Left() and Right() to extract substrings, InStr() to find positions of substrings, and Trim() to remove leading/trailing spaces. Strings can be concatenated with & and compared using operators like = or functions like StrComp().

Uploaded by

Bikash Kumar Behera
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
27 views2 pages

Essential String Functions in VBA

String functions allow manipulation of strings in Visual Basic without modifying the original string. Some key string functions include Len() to get string length, Left() and Right() to extract substrings, InStr() to find positions of substrings, and Trim() to remove leading/trailing spaces. Strings can be concatenated with & and compared using operators like = or functions like StrComp().

Uploaded by

Bikash Kumar Behera
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

String Functions

Strings are not objects so they do not have methods but there is a number of functions that manipulate strings. Note that none of the functions
modify the original string, except for Mid$ when it is on the left hand side of an assignment statement:

Asc Returns the integer code of the first character of the string. The inverse function would be Chr.
Len Returns the length of the string.
InStr Returns the character index of the first occurrence of the substring in a string or zero if the substring is not found.
InstrB Like InStr except that it returns the byte position. It has to be remembered, that Visual Basic 6 strings are Unicode strings.
InstrRev Like InStr except that it returns the character position of the last occurrence of the substring.
Left$ Returns the specified number of characters from the beginning of the string. If there are fewer characters in the string Left$ returns the whole
string, no error is raised,
Mid$ Returns a number of characters starting at the given position, on the left hand side it replaces those characters,
Right$ Returns the specified number of characters from the end of the string, if there are not that many characters, then Right$ returns the whole
string.
IsNumeric Returns true if the string looks like a number.
LTrim$, RTrim$, Trim$ Returns a copy of the string with leading, trailing or leading and trailing spaces removed respectively. Note that only ASCII
spaces (character code 32) are removed, other whitespace characters such as tabs are treated as non-spaces.
LCase$, UCase Converts the whole string to lower case or upper case respectively.
Val Returns a number corresponding to the number found at the start of the string. Note that Val is not locale aware, which means that it always
expects decimal points regardless of the regional settings of your computer; if you are reading from a comma delimited file this is probably the
function you want to use.
Str Returns a string corresponding to the given number. Like Val this is not locale aware. This is the function you should use if you are creating a
text file containing numbers to be read on someone else's computer.
CStr Converts the expression to a string. This procedure is locale aware and the correct function to use if converting numbers and differently typed
values to strings for user-display. Usually it is unnecessary because Visual Basic automatically converts when necessary and uses Regional
Settings to do so.
Format$ Converts a number to a string using a specific format. The format is provided as a string of characters, that shows how many digits
should be given before and after the decimal point. Like CStr, Format$ is locale aware so the decimal separator will be whatever is specified in the
user's Regional Settings. Format$ also provides for conversion of dates to various built-in and custom string formats.
CBool, CByte, CCur, CInt, CLng, CSng, CDbl, CDec
Locale aware conversions to Boolean, Byte, Currency, Integer, Long, Single, Double, Decimal.
Split
Chops a string into pieces and returns a Variant Array. If no delimiter is specified then spaces will be used. Delimiters may be any string of any
length. Two adjacent delimiters delimit an empty string.
Hex$
Returns a string of Hex characters representing a number.
Oct$
Returns a string of Octal characters representing a number.
Replace$
Returns a string with occurrences of a specified substring replaced with a new string. Note that the substring and the new string do not have to be
the same size.
StrComp
Returns -1 if the first string is less than the second, 0 if they are identical, +1 if the first is greater than the second. Takes an optional argument that
determines the comparison algorithm: vbBinary for exact comparisons using the character codes, vbTextCompare for case insensitive comparisons.

Comparison

Two strings are equal by value if they have the same content:

If "Hello" = "Hello" Then MsgBox "A"

The statement Option Compare Text can be placed at the top of a module to make the comparison case-insensitive, impacting =, <, >, <=, >=,
<>:

Option Compare Text


Sub Test()
If "Hello" = "hello" Then MsgBox "A"
If "aaa" < "BBB" Then MsgBox "B"
End Sub

To test whether two strings are equal by reference, that is, whether they start at the same address, you can use StrPtr function.

To test whether a string is greater than or less than another using lexicographical order, you can use <, >, <=, >=, <>. To decide whether a string
is less than another, the two are compared character by character and as soon as a character from one string is found to have a lower ASCII code
than the corresponding (same position) character in the other string, the first is declared to be less than the second. The converse test also works.

Another way of testing whether strings are equal or greater than one another is the StrComp function. Unlike =, <, >, etc., the StrComp function has
an optional argument that controls whether the comparison is case-sensitive.

Example:

strHello = "hello": strHello2 = "Hello"


If StrComp(strHello, strHello2, vbTextCompare) = 0 Then
[Link] "The strings are the same."
End If

Concatenation
The operator intended to perform string contatenation is &. The operator + can sometimes be used to the same effect, but not always:

a = "123"
b = "456"
c = a & b 'Yields 123456
d = a + b 'Yields 123456: applied to two strings
[Link] c, d

a = 123 'A number, not a string


b = "456" 'A string
c = a & b 'Yields 123456
d = a + b 'Yields 579: applied to a number and to a string
[Link] c, d

Common questions

Powered by AI

The & operator is intended for string concatenation, providing consistent results regardless of data types. For example, concatenating two strings '123' and '456' using & results in '123456' . The + operator can also concatenate two strings, yielding '123456', but behaves differently when combining a string with a number, performing an arithmetic addition instead (e.g., '123' + 456 yields 579).

LTrim$, RTrim$, and Trim$ are used to remove spaces from strings: LTrim$ removes leading spaces, RTrim$ removes trailing spaces, and Trim$ removes both leading and trailing spaces. They only remove ASCII spaces (character code 32), not other whitespace characters like tabs .

Option Compare Text can be set at the module's top to make comparisons case-insensitive, affecting comparison operators like =, <, >, etc. It changes the default string comparisons to use vbTextCompare, thereby considering 'Hello' and 'hello' as equal, impacting all string literal comparisons in that module .

When Mid$ is used on the left hand side of an assignment statement, it replaces characters at the specified position in the original string. Otherwise, Mid$ simply returns a substring starting at the specified position without modifying the original string .

Left$ extracts a specified number of characters from the string's start, Right$ from the end, and both return the whole string if fewer characters are requested than are available. Mid$ extracts starting from a given position for a specified length, offering central substring slices. Mid$ can also modify the original string when used on the left side of an assignment .

Split is used to divide a string into an array based on specified delimiters; without a delimiter, it defaults to using spaces. The choice of delimiter can significantly affect the output; for example, using ' ' versus ',' would yield vastly different array elements. Two consecutive delimiters create an array element as an empty string .

Format$ is locale aware, meaning it uses the user's Regional Settings to determine the appropriate decimal separator when converting numbers to strings . Conversely, Val is not locale aware; it always expects decimal points irrespective of the regional settings .

CStr is preferred over Str for converting numbers to strings because it is locale aware, adjusting the conversion according to the user's regional settings, which is beneficial for display purposes and user interfaces. Str is not locale aware and is more suited for creating locale-independent text files .

InStr finds the first occurrence of a substring within a string, returning its character index, or zero if not found. InStrB works similarly but returns the byte position instead, important for Unicode strings. InstrRev performs the reverse search, finding the last occurrence of the substring and returning its character index .

StrComp can be used to compare two strings with an optional argument that specifies the comparison algorithm: vbBinaryCompare for binary comparisons or vbTextCompare for case-insensitive comparisons. It returns -1 if the first string is less, 0 if they are equal, and +1 if the first is greater .

You might also like