Replies

Tell them to change their name.

Microsoft wouldn’t issue a product with a bug – it’s a feature which in this case is telling them that their name is wrong.

Geoff:

You wrote:

“There’s some sample code in Excel which goes:

“for each cell in …”

“When I first came across that, I couldn’t find a type which worked, so I ended up coding…”

I ran into the same thing, although it came from some guy on the comp.apps.spreadsheets ng – he had written a routine to do custom formatting in excel 5. It was quite cool, but he did the same thing: “for each Cell in WorkRange…”

I figured that Cell was a keyword, and couldn’t figure out why I couldn’t re-create his code (at least, not in a way that worked). He was very patient in explaining the problem, and I appreciated that, and he was just a “volunteer” after all.

I agree that it is galling not to have explicit declarations in all the MS help file code examples (and I really hate “myfile,” “myvar,” “mybutton,” etc – they make “mepuke”) and the stuff on the knowledgebase. It doesn’t take much extra time, it is good programming practice, and the whole point is to teach something -sometimes it is useful to know just what sort of value is expected (or allowed) in each variable.

Why doesn’t the lounge have a “recommend this post” feature – your rant deserves a recommendation!

Brett:

John Walkenbach’s EXCELLENT book Microsoft Excel 2000 – Power Progamming with VBA has a discussion of this very topic in chapter 9 (pp 226-237) and walks the reader through the development process for a utility to sort worksheets in alphabetic order.

The eventual program is included in the book, as well as on a companion CD-ROM (as is a slightly improved version). I am reluctant to violate John’s copyright (at least publicly ) but if you pick up a copy in the library there is only about a page of code to re-type, even if you don’t get the CD-ROM. The book is a terrific reference if you are going to be doing any VBA development.

If you are doing this on a one-time basis, John’s suggestion is th way to go. If you are doing this often, try the code in
http://www.wopr.com/cgi-bin/w3t/showthread…d&sb=5#Post1143%5B/url%5D

but change the “Upper(Cell.Value)” function to “Application.Proper(Cell.Value)”

This will convert the selected cells from upper- or mixed-case to proper case. That thread is not a bad place to start looking at VBA macros for this sort of thing. If you are having a hard time linking to that post, the whole code would like like this:

****

Sub ConvertToUpper()
On Error GoTo errConvertToUpper
Dim Cell As Range
For Each Cell In Selection
If Not Cell.HasFormula Then Cell.Value = Application.Proper(Cell.Value)
Next Cell
exitConvertToUpper:
Exit Sub
errConvertToUpper:
If Err.Number = 438 Then
MsgBox “You probably don’t have cell(s) selected”, vbExclamation, “Selection Alert”
Resume exitConvertToUpper
End If
MsgBox Err.Number & ” ” & Err.Description
Resume exitConvertToUpper
End Sub

****

Good catch, Tom!

I think the code looks pretty good now!

Just like the old carpenter’s rule, “measure twice – cut once” I must remember to read twice, post once!

First, there is no need for the “Rng” variable in my last bit of code – it will work perfectly well by using:

For Each Cell in Selection…

and probably a microsecond faster, too!

Second, Geoff left open the question of how to make the macro available to all workbooks. Create it in your personal macro workbook, and if you are going to use it a lot, assign it to a (new) button on a toolbar. It will always be available to *you*, even if not to other users.

Geoff Whitfield suggested looping through the rows and columns in the selected range and substituting the UCase(text) for text in each cell.

I think the approach is right, but runs into problems if the selected range does not start at A1, or is not a rectangular section of the s/sheet, I would think.

I would suggest:
****
Option Explicit

Sub ConvertToUpper()
Dim Rng As Range
Dim Cell As Range

Set Rng = Selection
For Each Cell In Rng
If Not IsNumeric(Cell.Value) Then Cell.Value = UCase$(Cell.Value)
Next Cell

End Sub
****

The test for a numeric value is just to avoid overwriting formulas with their value equivalents. Unfortunately I couldn’t find an equivalent “IsString” function, so I had to use the negation of the numeric test – there could be other values that you would want to avoid overwriting.

Thanks – works like a charm. I knew I had seen something like that before, but couldn’t recall where (early dementia, I suspect…)

You might also look at:

http://www.beyondtechnology.com/geeks007.shtml

a site run by Rodney Powell. This particular page discusses the creation of dynamic named ranges, which will expand and contract based on the number of non-blank lines in your s/sheet.

There are come limitations (as always) that may mean it won’t work for you – primarily that the database has to be the only thing on the sheet, and that in order to reference that named range the s/sheet has to be open (although not active).

I am not sure if Access (or was it FoxPro?) would deal properly with such a named range, but it might be worth a try.