excel疑难千寻千解丛书_Excel数据验证疑难解答

excel疑难千寻千解丛书

How many data validation rules do you have in the giant Excel workbook that you use every morning? If you inherited that file from somebody else, you might not have any idea what's on some of the sheets. Your workbook could be slowing down, or maybe it's even crashing, and you're not sure why. Use these macros for data validation troubleshooting, and see if you need to clean things up.

您每天早晨使用的巨型Excel工作簿中有多少个数据验证规则？ 如果您从其他人那里继承了该文件，则您可能不知道某些工作表上的内容。 您的工作簿可能会变慢，甚至崩溃，并且您不确定为什么。 使用这些宏进行数据验证疑难解答，并查看是否需要清理。

数据验证过多 (Too Much Data Validation)

Recently, someone sent me a workbook that was crashing frequently. They were trying to use my Data Entry PopUp List kit, and thought that might be the problem.

最近，有人给我发送了一本经常崩溃的工作簿。 他们试图使用我的“数据输入弹出列表”工具包 ，并认为这可能是问题所在。

The workbook was small – they had removed most of the data, for privacy reasons.  But, when I tried to save the file, it crashed.

该工作簿很小-出于隐私原因，他们已删除了大部分数据。 但是，当我尝试保存文件时，它崩溃了。

So, I opened the file again, and ran a macro to list all the sheets, with details of what was on each sheet. Can you spot the problem?

因此，我再次打开文件，并运行一个宏以列出所有工作表，并详细说明每个工作表上的内容。 你能发现问题吗？

That's right – over 5 million cells that have data validation! Instead of adding the rules to just the columns in an Excel table, entire worksheet columns had the rules.

是的-超过500万个具有数据验证功能的单元！ 整个工作表列都具有规则，而不是将规则仅添加到Excel表中的列。

A little data validation can be a good thing – it controls what people can enter on the worksheet. But this much DV was definitely not a good thing!

进行一点数据验证可能是一件好事–它控制了人们可以在工作表上输入的内容。 但是这么多的DV绝对不是一件好事！

So, I removed all the unnecessary data validation rules, and the workbook stopped crashing.

因此，我删除了所有不必要的数据验证规则，并且工作簿停止崩溃。

数据验证故障排除 (Data Validation Troubleshooting)

There is a macro in the next section, and you can use it for your data validation troubleshooting.

下一节有一个宏，您可以将其用于数据验证故障排除。

The macro adds a new sheet in the active workbook. On that sheet, it lists all the other sheets, with a summary of the information on each sheet:

该宏在活动工作簿中添加一个新工作表。 在该工作表上，它列出了所有其他工作表，并汇总了每个工作表上的信息：

• Order
  订购
• Sheet Name
  工作表名称
• Used Range Address
  使用范围地址
• Count of cells in Used Range
  使用范围内的单元数
• Count of data validation cells
  数据验证单元数

Here's a screen shot of the list from one of my sample files. I put data validation in the full column on the DV03 sheet, and you can see the high count.

这是我的一个示例文件中的列表的屏幕截图。 我将数据验证放在DV03表格的整列中，您可以看到计数很高。

列出所有图纸详细信息宏的代码 (Code for the List All Sheet Details Macro)

Here's the code for the macro that creates the list of sheet details – store it in a regular code module in your workbook. There are instructions on my Contextures website.

这是用于创建工作表详细信息列表的宏的代码-将其存储在工作簿的常规代码模块中。 我的Contextures网站上有说明 。

Sub DataValSummary()
  Dim ws As Worksheet
  Dim lCount As Long
  Dim wsTemp As Worksheet
  Dim rngF As Range
  Dim lFields As Long
  Dim lTab As Long
  Dim rngDV As Range
  Dim vDV As Variant
  Dim strNA As String
  Dim strSh As String
