VBA selection to UpperCase

BTW the section within the 1 to 26 count where it is physically changing each character is for when there is a text string within a formula. This leaves the formula intact but changes the text results within it.

Thanks
Rob H

if you are looking to avoid screen flickering then you can use the application screen updating flag like below-
 

Sub MyUpperCase()
    
    Application.ScreenUpdating = False

    Dim cell As Range
    For Each cell In Range("$A$1:" & Range("$A$1").SpecialCells(xlLastCell).Address)
        If Len(cell) > 0 Then cell = UCase(cell)
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

Select allOpen in new window

Whoa, Rob!

I was just going to suggest that there is no other way than going cell by cell, for example in a For/Next loop like

Sub test()
Dim cel As Range
For Each cel In Selection
    cel.Value = UCase(cel)
Next cel

End Sub

Select allOpen in new window

... but now I feel there may be more to the question than I realise.

cheers, teylyn

The comment above will convert all cells on the current sheet, including the results of formulae.

Whereas mine will only affect the selected range and will check for formulae first.

You could adapt the Application.ScreenUpdating lines into mine, likewise you could switch off calculation if selecting a large range:

Application.ScreenUpdating = False
If Application.Calculation = xlCalculationAutomatic Then
    Let CalcFlag = True

    With Application
        .Calculation = xlCalculationManual
    End With

    End If
    
    ...previous code submission...

    If CalcFlag = True Then

    With Application
        .Calculation = xlCalculationAutomatic
    End With

End If
Application.ScreenUpdating = True

Select allOpen in new window

Thanks
Rob H

This can actually be much simpler for converting to all upper or all lower:

Sub test()
    
    Dim cel As Range
    
    For Each cel In Selection.Cells
        cel.Formula = UCase(cel.Formula) 'use LCase for lower case
    Next
    
End Sub

Select allOpen in new window

Proper (i.e. Title) case is trickier; the following worked on both formulas and constants:

Sub test()
    
    Dim cel As Range
    Dim Word As String
    
    For Each cel In Selection.Cells
        Do
            Word = RegExpFind(cel.Formula, "\b([a-z])(\w*)\b", 1)
            If Word = "" Then
                Exit Do
            Else
                cel.Formula = RegExpReplace(cel.Formula, "\b([a-z])(\w*)\b", StrConv(Word, vbProperCase), False)
            End If
        Loop
    Next
    
End Sub

Function RegExpReplace(LookIn As String, PatternStr As String, Optional ReplaceWith As String = "", _
    Optional ReplaceAll As Boolean = True, Optional MatchCase As Boolean = True, _
    Optional MultiLine As Boolean = False)
    
    ' Function written by Patrick G. Matthews.  You may use and distribute this code freely,
    ' as long as you properly credit and attribute authorship and the URL of where you
    ' found the code
    
    ' For more info, please see:
    ' http://www.experts-exchange.com/articles/Programming/Languages/Visual_Basic/Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html
    
    ' This function relies on the VBScript version of Regular Expressions, and thus some of
    ' the functionality available in Perl and/or .Net may not be available.  The full extent
    ' of what functionality will be available on any given computer is based on which version
    ' of the VBScript runtime is installed on that computer
    
    ' This function uses Regular Expressions to parse a string, and replace parts of the string
    ' matching the specified pattern with another string.  The optional argument ReplaceAll
    ' controls whether all instances of the matched string are replaced (True) or just the first
    ' instance (False)
    
    ' If you need to replace the Nth match, or a range of matches, then use RegExpReplaceRange
    ' instead
    
    ' By default, RegExp is case-sensitive in pattern-matching.  To keep this, omit MatchCase or
    ' set it to True
    
    ' If you use this function from Excel, you may substitute range references for all the arguments
    
    ' Normally as an object variable I would set the RegX variable to Nothing; however, in cases
    ' where a large number of calls to this function are made, making RegX a static variable that
    ' preserves its state in between calls significantly improves performance
    
    Static RegX As Object
    
    If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = ReplaceAll
        .IgnoreCase = Not MatchCase
        .MultiLine = MultiLine
    End With
    
    RegExpReplace = RegX.Replace(LookIn, ReplaceWith)
    
End Function

