Written by If you want to convert the text to uppercase, use the following code; however, I recommend downloading ASAP Utilities, it has many functionalities, including text conversion. It doesn’t offer source code though.
Here’s my code for uppercase conversion:
(If you want to convert your text to lowercase, replace Ucase with Lcase function in the code)
Sub Conv2UCase()
On Error GoTo Conv2UCase_Error
Dim vDataArr As Variant
Dim lUpperBndRow As Long, lUpperBndCol As Long
Dim lRow As Long, lCol As Long
‘store selected values in an array
vDataArr = Selection
‘get the upper bound of rows
lUpperBndRow = UBound(vDataArr, 1)
‘get the upper bound of cols
lUpperBndCol = UBound(vDataArr, 2)
‘Start a loop to go through all the elements of the array
For lRow = 1 To lUpperBndRow
For lCol = 1 To lUpperBndCol
‘Check if the value is text, if not don’t convert
If WorksheetFunction.IsText(vDataArr(lRow, lCol)) Then
‘Convert values to upper case
vDataArr(lRow, lCol) = UCase(vDataArr(lRow, lCol))
End If
Next lCol
Next lRow
‘Return the converted values to the range
Selection = vDataArr
Exit Sub
Conv2UCase_Error:
MsgBox “Error ” & Err.Number & ” (” & Err.Description & “) in Sub:Conv2UCase”
End Sub
Related posts:
What a poor effort. Messy, too many variables, slow. Just do this:
‘Will convert selected range to Upper case
Sub Conv2UCase()
Dim vCell As Variant
For Each vCell In Selection
vCell.Value = UCase(vCell.Value)
Next
End Sub
Anon: True.
I just wanted to demonstrate the use of array, and this was probably when I was new to VBA. BTW, your code is slightly inefficient too. Instead of having a cell dimmed as variant, it should be rngCell as Range.