counting the number of cell formats in a workbook

[jérome Yacc]

I'm currently facing the EXCEL error "number of different cell formats
too high" (it is a raw translation of french text, so maybe it's not
the same message in english)

I know that the maximum number of different cell formats is about 4000
per workbook.

So my point is : How to trace the evolution of the current number of
different cell formats in my active workbook? Which property of which
object will give me this figure?

Thank you
Jérôme

 

[Norman Jones]

Hi Jerome,

So my point is : How to trace the evolution of the current number of
different cell formats in my active workbook? Which property of which
object will give me this figure?

I am not awaare of any such property.

I'm currently facing the EXCEL error "number of different cell formats
too high" (it is a raw translation of french text, so maybe it's not
the same message in english)

To resolve your immediate problem, you could try code posted by Leo Heuser:

'=============================>>
Sub DeleteUnusedCustomNumberFormat­s()
'(e-mail address removed), May 6. 2001
'Version 1.01
Dim Buffer As Object
Dim Sh As Object
Dim SaveFormat As Variant
Dim fFormat As Variant
Dim nFormat() As Variant
Dim xFormat As Long
Dim Counter As Long
Dim Counter1 As Long
Dim Counter2 As Long
Dim StartRow As Long
Dim EndRow As Long
Dim pPresent As Boolean
Dim NumberOfFormats As Long
Dim Answer
Dim Cell As Object
Dim DataStart As Long
Dim DataEnd As Long
Dim AnswerText As String
Dim ActWorkbookName As String
Dim BufferWorkbookName As String


NumberOfFormats = 1000
StartRow = 3 ' Do not alter this value
EndRow = 16384 ' For Excel 97 and 2000 set EndRow to 65536


ReDim nFormat(0 To NumberOfFormats)


AnswerText = "Do you want to delete unused custom formats from the
workbook?"
AnswerText = AnswerText & Chr(10) & "To get a list of used and unused
formats only, choose No."
Answer = MsgBox(AnswerText, 259)
If Answer = vbCancel Then GoTo Finito


On Error GoTo Finito
ActWorkbookName = ActiveWorkbook.Name
Workbooks.Add
BufferWorkbookName = ActiveWorkbook.Name


Set Buffer = Workbooks(BufferWorkbookName).­ActiveSheet.Range("A3")
nFormat(0) = Buffer.NumberFormatLocal
Buffer.NumberFormat = "@"
Buffer.Value = nFormat(0)


Workbooks(ActWorkbookName).Act­ivate


Counter = 1
Do
SaveFormat = Buffer.Value
DoEvents
SendKeys "{TAB 3}"
For Counter1 = 1 To Counter
SendKeys "{DOWN}"
Next Counter1
SendKeys "+{TAB}{HOME}'{HOME}+{END}^C{T­AB 4}{ENTER}"
Application.Dialogs(xlDialogFo­rmatNumber).Show nFormat(0)
ActiveSheet.Paste Destination:=Buffer
Buffer.Value = Mid(Buffer.Value, 2)
nFormat(Counter) = Buffer.Value
Counter = Counter + 1
Loop Until nFormat(Counter - 1) = SaveFormat


ReDim Preserve nFormat(0 To Counter - 2)


Workbooks(BufferWorkbookName).­Activate


Range("A1").Value = "Custom formats"
Range("B1").Value = "Formats used in workbook"
Range("C1").Value = "Formats not used"
Range("A1:C1").Font.Bold = True


For Counter = 0 To UBound(nFormat)
Cells(StartRow, 1).Offset(Counter, 0).NumberFormatLocal =
nFormat(Counter)
Cells(StartRow, 1).Offset(Counter, 0).Value = nFormat(Counter)
Next Counter


Counter = 0
For Each Sh In Workbooks(ActWorkbookName).Wor­ksheets
For Each Cell In Sh.UsedRange.Cells
fFormat = Cell.NumberFormatLocal
If Application.WorksheetFunction.­CountIf(Range(Cells(StartRow,
2), Cells(EndRow, 2)), fFormat) = 0 Then
Cells(StartRow, 2).Offset(Counter, 0).NumberFormatLocal =
fFormat
Cells(StartRow, 2).Offset(Counter, 0).Value = fFormat
Counter = Counter + 1
End If
Next Cell
Next Sh


xFormat = Range(Cells(StartRow, 2), Cells(EndRow, 2)).Find("").Row - 2
Counter2 = 0
For Counter = 0 To UBound(nFormat)
pPresent = False
For Counter1 = 1 To xFormat
If nFormat(Counter) = Cells(StartRow, 2).Offset(Counter1,
0).NumberFormatLocal Then
pPresent = True
End If
Next Counter1
If pPresent = False Then
Cells(StartRow, 3).Offset(Counter2, 0).NumberFormatLocal =
nFormat(Counter)
Cells(StartRow, 3).Offset(Counter2, 0).Value = nFormat(Counter)
Counter2 = Counter2 + 1
End If
Next Counter
With ActiveSheet.Columns("A:C")
.AutoFit
.HorizontalAlignment = xlLeft
End With
If Answer = vbYes Then
DataStart = Range(Cells(1, 3), Cells(EndRow, 3)).Find("").Row + 1
DataEnd = Cells(DataStart, 3).Resize(EndRow, 1).Find("").Row - 1
On Error Resume Next
For Each Cell In Range(Cells(DataStart, 3), Cells(DataEnd, 3)).Cells
Workbooks(ActWorkbookName).Del­eteNumberFormat
(Cell.NumberFormat)
Next Cell
End If
Finito:
Set Cell = Nothing
Set Sh = Nothing
Set Buffer = Nothing
End Sub


'<<=============================

---
Regards,
Norman


"jérome Yacc" <[email protected]>
wrote in message

 

[Norman Jones]

Hi Jerome,

To add, if you are not familiar with macros, you may wish to visit David
McRitchie's site and look at his 'Getting Started With Macros And User
Defined Functions' page:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[jérome Yacc]

Hello, Norman

Thank you for your help.
Unfortunately, the use of the proposed macro will not help, as , to be
more precise, the error occurs when executing a macro which aims, at
inserting into a workbook sheets from other workbook, after
pre-processing them.
So it seems to me not conceavable to clean up the workbook after each
insertion. Moreover, the point is that I do not know if the problem
arises from polluting unused cells or from "normal" reasons, that is
because in my pre-processing, I define for (large) range of cells
conditional formats.

One possible explanation for my problem is that EXCEL accounts for a
new cell format if for instance the text in the condition attached to
the conditional format varies.
Indeed, in my condition, I use a VBA macro to test if the cell contains
a formula, so the text of this condition is something like :
"=isFormula($A$1)", when attached to $A$1 cell, where isFormula(aRange)
returns true if aRange contains a formula.
So, for each addressed cell, the text of the condition varies with the
cell reference.

Another possible explanation for my problem is that EXCEL manages cell
formats relative to a sheet, even when the same formats applies between
two sheets. I raises this point because I've pointed out that at each
sheet insertion, the number of format styles increases, because these
styles' names are qualified by the sheet name, even if completely
identical. Format styles are not the same as cell formats, but if it
is done this way for the styles, maybe it is the same for cell
formats.

This is why I wanted to trace, between each insertion, the evolution of
the number of cell formats the receiving workbook has to manage.

Best regards
Jérôme