Function RegExpFind(LookIn As String, PatternStr As String, Optional Pos, _
    Optional MatchCase As Boolean = True, Optional ReturnType As Long = 0, _
    Optional MultiLine As Boolean = False)
    
    ' Function written by Patrick G. Matthews.  You may use and distribute this code freely,
    ' as long as you properly credit and attribute authorship and the URL of where you
    ' found the code
    
    ' For more info, please see:
    ' http://www.experts-exchange.com/articles/Programming/Languages/Visual_Basic/Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html
    
    ' This function relies on the VBScript version of Regular Expressions, and thus some of
    ' the functionality available in Perl and/or .Net may not be available.  The full extent
    ' of what functionality will be available on any given computer is based on which version
    ' of the VBScript runtime is installed on that computer
    
    ' This function uses Regular Expressions to parse a string (LookIn), and return matches to a
    ' pattern (PatternStr).  Use Pos to indicate which match you want:
    ' Pos omitted               : function returns a zero-based array of all matches
    ' Pos = 1                   : the first match
    ' Pos = 2                   : the second match
    ' Pos = <positive integer>  : the Nth match
    ' Pos = 0                   : the last match
    ' Pos = -1                  : the last match
    ' Pos = -2                  : the 2nd to last match
    ' Pos = <negative integer>  : the Nth to last match
    ' If Pos is non-numeric, or if the absolute value of Pos is greater than the number of
    ' matches, the function returns an empty string.  If no match is found, the function returns
    ' an empty string.  (Earlier versions of this code used zero for the last match; this is
    ' retained for backward compatibility)
    
    ' If MatchCase is omitted or True (default for RegExp) then the Pattern must match case (and
    ' thus you may have to use [a-zA-Z] instead of just [a-z] or [A-Z]).
    
    ' ReturnType indicates what information you want to return:
    ' ReturnType = 0            : the matched values
    ' ReturnType = 1            : the starting character positions for the matched values
    ' ReturnType = 2            : the lengths of the matched values
    
    ' If you use this function in Excel, you can use range references for any of the arguments.
    ' If you use this in Excel and return the full array, make sure to set up the formula as an
    ' array formula.  If you need the array formula to go down a column, use TRANSPOSE()
    
    ' Note: RegExp counts the character positions for the Match.FirstIndex property as starting
    ' at zero.  Since VB6 and VBA has strings starting at position 1, I have added one to make
    ' the character positions conform to VBA/VB6 expectations
    
    ' Normally as an object variable I would set the RegX variable to Nothing; however, in cases
    ' where a large number of calls to this function are made, making RegX a static variable that
    ' preserves its state in between calls significantly improves performance
    
    Static RegX As Object
    Dim TheMatches As Object
    Dim Answer()
    Dim Counter As Long
    
    ' Evaluate Pos.  If it is there, it must be numeric and converted to Long
    
    If Not IsMissing(Pos) Then
        If Not IsNumeric(Pos) Then
            RegExpFind = ""
            Exit Function
        Else
            Pos = CLng(Pos)
        End If
    End If
    
    ' Evaluate ReturnType
    
    If ReturnType < 0 Or ReturnType > 2 Then
        RegExpFind = ""
        Exit Function
    End If
    
    ' Create instance of RegExp object if needed, and set properties
    
    If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = True
        .IgnoreCase = Not MatchCase
        .MultiLine = MultiLine
    End With
        
    ' Test to see if there are any matches
    
    If RegX.test(LookIn) Then
        
        ' Run RegExp to get the matches, which are returned as a zero-based collection
        
        Set TheMatches = RegX.Execute(LookIn)
        
        ' Test to see if Pos is negative, which indicates the user wants the Nth to last
        ' match.  If it is, then based on the number of matches convert Pos to a positive
        ' number, or zero for the last match
        
        If Not IsMissing(Pos) Then
            If Pos < 0 Then
                If Pos = -1 Then
                    Pos = 0
                Else
                    
                    ' If Abs(Pos) > number of matches, then the Nth to last match does not
                    ' exist.  Return a zero-length string
                    
                    If Abs(Pos) <= TheMatches.Count Then
                        Pos = TheMatches.Count + Pos + 1
                    Else
                        RegExpFind = ""
                        GoTo Cleanup
                    End If
                End If
            End If
        End If
        
        ' If Pos is missing, user wants array of all matches.  Build it and assign it as the
        ' function's return value
        
        If IsMissing(Pos) Then
            ReDim Answer(0 To TheMatches.Count - 1)
            For Counter = 0 To UBound(Answer)
                Select Case ReturnType
                    Case 0: Answer(Counter) = TheMatches(Counter)
                    Case 1: Answer(Counter) = TheMatches(Counter).FirstIndex + 1
                    Case 2: Answer(Counter) = TheMatches(Counter).Length
                End Select
            Next
            RegExpFind = Answer
        
        ' User wanted the Nth match (or last match, if Pos = 0).  Get the Nth value, if possible
        
        Else
            Select Case Pos
                Case 0                          ' Last match
                    Select Case ReturnType
                        Case 0: RegExpFind = TheMatches(TheMatches.Count - 1)
                        Case 1: RegExpFind = TheMatches(TheMatches.Count - 1).FirstIndex + 1
                        Case 2: RegExpFind = TheMatches(TheMatches.Count - 1).Length
                    End Select
                Case 1 To TheMatches.Count      ' Nth match
                    Select Case ReturnType
                        Case 0: RegExpFind = TheMatches(Pos - 1)
                        Case 1: RegExpFind = TheMatches(Pos - 1).FirstIndex + 1
                        Case 2: RegExpFind = TheMatches(Pos - 1).Length
                    End Select
                Case Else                       ' Invalid item number
                    RegExpFind = ""
            End Select
        End If
    
    ' If there are no matches, return empty string
    
    Else
        RegExpFind = ""
    End If
    
