Monday, September 17, 2007
Convert Text to Uppercase
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)
Here's my code for uppercase conversion:
(If you want to convert your text to lowercase, replace Ucase with Lcase function in the code)
'Will convert selected range to Upper case, using array
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
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
Comments:
Links to this post:
<< Home
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
'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
# posted by 
: 12:14 PM
: 12:14 PM
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.
Post a Comment
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.
<< Home