Application.EnableEvents = False
Application.ScreenUpdating = False
On Error Resume Next
  
  Set wsTemp = Worksheets.Add(Before:=Sheets(1))
  lCount = 2
  lFields = 5 'not tab color
  strNA = " --"
  
  With wsTemp
    .Range(.Cells(1, 1), .Cells(1, lFields)).Value _
          = Array( _
              "Order", _
              "Sheet Name", _
              "Used Range", _
              "Range Cells", _
              "DV Cells")
  End With
  
  For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> wsTemp.Name Then
      If ws.ProtectContents = True Then
        vDV = strNA
        strSh = strNA

      Else
        Set rngDV = Nothing
        vDV = 0
        Set rngDV = ws.Cells.SpecialCells(xlCellTypeAllValidation)
        If Not rngDV Is Nothing Then
          vDV = rngDV.Cells.Count
        End If
      End If
      
      With wsTemp
        .Range(.Cells(lCount, 1), .Cells(lCount, lFields)).Value _
          = Array( _
              ws.Index, _
              ws.Name, _
              ws.UsedRange.Address, _
              ws.UsedRange.Cells.Count, _
              vDV)
        'add hyperlink to sheet name in column B
        .Hyperlinks.Add _
            Anchor:=.Cells(lCount, 2), _
            Address:="", _
            SubAddress:="'" & ws.Name & "'!A1", _
            ScreenTip:=ws.Name, _
            TextToDisplay:=ws.Name
        lCount = lCount + 1
      End With
      
    End If
  Next ws
 
With wsTemp
    With .Range(.Cells(1, 1), .Cells(1, lFields + 2))
      .EntireColumn.AutoFit
      .AutoFilter
    End With
    .Rows(1).Font.Bold = True
End With

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

列出所有数据验证规则 (List All Data Validation Rules )

After you run that macro, you might spot a problem right away, and be able to fix it.  If not, there are more suggestions and tools on the Data Validation Troubleshooting page on my Contextures website.

运行该宏后，您可能会立即发现问题并能够解决它。 如果没有，在我的Contextures网站上的“ 数据验证疑难解答”页面上有更多建议和工具。

For example, use the macros from that page, to list all data validation rules on the active worksheet, so you can see the cell address, the data validation type, and the formulas.

例如，使用该页面上的宏在活动工作表上列出所有数据验证规则，这样您就可以看到单元格地址，数据验证类型和公式。

Don't run those macro on a sheet with millions of data validation cells though – get rid of any unnecessary DV cells first. Otherwise, Excel might melt into a puddle.

但是，不要在具有数百万个数据验证单元的工作表上运行这些宏-首先清除所有不必要的DV单元。 否则，Excel可能会陷入困境。

带有清单的新表 (New Sheet with List)

One macro adds a new sheet to the workbook, and lists the details there.

一个宏将新的工作表添加到工作簿，并在其中列出详细信息。

带列表的文本文件 (Text File with List)

The other macro lists the details in a text file, created in your default file save location.

另一个宏在文本文件中列出了详细信息，该文本文件是在默认文件保存位置创建的。

下载工作簿 (Download the Workbook)

To download the data validation troubleshooting workbook, go to the Data Validation Troubleshooting page on my Contextures site.

要下载数据验证故障排除工作簿，请转到Contextures网站上的“ 数据验证故障排除”页面 。

The zipped file is in xlsm format, and contains all three data validation troubleshooting macros. When you open the workbook, be sure to enable macros, if you want to create the lists.

压缩文件为xlsm格式，包含所有三个数据验证疑难解答宏。 打开工作簿时，如果要创建列表，请确保启用宏。

使用Excel工具进行故障排除 (Troubleshooting with Excel Tools)

If you have a copy of my Excel Tools add-in, it has a command to create a list of all the data validation on the active sheet. (It has lots of other troubleshooting tools too!)

如果您有我的Excel Tools加载项的副本，它将有一个命令来创建活动工作表上所有数据验证的列表。 (它也有许多其他故障排除工具！)

When you click that command, a message appears, asking if you want to list the rules for the selected cells only.

当您单击该命令时，将显示一条消息，询问您是否仅列出所选单元格的规则。

If you click No, the list will show the rules for the entire active worksheet.

如果单击“否”，列表将显示整个活动工作表的规则。

The Excel Tools add-in also has commands to create lists of sheet contents, such as the summary list shown below. This list shows the data validation cell counts, as well as formula counts, used range address, tab colour, and other details.

Excel Tools加载项还具有创建图纸内容列表的命令，例如下面显示的摘要列表。 此列表显示数据验证单元格计数以及公式计数，使用的范围地址，选项卡颜色和其他详细信息。

翻译自: https://contexturesblog.com/archives/2017/11/09/excel-data-validation-troubleshooting/

excel疑难千寻千解丛书