Cleanup:
    ' Release object variables
    
    Set TheMatches = Nothing
    
End Function

Select allOpen in new window

The latter depends on Regular Expressions; fr more info please see my article https://www.experts-exchange.com/Programming/Languages/Visual_Basic/A_1336-Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html

Hi Teylyn,

This was a code that I have been using for some time to replicate the ChangeCase option in MS Word. i believe this is now available in xl2007

However, until recently, I had pretty much what you suggested until I read an answer on EE that included the cell.HasFormula property. Until then I had to be careful not to select formulas or it converted it to the result.

The check on Calculation is within a different sub that I use, the text ranges for conversion don't tend  to be that big for me so I don't need to worry about slowed performance with screen update or calculation otherwise those would have been in the code already.

Thanks
Rob H

Thanks to all.
These are very helpful comment.

But I was only after skip the loop.
I mean a short way of converting the whole selection to uppercase.
Rather then going trough the loop.
I know its simple ot use the loop. but just wondering and I think it owuld have been a bit quicker too isnt it? - if it was possible
Please correct because I am new

I'm quite confused as to what is exactly being asked here, but this appears to convert everything selected to upper case. I've been caught out before with EVALUATE so am a little hesitant:

With Selection
    .Value = Evaluate("IF(ROW(" & .Address & "),UPPER(" & .Address & "))")
End With

Select allOpen in new window

SameerMirza,

I don't think you can do this without looping through each cell.

Converting cell contents to Upper Case is a bit different from, like, applying a certain format to a range with one line of code.

Excel needs to look at each cell individually to figure out how to process the contents, hence the loop is required.

cheers, teylyn

@Rob, thanks. Such a lot to learn. Isn't EE great?

@Teylyn

Certainly is great place to learn ( for free :-} ). Especially from the likes of Patrick and yourself, you have nearly 3 times as many points this year in Excel zone as I do!

Thanks
Rob

StephenJR,
thanks. This is exactly what I am after.
but if I dont want to select the range.
and I have
strtRow  = 5
endRow = 1000
colRef = N
start = N5
end = N1000

How would I replace .select
with .range
I dont know why we are using evaluate but I would google it.

@Rob

>>you have nearly 3 times as many points this year in Excel zone as I do

Yeah, maybe I do. But that may be due to me spending 3 x more time on EE than you do, or me lucking out on questions that happen to suit my area of expertise. And I normally only score when rorya, barryhoudini, zorvek and matthewspatrick (and a few others) are looking the other way. I think in the VBA space you easily have the upper hand compared to my pedestrian approaches. But I'll keep teaching myself and learning, thanks to you and others, and that's one of many things that keeps me here.

As an alternative to checking each cell in a worksheet or each cell in a selection,
also consider checking each used cell with usedrange.

As a test, filling the cells A1 : XXX100 takes me 0.7 seconds using the "with selection" setup and more than a minute using "for each cell"
But this  most likely is caused to a certain extend by additional macro calls that are inserted on my working place due to various document management addins.
The drawback of the "with selection" setup is also that when you operate this on a large part or even a complete excel worksheet, you might get into memory usage problems.

as far as i known, the UPPER() function is not an array function and thus must be called in a loop for each cell.

guy you are great. :) trust me
I would actully like to delete my above comment. - if its possible (acting too dumb)

I think I should just stick with the loop approach.
dont need to do much tricks. It wouldnt make difference in my current module anyways.
I asked this question with the vision that it may be useful in the future.

I hope I am not annoying all the other participants.
But if you have use the loop then I should award the points to the very first answer.