Excel change case add in

  • Thread starter Thread starter Mr.Cricket
  • Start date Start date
Don't want to be *too* critical, but since you're offering this in an
open forum, people who might download your add-in should be aware of a
few flaws:

1) There is a BAD bug in your code. If you select two or more cells with
formulae in them, those formulae are converted to string literals. E.g:

Before After
A1: ="Profit: " & J1 PROFIT: 100
A2: ="hello" HELLO
J1: 100 100


2) The add-in changes the Selection - in general that's bad form,
especially when the add-in's behavior depends on the Selection object.
Much better to use range objects directly. This is especially true if
the user has a _SelectionChange event macro, which might have
unpredictable effects on your add-in's behavior.

3) Resetting the user's calculation mode to Automatic is also generally
bad form.

4) Using SpecialCells is problematic and can lead to inconsistent
behavior without careful coding. If a single cell is selected,
Selection.SpecialCells will select from the entire worksheet, while if
two or more cells are selected, SpecialCells only looks at the selected
cells.

5) Protecting your code for such a simple function is overkill and
should lead users with any degree of caution to avoid your add-in.

All of these should be easy to correct.
 
4) Using SpecialCells is problematic and can lead to inconsistent
behavior without careful coding. If a single cell is selected,
Selection.SpecialCells will select from the entire worksheet,
Click to expand...

That has bitten me in the ass a few times. I think the design decision to
default to the whole sheet is one of the dumber decisions MS has made for
Excel in quite a while.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
Chip Pearson said:
That has bitten me in the ass a few times. I think the design decision to
default to the whole sheet is one of the dumber decisions MS has made for
Excel in quite a while.
Click to expand...

Agreed - in my apps the only way I use SpecialCells on a Selection
(which is admittedly very rarely) is to wrap it with the Intersect
method - usually in a function, so I don't forget, e.g.:

Public Function SelectionSpecialCells( _
ByVal nType As Long, _
Optional ByVal vValue As Variant = Empty) As Range
Dim rTest As Range
If TypeOf Selection Is Excel.Range Then
With Selection
If IsEmpty(vValue) Then
Set rTest = Intersect(.Cells, .SpecialCells(nType))
Else
Set rTest = Intersect(.Cells, .SpecialCells(nType, vValue))
End If
End With
End If
Set SelectionSpecialCells = rTest
End Function
 
The best Excel Change Case Add-In can be found at the following link

http://www.nucleation.in/2012/07/change-case-excel-add-in.html

In this add in, change case functionality is incorporated to the context menu, which makes it very easy to use.

This Add-In has the following features
a. Change case of selected cells to ‘UPPER CASE’
b. Change case of selected cells to ‘lower case’
c. Change case of selected cells to ‘Proper Case’
d. Change case of selected cells to ‘Sentence case’
e. Change case of selected cells to ‘tOgGlE cAsE’
f. Customizable shortcut keys to perform change case actions
 
Back
Top