带有VBA代码的excel保存后，部分内容已损坏

大明湖的狗凯.

已于 2024-11-25 10:00:38 修改

阅读量387

点赞数 5

文章标签： excel

于 2024-11-01 14:34:38 首次发布

本文链接：https://blog.csdn.net/weixin_69477306/article/details/143431073

版权

在当今数字化办公的浪潮中，Excel 作为一款强大的电子表格软件，广泛应用于各类数据处理与分析工作。而 VBA（Visual Basic for Applications）则为 Excel 增添了强大的自动化与定制化功能，使我们能够根据特定需求开发各种复杂的应用程序。然而，在使用 Excel VBA 进行代码开发的过程中，难免会遇到一些棘手的问题。近期我在工作中就遭遇了这样一个令人困扰的 Excel 异常情况，在此将整个问题的发现、分析与解决过程详细分享，希望能为广大 Excel VBA 开发者提供有益的参考与借鉴。

在日常的 Excel VBA 开发工作中，我所负责的项目涉及到大量数据的处理与交互，其中包括了动态生成数据验证下拉菜单以方便用户进行数据选择与录入。起初，Excel 开始频繁出现一些异常现象，例如文件打开缓慢、操作响应延迟，甚至有时会出现无响应的死机状态。这严重影响了工作效率，也给数据的准确性与完整性带来了潜在风险。

为了解决这些问题，我首先尝试了一些常规的排查方法。我检查了计算机的硬件资源，包括内存、CPU 使用率等，发现均处于正常水平，排除了硬件性能不足导致的问题。接着，我对 Excel 的各项设置进行了详细检查与调整，如重新计算选项、显示设置、加载项管理等，但遗憾的是，这些努力并没有让问题得到解决。

不过，在更换 Excel 版本后，情况出现了转机。Excel 给出了相对详细的修复内容提示：“Removed feature: Data validation from/xl/worksheets/sheet1.xml part”。这个提示成为了我进一步探究问题的关键线索。

经过大量查阅资料和反复试验，我终于发现了问题所在。在 Excel 中，如果存在动态生成的数据验证（这里主要是指下拉菜单的形式），无论是通过公式生成，还是使用 VBA 代码生成，都有可能引发这个问题。而且，这种情况在保存 Excel 文件后重新启动时就会出现。除此之外，我还发现了另一个可能导致问题的因素，那就是当下拉菜单中的字符数超过 255 时，也有很大的概率会出现这种异常情况。

深入分析数据验证引发问题的原因，主要是由于动态生成的数据验证在 Excel 文件的内部结构中可能会产生一些复杂的关联与引用。当文件保存与重新加载时，这些关联与引用的处理可能会出现异常，尤其是在下拉菜单内容较长或数据验证规则较为复杂的情况下，更容易导致 Excel 在解析与加载数据时出现错误，从而引发各种性能问题与异常现象。

最近在工作中，进行excel的vba代码开发，遇到一个奇怪的问题；excel时常出现如下情况。

然而，令人失望的是，这些努力并没有让问题得到解决。不过，在更换版本后，Excel 给出了相对详细的修复内容提示：“Removed feature: Data validation from/xl/worksheets/sheet1.xml part”。这个提示成为了我进一步探究问题的关键线索。

既然找到了问题的原因，接下来就是寻找解决方案了。经过研究，我找到了一个有效的解决办法，具体操作如下：

我们需要在 VBA 编译器的 thisworkbook 模块中添加一段代码，这段代码的作用是在关闭此 Excel 文件时，自动删除表中的所有数据验证。以下是详细的代码内容：

' 以下代码需要放在 ThisWorkbook 模块中
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' 定义工作表对象 ws，以及用于循环的变量 i 和 j
    Dim ws As Worksheet
    Dim i As Long
    Dim j As Long

    ' 开始遍历工作簿中的每一个工作表
    For Each ws In ThisWorkbook.Worksheets
        ' 忽略错误继续执行下面的代码，这是为了防止在处理某些单元格时出现错误而中断程序
        On Error Resume Next
        ' 这里的两个嵌套循环是为了遍历工作表中的每一个单元格
        ' 外层循环控制列，从第 1 列到第 60 列（可根据实际情况调整范围）
        For i = 1 To 60
            ' 内层循环控制行，从第 1 行到第 2000 行（可根据实际情况调整范围）
            For j = 1 To 2000
                ' 针对每个单元格进行操作
                With ws.Cells(j, i)
                    ' 判断单元格的数据验证类型是否为下拉列表验证（xlValidateList）
                    If.Validation.Type = xlValidateList Then
                        ' 如果是下拉列表验证，则删除该数据验证
                      .Validation.Delete
                    End If
                End With
            Next j
        Next i
        ' 恢复正常的错误处理机制
        On Error GoTo 0
    Next ws

    ' 保存工作簿，这一步非常重要，如果不保存，之前的删除数据验证操作就不会生效
    ThisWorkbook.Save
End Sub

在这里一定要特别注意，最后一定要保存工作簿。因为如果不保存，那么前面编写的删除数据验证的代码就相当于没有执行，问题依然会存在。希望这个解决方案能够帮助到其他遇到类似问题的开发者，避免在这个问题上花费过多的时间和精力。