时间戳

首页 > 解决方案 > VBA excel中“Case Else”优先于其他“Case”条件的问题

excel - VBA excel中“Case Else”优先于其他“Case”条件的问题

问题描述

我遇到的问题似乎Case Else优先于其他Case条件。

宏的想法取决于工作表名称，应用另一个单独的宏。我有大约 8 张表，其中每张表都有特定的宏，然后对于剩余的表（大约 20 张），我使用Case Else并应用单独的宏。这个问题是当最后有一个Case Else函数时，其他Case的都没有应用。当我删除时Case Else，正确的宏将应用于工作表。就好像Case Else成为默认/优先。我尝试了以下应用单独宏的替代方法，但问题仍然存在：

Sub Specify()

    Dim Fun As String
    Dim sht As Worksheet

    For Each sht In Worksheets
        Select Case sht.Name
            Case "NB12", "NB15"
                Application.Run "Groundwater_Macros.xlsm!limits_Alluvium"
            Case "NB24"
                Application.Run "Groundwater_Macros.xlsm!limits_BOCOBOML_GFA"
            Case "NB16", "NB17", "NB19", "NB20", "Bore 31"
                Application.Run "Groundwater_Macros.xlsm!limits_BOCOBOML_MIA"
            Case "Bore 47", "Bore 48"
                Application.Run "Groundwater_Macros.xlsm!limits_FracturedRock_GFA"
            Case "Bore 4", "Bore 4a", "Bore 40"
                Application.Run "Groundwater_Macros.xlsm!limits_FracturedRock_MIA_West"
            Case "Bore 30"
                Application.Run "Groundwater_Macros.xlsm!limits_FracturedRock_MIA_East"
            Case Else
                Application.Run "Groundwater_Macros.xlsm!limits_Monitoring_bores"
                Debug.Print sht.Name

        End Select
    Next sht

End Sub

Sub Specify_test()


    Dim Fun As String
    Dim sht As Worksheet

    For Each sht In Worksheets
        Select Case sht.Name
            Case "NB12", "NB15"
                Call limits_Alluvium
            Case "NB24"
                Call limits_BOCOBOML_GFA
            Case "NB16", "NB17", "NB19", "NB20", "Bore 31"
                Call limits_BOCOBOML_MIA
            Case "Bore 47", "Bore 48"
                Call limits_FracturedRock_GFA
            Case "Bore 4", "Bore 4a", "Bore 40"
                Call limits_FracturedRock_MIA_West
            Case "Bore 30"
                Call limits_FracturedRock_MIA_East
            Case Else
                Call limits_Monitoring_bores
                If Len(Fun) Then Fun = Fun & vbCr
                Fun = Fun & sht.Name
        End Select
    Next sht
    MsgBox Fun, vbInformation, "Sheets not processed"


End Sub

Sub Specify_test2()


    Dim Fun As String
    Dim sht As Worksheet

    For Each sht In Worksheets
        'Select Case sht.Name
            If sht.Name = "NB12" Or sht.Name = "NB15" Then
                limits_Alluvium
            ElseIf sht.Name = "NB24" Then
                limits_BOCOBOML_GFA
            ElseIf sht.Name = "NB16" Or sht.Name = "NB17" Or sht.Name = "NB19" Or sht.Name = "NB20" Or sht.Name = "Bore 31" Then
                limits_BOCOBOML_MIA
            ElseIf sht.Name = "Bore 47" Or sht.Name = "Bore 48" Then
                limits_FracturedRock_GFA
            ElseIf sht.Name = "Bore 4" Or sht.Name = "Bore 4a" Or sht.Name = "Bore 40" Then
                limits_FracturedRock_MIA_West
            ElseIf sht.Name = "Bore 30" Then
                limits_FracturedRock_MIA_East
            Else
                limits_Monitoring_bores
            End If
        'End Select
    Next sht

End Sub

Sub Specify_test3()

Dim Fun As String
Dim sht As Worksheet

For Each sht In Worksheets
    Select Case sht.Name
    Case "NB12", "NB15": Call limits_Alluvium
    Case "NB24": Call limits_BOCOBOML_GFA
    Case "NB16", "NB17", "NB19", "NB20", "Bore 31": Call limits_BOCOBOML_MIA
    Case "Bore 47", "Bore 48": Call limits_FracturedRock_GFA
    Case "Bore 4", "Bore 4a", "Bore 40": Call limits_FracturedRock_MIA_West
    Case "Bore 30": Call limits_FracturedRock_MIA_East
    Case Else: Call limits_Monitoring_bores
    End Select
Next sht

End Sub

我已经对工作表名称进行了故障排除。我认为这不是问题，因为何时Case Else省略，应用了正确的宏。

标签： excelvba

解决方案

执行意外倒退Case Else是一个症状，一个线索。

For Each sht In Worksheets

这个对象究竟Sheets属于哪个特定的工作簿，取决于太多的事情来可靠地工作。

如果代码在ThisWorkbook模块中，那么我们应该明确表示：

For Each sheet In Me.Worksheets

但是该代码不能在ThisWorkbook模块中，否则我们就不会在这里，所以...

如果我们在任何其他模块中并且我们想要在 中迭代工作表ThisWorkbook，那么我们将使用工作簿的代码名称作为限定符：

For Each sheet In ThisWorkbook.Worksheets

如果我们在除 之外的任何模块中ThisWorkbook，那么Worksheets本质上只有ActiveWorkbook.Worksheets，并且如果我们不知道我们正在从哪个工作簿迭代工作表，那么我们有可能不会迭代我们认为是活动工作簿的工作表;-)

有一个Rubberduck 检查可以ActiveWorkbook为您找到所有这些隐含/不合格的引用（免责声明：我管理 Rubberduck 项目）。

推荐阅读