☰

我在 Excel 2013 中不断收到运行时错误 13

tag-icon • tag-icon microsoft-excel vba
我在 Excel 2013 中不断收到运行时错误 13

下面的代码一开始运行良好，但如果我从单元格中删除所有数据，我会收到错误“错误 13”，然后代码停止工作。有什么办法可以解决这个问题吗？我已将代码包含在以下代码中：

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Application.EnableEvents = False

If Target.Column = 1 Then
    Target = StrConv(Target, vbProperCase)
End If
Application.EnableEvents = True

If Target.Column = 3 Then
    Target = StrConv(Target, vbProperCase)
End If
Application.EnableEvents = True

If Target.Column = 6 Then
    Target = StrConv(Target, vbProperCase)
End If
Application.EnableEvents = True

If Target.Column = 7 Then
    Target = StrConv(Target, vbProperCase)
End If
Application.EnableEvents = True

End Sub

答案1

当您删除一行时，您的代码在第Target = StrConv(Target, vbProperCase)(Col 1)行出现错误

您正在更改行中的所有单元格，因此目标包含多个单元格

  • StrConv()只接受一个字符串
  • 在你犯错之前Application.EnableEvents = False
  • 发生错误后，代码停止并且无法执行Application.EnableEvents = True

所以现在所有活动都取消了

此版本将检查以下可能性：

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    Application.EnableEvents = False
    With Target
        If .CountLarge = 1 Then            'one cell
            Select Case .Column
                Case 1, 3, 6, 7:
                    If Not IsError(.Value2) Then
                        If Len(.Value2) > 0 Then .Value2 = StrConv(.Value2, vbProperCase)
                    End If
            End Select
        Else
            Dim cel As Range, ur As Range
            For Each cel In Target         'multiple cells (copy/paste, del/insert rows/cols)
                With cel
                    Set ur = UsedRange
                    If .Row > ur.Rows.Count Or .Column > ur.Columns.Count Then Exit For
                    Select Case .Column
                        Case 1, 3, 6, 7:
                            If Not IsError(.Value2) Then
                                If Len(.Value2) > 0 Then
                                    .Value2 = StrConv(.Value2, vbProperCase)
                                End If
                            End If
                    End Select
                End With
            Next
        End If
    End With
    Application.EnableEvents = True
End Sub

Worksheet_SelectionChange()这也很好用

相关内容