Leo Heuser format cleanup code, and a question

[GatesAntichrist]

(re: DeleteUnusedCustomNumberFormats() '(e-mail address removed), May 6
2001)
Leo's fine code seems to still hold up but has a killer problem
simulated below:
In XL2002, start a fresh sheet. "General" should be the default cel
format.
Type the mere digit 0 in cell A1. It ought to look like a 0, and b
General format.
A2 =COUNTIF(A1:A1,"0.0%")
A2 gets a 1 … ouch.

Those of you USENET vets who are in harmony with the code and Leo ca
see how this gives a false negative, ruining the results. Th
Application.COUNTIF here "tells" the code that "0.0%" is already show
in the "formats being used" column list, though it isn't; it neve
makes it in the "used" column and ultimately shows as unused. It i
not warm and fuzzy when you delete the format relying on that info!

Now for those of you that don't have the code or don't recall it o
don't follow along with the above paragraph:
1. Is this behavior new with XL2002? (that the countif returns 1 an
not 0)
2. Is COUNTIF really looking for text, or is it cleverl
type-converting? Can I "cast" somehow to subvert that?
3. Is the way out of this mess to use .Find method? O
Application.[something else]?

TI

 

[keepITcool]

I've reworked Leo's code:

- skipped the documentation part...
- rather than add a worksheet I've tried to remain within VBA
as much as possible.
- reduced window jittering

To make it perfect I should expand UsedFormats to search for
numberformat's used in Styles,Pivots and Graphs..

(coding for xlXP+ it could more efficient... cause FindFormats can be
used)


I haven't extensively tested it...
but let me know what you think..




Option Explicit
Option Base 0

'USER32
Private Declare Function GetDesktopWindow _
Lib "user32" () As Long
Private Declare Function LockWindowUpdate _
Lib "user32" (ByVal hwndLock As Long) As Long


Sub ClearUnusedNumberFormats()
Dim cUsed As Collection
Dim cDefi As Collection
Dim cKill As Collection
Dim cSkip As Collection

Dim vItm As Variant
Dim sMsg As String

Set cUsed = UsedNumberFormats
Set cDefi = DefinedNumberFormats
Set cKill = New Collection
Set cSkip = New Collection

On Error Resume Next
For Each vItm In cDefi
If IsError(cUsed(vItm(1))) Then
Err.Clear
ActiveWorkbook.DeleteNumberFormat vItm(0)
If Err = 0 Then cKill.Add vItm Else cSkip.Add vItm
End If
Next

For Each vItm In cKill
sMsg = sMsg & vItm(1) & vbNewLine
Next
If sMsg = "" Then sMsg = "None..."
MsgBox sMsg, , "Deleted NumberFormats"


End Sub


Function UsedNumberFormats( _
Optional wkb As Workbook) As Collection
Dim cRes As Collection
Dim wks As Worksheet
Dim rng As Range


If wkb Is Nothing Then Set wkb = ActiveWorkbook

Set cRes = New Collection

On Error Resume Next
For Each wks In wkb.Worksheets
For Each rng In wks.UsedRange.Cells
cRes.Add Array(rng.NumberFormat, _
rng.NumberFormatLocal), rng.NumberFormatLocal
Next
Next
Set UsedNumberFormats = cRes
End Function


Function DefinedNumberFormats( _
Optional wkb As Workbook) As Collection
'Reworked from Leo Heusers original approach

Dim cRes As Collection
Dim rng(0 To 1) As Range
Dim sGen As String

Set cRes = New Collection
sGen = Application.International(xlGeneralFormatName)

If wkb Is Nothing Then Set wkb = ActiveWorkbook Else _
wkb.Activate

'Find a blank cell with General numberformat
With ActiveSheet.Cells
Set rng(0) = ActiveCell
Set rng(1) = .Find("", rng(0))
If rng(1) Is Nothing Then Set rng(1) = rng(0)
While rng(0).Address <> rng(1).Address And rng( _
1).NumberFormatLocal <> sGen
Set rng(1) = .FindNext(rng(1))
Wend
End With
If rng(1).NumberFormatLocal <> sGen Then Exit Function
rng(1).Select

'Loop Thru the Dialog
cRes.Add Array(rng(1).NumberFormat, _
rng(1).NumberFormatLocal), rng(1).NumberFormatLocal
Application.Top = Application.Top - 5000
LockWindowUpdate GetDesktopWindow

On Error GoTo done
Do
DoEvents
SendKeys "{tab 3}{down}{enter}"
Application.Dialogs(xlDialogFormatNumber).Show cRes( _
cRes.Count)(1)
cRes.Add Array(rng(1).NumberFormat, _
rng(1).NumberFormatLocal), rng(1).NumberFormatLocal
Loop

done:

rng(1).NumberFormat = "General"
Set DefinedNumberFormats = cRes
LockWindowUpdate False
Application.Top = Application.Top + 5000


End Function




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

(re: DeleteUnusedCustomNumberFormats() '(e-mail address removed), May 6.
2001)
Leo's fine code seems to still hold up but has a killer problem,
simulated below:
In XL2002, start a fresh sheet. "General" should be the default cell
format.
Type the mere digit 0 in cell A1. It ought to look like a 0, and be
General format.
A2 =COUNTIF(A1:A1,"0.0%")
A2 gets a 1 … ouch.

