VBA Left Function
The Excel VBA Left function extracts characters from the left side of an expression, beginning with the first character in the expression. A user-specified number sets the length of the subset returned, which is a string data type and includes any blank spaces.
Syntax:
Left(Expression, N)
The VBA Left function accepts two arguments, as described below.
Arguments:
| Expression | - the expression from which a substring on the left is extracted |
| N | - number of characters from the left to return |
Notes about usage:
- Expression may be any valid VBA data type, except array or object.
- Left will accept an element of an array for the expression.
- If Expression is a zero-length string (""), then a zero-length string ("") is returned for all valid values of N.
- If N is a fractional number, then it is rounded to the nearest Long integer.
- If the rounded value of N is a negative number, then an error message is generated and the program execution ends.
- If the rounded value of N is 0, then a zero-length string ("") is returned.
- If N is greater than or equal to the number of characters in Expression, the entire string is returned.
- The value returned is string, regardless of the expression data type.
- Using an expression of array or object data type results in an error message and program execution stops.
- The number of characters to return is a required argument. If omitted, an error messages is generated and program execution stops. Note: This function can also be used as part of an Excel spreadsheet formula, and its behavior here is different. Here, a zero-length string is returned ("").
How to use the VBA Left function
Dim str1 As String
str1 = "Last Name, First Name, Address"
MsgBox Left(str1, 1)
' Returns "L"
MsgBox Left(str1, 4)
' Returns "Last"
MsgBox Left(str1, 8)
' Returns "Last Nam"
In the first use of left, the first letter "L" is returned. Asking for four (4) characters to be returned results in "Name." With a request for eight (8) characters to be returned results in "Last Nam," since blank spaces count as characters.
Use the VBA Left function with extra spaces
Here, we consider two expressions conveying the same textual message, but one has extra blank spaces.
Dim str1, str2 As String
Str1 = "This example has lots of spaces"
str2 = " This example has lots of spaces "
MsgBox Left(str1, 1)
' Returns "T"
MsgBox Left(str2, 1)
' Returns " "
MsgBox Left(str1, 16)
' Returns "This example has"
MsgBox Left(str2, 16)
' Returns " This example "
The first string, str1, is of length thirty-one (31) characters, while str2 is of length forty-one (41). If we use Left to extract the first letter of either string, the value returned with str1 is the desired "T," while we will get a space with str2. And the difference between the letters extracted is even more pronounced when asking for sixteen (16).
Imagine having a list of names from which you need the initial or the first name, for example. Having extra unknown spaces can lead to incorrect results.
Using the VBA Left function without a length argument
Dim str1 As String
Str1 = "This example has lots of spaces"
MsgBox Left(str1)
If the length argument is omitted from the Left function, then VBA generates a Compile error saying that the argument is not optional. Further program processing ends.
Interact with an Excel spreadsheet using a User-defined Left function
Create a VBA user-defined function and name it leftEX in which the return value from the VBA built-in Left function is the return value of leftEX.
Next, test that the function leftEX returns the correct value.
Sub testleftEX()
Dim str1, str2 As String
Dim N As Long
str1 = "LastName, FirstName"
N = 8
MsgBox Left(str1, 8)
' Returns "LastName"
str2 = leftEX(str1, N)
MsgBox str2
' Returns "LastName"
End Sub
Using the built-in function Left directly in a VBA subroutine returns the same value as calling the user-defined function leftEX. Next enter "LastName FirstName" in A1 (Cells(1,1)) of a spreadsheet. Then select B1.
Just above B1, click on the function insert button fx. An insert function dialog box appears.
Use the select a category drop-down menu and set to User Defined. Then in Select a Function, scroll down to leftEX, the function we created. Click the OK button.
A Function Arguments dialog box appears. Excel creates input boxes in this dialog box based on the arguments defined in the function selected.
Enter the cell A1, where the data is stored. Then input eight (8) in the input box next to N. This will tell Excel to select the first eight (8) left-most characters in the data in A1.
Because we selected B1 before this point, leftEX will write the result in B1 when the OK button is clicked.
Note: Be careful that you did not select a cell with data in it, as that data will be overwritten, unless, of course, you want to overwrite the data.
Interact with an Excel spreadsheet using the User-defined Left function while omitting the length argument
Repeat the same steps as in Example 4, but do not enter a value for N.
Click OK.
Instead of "LastName" or any other characters, an error flag #VALUE! is recorded in B1.
Note: This example was constructed to show that error messages in Excel functions differ from those in VBA. Excel error messages are not as informative as those generated within a VBA subroutine.