vba 太多不同的单元格格式

Too Many Different Cell Formats

提问by Craig

1. I have a massive file with 10 sheets recreated from scratch, 12 sheets updated, 5 sheets loaded with raw data, and 7 sheets that are used by the macros for the report.
2. I have recently added a new sheet and am running into the Excel "Too many different cell formats"problem.

1. 我有一个巨大的文件，其中 10 张从头开始重新创建，12 张更新，5 张加载了原始数据，7 张由宏用于报告。
2. 我最近添加了一个新工作表，并且遇到了 Excel “太多不同的单元格格式”问题。

what I have tried

我试过的

I have read some of the google search results and they say I should simplify the formatting, but I don't even know how I got up to 4000 distinct cell formats, let alone how much I'm using which ones, so I can remove some.

我已经阅读了一些谷歌搜索结果，他们说我应该简化格式，但我什至不知道我是如何得到多达 4000 种不同的单元格格式的，更不用说我使用了多少种格式，所以我可以删除一些。

It is also popping up some times the file is run, but not all, until such time as it comes up, and then it happens every time it is run. Since the macro is doing so much of the work, including creating the 10 sheets from scratch, I am not sure what to do.

它也会在文件运行时弹出一些时间，但不是全部，直到它出现时，然后每次运行时都会发生。由于宏做了很多工作，包括从头开始创建 10 张纸，我不知道该怎么做。

Does anyone know

有人知道吗

• of a Macro I could run to get a listing of all the cell formats and how many cells are using them?
• of a program they trust to help remove excess cell formats?

• 我可以运行一个宏来获取所有单元格格式的列表以及有多少单元格正在使用它们？
• 他们信任的程序可以帮助删除多余的单元格格式？

Thanks

谢谢

采纳答案by Mike Lewis

"Cell formats" are complicated. Cells do not really have a "format". They have a font (which itself has a name and a size), a NumberFormat, Height, Width, Orientation, etc.

“单元格格式”很复杂。单元格并没有真正的“格式”。它们有一个字体（它本身有一个名称和一个大小）、一个数字格式、高度、宽度、方向等。

So you need to define what you mean by "format".

因此，您需要定义“格式”的含义。

Below is code to get the Font Name and Size. You can substitute whatever attributes you like.

下面是获取字体名称和大小的代码。您可以替换任何您喜欢的属性。

The code below assumes you have created a Worksheet named "Formats" in the workbook. After you run the macro, the Font Names and sizes will be listed in that worksheet.

下面的代码假设您已经在工作簿中创建了一个名为“格式”的工作表。运行宏后，字体名称和大小将列在该工作表中。

Public Sub GetFormats()

    Dim CurrentSheet As Integer
    Dim UsedRange As Range
    Dim CurrentCell As Range
    Dim rw As Long

    Sheets("Formats").Cells.ClearContents
    rw = 1
    For CurrentSheet = 1 To Sheets.Count
        Set UsedRange = Range(Sheets(CurrentSheet).Range("A1"), Sheets(CurrentSheet).Range("A1").SpecialCells(xlLastCell))
        For Each CurrentCell In UsedRange
            FontUsed = CurrentCell.Font.Name + ":" + CStr(CurrentCell.Font.Size)
            If Sheets("Formats").Cells.Find(FontUsed) Is Nothing Then
                Sheets("Formats").Cells(rw, 1).Value = FontUsed
                rw = rw + 1
            End If
        Next
    Next CurrentSheet
End Sub

回答by Chuck The Nerd

The problem you describe caused me (and a coworker) to lose many hours of productivity when using Excel 2010. The following VBA code / macro helped me to drop a .xlsm file from using 3540 styles down to 34.

您描述的问题导致我（和一位同事）在使用 Excel 2010 时失去了许多小时的工作效率。以下 VBA 代码/宏帮助我将 .xlsm 文件从使用 3540 样式减少到 34。