Those of you USENET vets who are in harmony with the code and Leo can
see how this gives a false negative, ruining the results. The
Application.COUNTIF here "tells" the code that "0.0%" is already shown
in the "formats being used" column list, though it isn't; it never
makes it in the "used" column and ultimately shows as unused. It is
not warm and fuzzy when you delete the format relying on that info!

Now for those of you that don't have the code or don't recall it or
don't follow along with the above paragraph:
1. Is this behavior new with XL2002? (that the countif returns 1 and
not 0)
2. Is COUNTIF really looking for text, or is it cleverly
type-converting? Can I "cast" somehow to subvert that?
3. Is the way out of this mess to use .Find method? Or
Application.[something else]?

TIA

 

[GatesAntichrist]

First, this is a delayed Usenet connection so forgive possible latency.
I mean, really first, Woohoo! A modern improved format cleane
function!

At first blush all seems well. Looks like clean and well-generalize
code. You also have some knowledge of the "local" format that I hadn'
even heard of.

AAR my dummy crash test of it started out fine except
Application.Top = Application.Top - 5000
.Top is already negative 3 (-3) before attempting this, and the ne
assignment wasn't allowed. I assumed this was your devious way t
"hide the infamous jitter" by pushing it off the top of the screen, s
I just commented this line and its later companion.

Oh, LockWindowUpdate GetDesktopWindow is downright unfriendly whe
stepping code (how about just falsing .ScreenUpdate, LOL). I need t
remember this statement when I confront my enemies

These are not even nitpicks - they're nonissues. AFAICT what you di
here is cherry, for my XL2002 testing.

Finally: since you've worked so diligently to get to here, and o
course it is to my benefit, I'll offer some feedback. Beware - all o
the following is sheerly speculative because I haven't had time t
become in karma with the code. Take it in the proper light because
admit to speaking over my head on these:

There did appear to be duplication - some deleted formats also wer
"skipped" in your code. If my eyes see correctly, then I guess thi
has to do with the entire used range sweeping and you just gra
everybody, right? One entry per used cell in the entire workbook? I
so, three thoughts come to mind, which no doubt you've also weighed: A
could it be tightened to only look at unique items, either whil
building the list or while processing; B) given a sufficiently larg
workbook, could the collection process crash? Crash and burn? C) Ar
Set foo=Nothing lines cried out for here? Any other cleanup wit
respect to the "New" ops you have done ... and am I thus in danger if
run the code consecutively without Excel restarts?

I confess on C) that I'm REALLY speaking out of turn. I'm one of thos
real men C programmers (not C pluth pluth, ha ha) who is inexpert wit
things like "New" and "cleaning up constructors" or whatever it i
OOPers do

Once again, bravo. I'll be working this puppy hard and keep reporting
believe you me

 

[keepITcool]

I found a 'hightech' approach here:
http://www.excel-center.com/mailing/091500h.htm

Further comments helter skelter thru your essay

I'll do some testing tomorrow. My karma needs some sleep
Greetz to BillG, none to GeorgeW.

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

First, this is a delayed Usenet connection so forgive possible latency.
I mean, really first, Woohoo! A modern improved format cleaner
function!

At first blush all seems well. Looks like clean and well-generalized
code. You also have some knowledge of the "local" format that I hadn't
even heard of.

AAR my dummy crash test of it started out fine except
Application.Top = Application.Top - 5000
Top is already negative 3 (-3) before attempting this, and the new
assignment wasn't allowed. I assumed this was your devious way to
"hide the infamous jitter" by pushing it off the top of the screen, so
I just commented this line and its later companion.

'Problem is more likely caused by maximized window.
I'll include a change to set the windows to normal in my 'next version'.

Oh, LockWindowUpdate GetDesktopWindow is downright unfriendly when
stepping code (how about just falsing .ScreenUpdate, LOL). I need to
remember this statement when I confront my enemies

try out both. I think LockWindowUpdate gives less 'jitter', and combined
with the Top.. is jitter free.

These are not even nitpicks - they're nonissues. AFAICT what you did
here is cherry, for my XL2002 testing.

Finally: since you've worked so diligently to get to here, and of
course it is to my benefit, I'll offer some feedback. Beware - all of
the following is sheerly speculative because I haven't had time to
become in karma with the code. Take it in the proper light because I
admit to speaking over my head on these:

There did appear to be duplication - some deleted formats also were
"skipped" in your code. If my eyes see correctly, then I guess this
has to do with the entire used range sweeping and you just grab
everybody, right? One entry per used cell in the entire workbook? If
so, three thoughts come to mind, which no doubt you've also weighed: A)
could it be tightened to only look at unique items, either while
building the list or while processing; B) given a sufficiently large
workbook, could the collection process crash? Crash and burn? C) Are
Set foo=Nothing lines cried out for here? Any other cleanup with
respect to the "New" ops you have done ... and am I thus in danger if I
run the code consecutively without Excel restarts?

It has to look at all items, but rest assured it 'collects' unique items
only. ... by assigning the KEY to the collection this forces only
uniques entries added to the collection. (all errors ignored)
so the collection count will remain well below 1000
(where it tends to get sluggish)

as i said.. finding the used formats could be significantly improved if
your just coding for xlXP+

First get all Defined NFs.
Then do a Find (with FindFormat) for each Defined NF.