Solved

VBA selection to UpperCase

Posted on 2011-09-12
19
318 Views
Last Modified: 2012-05-12
Hi,

I was wondering if there is quick way to convert the whole selection to upper case in VBA for excel?
I know that we can go through the loop and pick every cell to convert it to upper case.
But just thinking that may be there is a way that we can convert the whole selection to upper case.

regards
0
Comment
Question by:SameerMirza
  • 5
  • 5
  • 4
  • +4
19 Comments
 
LVL 31

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 36522088
See code below for changing to upper case, lower case and Tite case:

Sub ChangeUpper()

        For Each cell In Selection
            If cell.HasFormula = False And cell.Value <> "" Then
            OT = cell.Value
            NT = StrConv(OT, vbUpperCase)
            Let cell.Value = NT
            Else
            For Count = 1 To 26
            F = Choose(Count, "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", _
            "r", "s", "t", "u", "v", "w", "x", "y", "z")
            R = Choose(Count, "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", _
            "R", "S", "T", "U", "V", "W", "X", "Y", "Z")
    
            Selection.Replace What:=F, Replacement:=R, LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False

            Next Count
            End If
        Next cell
        
End Sub

Sub ChangeLower()

        For Each cell In Selection
            If cell.HasFormula = False And cell.Value <> "" Then
            OT = cell.Value
            NT = StrConv(OT, vbLowerCase)
            Let cell.Value = NT
            Else
            For Count = 1 To 26
            F = Choose(Count, "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", _
            "r", "s", "t", "u", "v", "w", "x", "y", "z")
            R = Choose(Count, "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", _
            "r", "s", "t", "u", "v", "w", "x", "y", "z")
    
            Selection.Replace What:=F, Replacement:=R, LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False

            Next Count
            End If
        Next cell

End Sub

Sub ChangeProper()

        For Each cell In Selection
        If cell.HasFormula = False And cell.Value <> "" Then
            OT = cell.Value
            NT = StrConv(OT, vbProperCase)
            Let cell.Value = NT
        Else
        End If
        Next cell
        
End Sub

Open in new window

0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 36522100
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
0
 
LVL 4

Expert Comment

by:javaftper
ID: 36522102
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

Open in new window

0
 
LVL 50

Expert Comment

by:teylyn
ID: 36522107
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

Open in new window


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

cheers, teylyn
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 36522130
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

Open in new window


Thanks
Rob H
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36522160
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

Open 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

Open in new window


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

Expert Comment

by:Rob Henson
ID: 36522166
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
0
 

Author Comment

by:SameerMirza
ID: 36522193
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
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 36522214
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

Open in new window

0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 50

Expert Comment

by:teylyn
ID: 36522226
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
0
 
LVL 50

Expert Comment

by:teylyn
ID: 36522234
@Rob, thanks. Such a lot to learn. Isn't EE great?
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 36522258
@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
0
 

Author Comment

by:SameerMirza
ID: 36522296
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.
0
 
LVL 50

Expert Comment

by:teylyn
ID: 36522341
@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.
0
 
LVL 19

Expert Comment

by:akoster
ID: 36522354
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.



0
 

Author Comment

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

Author Comment

by:SameerMirza
ID: 36522384
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.
0
 

Author Closing Comment

by:SameerMirza
ID: 37316089
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.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now