' Description:
'    Borrowed largely from http://www.jkp-ads.com/Articles/styles06.asp

Option Explicit

' Description:
'    This is the "driver" for the entire module.
Public Sub DropUnusedStyles()

    Dim styleObj As Style
    Dim rngCell As Range
    Dim wb As Workbook
    Dim wsh As Worksheet
    Dim str As String
    Dim iStyleCount As Long
    Dim dict As New Scripting.Dictionary    ' <- from Tools / References... / "Microsoft Scripting Runtime"

    ' wb := workbook of interest.  Choose one of the following
    ' Set wb = ThisWorkbook ' choose this module's workbook
    Set wb = ActiveWorkbook ' the active workbook in excel


    Debug.Print "BEGINNING # of styles in workbook: " & wb.Styles.Count
    MsgBox "BEGINNING # of styles in workbook: " & wb.Styles.Count

    ' dict := list of styles
    For Each styleObj In wb.Styles
        str = styleObj.NameLocal
        iStyleCount = iStyleCount + 1
        Call dict.Add(str, 0)    ' First time:  adds keys
    Next styleObj
    Debug.Print "  dictionary now has " & dict.Count & " entries."
    ' Status, dictionary has styles (key) which are known to workbook


    ' Traverse each visible worksheet and increment count each style occurrence
    For Each wsh In wb.Worksheets
        If wsh.Visible Then
            For Each rngCell In wsh.UsedRange.Cells
                str = rngCell.Style
                dict.Item(str) = dict.Item(str) + 1     ' This time:  counts occurrences
            Next rngCell
        End If
    Next wsh
    ' Status, dictionary styles (key) has cell occurrence count (item)


    ' Try to delete unused styles
    Dim aKey As Variant
    On Error Resume Next    ' wb.Styles(aKey).Delete may throw error

    For Each aKey In dict.Keys

        ' display count & stylename
        '    e.g. "24   Normal"
        Debug.Print dict.Item(aKey) & vbTab & aKey

        If dict.Item(aKey) = 0 Then
            ' Occurrence count (Item) indicates this style is not used
            Call wb.Styles(aKey).Delete
            If Err.Number <> 0 Then
                Debug.Print vbTab & "^-- failed to delete"
                Err.Clear
            End If
            Call dict.Remove(aKey)
        End If

    Next aKey

    Debug.Print "ENDING # of style in workbook: " & wb.Styles.Count
    MsgBox "ENDING # of style in workbook: " & wb.Styles.Count

End Sub

回答by XLGeek

Lots of people seem to run into this problem.

很多人似乎遇到了这个问题。

Most often the issue is related to the excessive number of unused and often corrupted stylesand not so much the total count of the cell unique cell format combos.

大多数情况下，问题与​​过多的未使用和经常损坏的样式有关，而不是与单元格唯一单元格格式组合的总数有关。

I wrote a utility to fix XL2007 OOXML files that can be saved down to XL2003. Here is the linkto the blog post:

我编写了一个实用程序来修复可以保存到 XL2003 的 XL2007 OOXML 文件。这是博客文章的链接：

• Requires .Net3.5 and MS Excel 2007.
• Will fix xlsx or xlsm files.
• The post has a ReadMe file to go with the app.

• 需要 .Net3.5 和 MS Excel 2007。
• 将修复 xlsx 或 xlsm 文件。
• 该帖子有一个自述文件，可与该应用程序一起使用。

No need to run the risk of further corrupting your file by using Open Office like it is suggested on some other forums

无需像其他论坛上建议的那样，使用 Open Office 冒进一步损坏文件的风险

回答by Alex S

I had this problem, found the easiest way to clear it was using this Excel add in. It appears to be the "official" answer from the Microsoft page on the problem.

我遇到了这个问题，找到清除它的最简单方法是使用这个 Excel 插件。这似乎是Microsoft 页面上关于问题的“官方”答案。

For people that are as confused as I was at using .xlam files, after downloading it you do this in Excel:

对于那些像我一样在使用 .xlam 文件时感到困惑的人，下载它后，您可以在 Excel 中执行以下操作：

1. Click on File < Options < Add-ins.
2. Under Manage option click on Go.
3. In the Add-ins window click on Browse, browse to the location where the XLAM file is saved, highlight it and click Ok.
4. Enable the new add-in in the Add-ins window and click Ok.
5. On your home screen ribbon bar there should now be a "Remove Styles" section, which consists of a button showing the number of styles in the workbook (probably thousands if you're having this problem).
6. Click the button on the ribbon and it will eliminate all the duplicate formats.

1. 单击文件 < 选项 < 加载项。
2. 在管理选项下单击转到。
3. 在加载项窗口中单击浏览，浏览到保存 XLAM 文件的位置，突出显示它并单击确定。
4. 在加载项窗口中启用新加载项，然后单击确定。
5. 在您的主屏幕功能区栏上，现在应该有一个“删除样式”部分，其中包含一个显示工作簿中样式数量的按钮（如果您遇到此问题，可能有数千个）。
6. 单击功能区上的按钮，它将消除所有重复的格式。

If you don't see it, check that it's showing on the ribbon correctly. Right click on the ribbon and say "Customize Ribbon". Then click on Add-Ins. Under Active Application Add-ins you should see "Remove Extra Styles".

如果您没有看到它，请检查它是否正确显示在功能区上。右键单击功能区并说“自定义功能区”。然后点击加载项。在活动应用程序加载项下，您应该看到“删除额外样式”。

2019 Update:At that link they have now put everything in a big zip file with discussions about the issue and so on. In that zip file there is another zipped folder called source code, in that folder you can find the .xlam file.

2019 年更新：在那个链接上，他们现在将所有内容都放在一个大的 zip 文件中，并讨论了这个问题等等。在该 zip 文件中还有另一个名为 source code 的压缩文件夹，在该文件夹中您可以找到 .xlam 文件。

回答by XLGeek

The bugs that lead to "Too many cell formats" error message in Excel 2007+ have been addressed: http://sergeig888.wordpress.com/2011/05/06/msft-released-hot-fix-for-excel-2007-custom-styles-duplication/Note that the bug fixes will not remove pre-existing styles related file corruption. Open XML based tools (available for free) are the only option that can remove elements inaccessible to the Excel object model based tools: e.g., bad styles that disguised themselves as built-in, hidden styles, etc... Open XML based cleanup means 100% styles related corruption free files.

已解决导致 Excel 2007+ 中“单元格格式过多”错误消息的错误：http://sergeig888.wordpress.com/2011/05/06/msft-released-hot-fix-for-excel-2007 -custom-styles-duplication/请注意，错误修复不会删除与文件损坏相关的预先存在的样式。基于 Open XML 的工具（免费提供）是可以删除基于 Excel 对象模型的工具无法访问的元素的唯一选项：例如，将自己伪装成内置样式、隐藏样式等的不良样式......基于 Open XML 的清理意味着100% 样式相关的无损坏文件。

回答by LeasMaps

This will delete all the styles EXCEPT for the default styles (normal, Explanatory, 20% Accent1 etc). Including styles that the user has created but it's a quick and dirty way to clean up a workbook:

这将删除除默认样式（正常、解释、20% Accent1 等）之外的所有样式。包括用户创建的样式，但这是清理工作簿的一种快速而肮脏的方式：

---

---

Sub NewNukeStyles()
Dim tempstyle As Style

For Each tempstyle In ActiveWorkbook.Styles

 If tempstyle.BuiltIn = False Then
    If tempstyle.Locked = True Then 'not sure what this is
       tempstyle.Delete
    End If
End If

Next tempstyle

End Sub 'NukeStyles

I'd love to know what the tempstyle.locked property actually refers to other than "a Boolean value that indicates if the object is locked".

我很想知道 tempstyle.locked 属性实际上指的是什么，而不是“指示对象是否被锁定的布尔值”。

回答by Joe Erickson

SpreadsheetGear for .NETwill coallesce non-unique formats which may help with your situation.

SpreadsheetGear for .NET将合并可能对您的情况有所帮助的非唯一格式。

You can download the free trial hereif you want to see whether it helps. Just load the workbook into the "SpreadsheetGear 2009 for Windows" application which is installed with the evaluation software and then save the workbook.

如果您想看看它是否有帮助，您可以在此处下载免费试用版。只需将工作簿加载到随评估软件一起安装的“SpreadsheetGear 2009 for Windows”应用程序中，然后保存工作簿。

If you actually have that many unique formats, you will have to simplify. Every unique combination of font / cell color (Interior), number format, horizontal and vertical alignment, borders, indent level, and probably a few things I'm not thinking of will cause a unique entry in the table.

如果你真的有这么多独特的格式，你将不得不简化。字体/单元格颜色（内部）、数字格式、水平和垂直对齐、边框、缩进级别以及我可能没有想到的一些事情的每个独特组合都会在表格中产生一个独特的条目。

Another option is to switch to Excel 2007 which has the limit on unique cell formats increased from 4,000 to 64K.

另一种选择是切换到 Excel 2007，它将唯一单元格格式的限制从 4,000 增加到 64K。

Disclaimer: I own SpreadsheetGear LLC

免责声明：我拥有 SpreadsheetGear LLC

回答by FinancialRadDeveloper

I have seen this problem before. It is possible to recreate to prove what I am abaout to say. It is a little bit of a catch 22 but when you have a sheet that has the 'Too many formats' problem, open a brand new sheet and copy one cell from the too many formats sheet and simply paste it into the new workbook. This workbook will now be 'Infected' as it were and will also have the too many formats error. It seems that a lot of the format data comes over with that paste unless you use a paste special to restrict it.

我以前见过这个问题。可以重新创建以证明我要说的话。这有点困难 22 但是当您的工作表存在“格式过多”问题时，请打开一个全新的工作表并从格式过多的工作表中复制一个单元格，然后将其粘贴到新工作簿中。该工作簿现在将被“感染”，并且还会出现格式过多的错误。除非您使用特殊粘贴来限制它，否则似乎很多格式数据都带有该粘贴。

How to make things better? Well a workaround is to use the Save As HTML function. Bear with me though this does work. I am talking about 2003 here, I don't know what happens in 2007, maybe this bug has been fixed. So ... Save as Html then close excel. Load a new session and load this in making sure you don'trun any macros and then once loaded save as a spreadsheet.

如何让事情变得更好？一种解决方法是使用另存为 HTML 功能。尽管这确实有效，但请耐心等待。我这里说的是 2003 年，我不知道 2007 年会发生什么，也许这个 bug 已经修复了。所以......另存为Html然后关闭excel。加载一个新会话并加载它以确保您不运行任何宏，然后加载后另存为电子表格。

When you close and reopen this new spreadsheet your Too Many Formats woes should be gone.

当您关闭并重新打开这个新电子表格时，您的太多格式问题应该消失了。

回答by Barbara Wiseman

One solution may be to use ASAP utilities. In the sheet section there is a remove all unused stylesoption. You then have to close the workbook and re-open, I think.

一种解决方案可能是使用ASAP 实用程序。在工作表部分有一个remove all unused styles选项。然后你必须关闭工作簿并重新打开，我想。

回答by Zach Shaw

If you can open the file Try > edit > clear > Formats.

如果您可以打开文件，请尝试 > 编辑 > 清除 > 格式。

Highlight the sheet before hitting formats it worked for me.

在点击对我有用的格式之前突出显示工作表。

make sure you make a copy of the EXCEL before doing this just incase something is missing you can cross ref against the old document.

确保在执行此操作之前制作 EXCEL 的副本，以防万一缺少某些内容，您可以对旧文档进行交叉引用。