SELECT CASE 中的 EXCEL VBA WorksheetFunction.CountIf()

EXCEL VBA WorksheetFunction.CountIf() in a SELECT CASE

提问by 4 Leave Cover

I know that it is possible to use Ifstatement but out of curiosity, as mentioned in the title, is it possible to use SELECTstatement to do something as BOLDEDbelow? I've submitted my whole Subas below for better understanding:

我知道可以使用Ifstatement 但出于好奇，正如标题中提到的，是否可以使用SELECTstatement 来做下面加粗的事情？Sub为了更好地理解，我已经提交了我的全文如下：

Sub addNewCust_Click()

子 addNewCust_Click()

Dim response As String

response = Application.InputBox(prompt:="", Title:="New customer name", Type:=2)

Select Case response
Case False
    Exit Sub

'Check if response is not an empty value AND record found in "CustomerList"

Case Is <> "" & WorksheetFunction.CountIf(Worksheets("CustomerList").Range("B:B"), response) > 0

Case Is <> "" & WorksheetFunction.CountIf(Worksheets("CustomerList").Range("B:B"), response) > 0

    MsgBox "'" & response & "' already exists on this sheet."
    Call addNewCust_Click

'Check if response is not an empty value and record is not found in "Customerlist"

Case Is <> "" & WorksheetFunction.CountIf(Worksheets("CustomerList").Range("B:B"), response) < 1

Case Is <> "" & WorksheetFunction.CountIf(Worksheets("CustomerList").Range("B:B"), response) < 1

    Sheets("CustomerList").Range("B1048576").End(xlUp).Offset(1, 0).Value = response
    MsgBox "'" & response & "' successfully entered!"**

Case Else
        MsgBox "Field is empty!"
        Call addNewCust_Click

End Select

End Sub

采纳答案by Siddharth Rout

Like this?

像这样？

Sub addNewCust_Click()
    Dim response As String

    response = Application.InputBox(prompt:="", Title:="New customer name", Type:=2)

    Select Case response
    Case False: Exit Sub    
    'Check if response is not an empty value AND record found in "CustomerList"
    Case Is <> ""
        If WorksheetFunction.CountIf(Worksheets("CustomerList").Range("B:B"), response) > 0 Then
            MsgBox "'" & response & "' already exists on this sheet."
            Call addNewCust_Click
        Else
            Sheets("CustomerList").Range("B1048576").End(xlUp).Offset(1, 0).Value = response
            MsgBox "'" & response & "' successfully entered!"
        End If
    Case Else
        MsgBox "Field is empty!"
        Call addNewCust_Click
    End Select
End Sub

FOLLOWUP (From Comments)

跟进（来自评论）

Select Caseis considered to be faster than If-Endifbut for such a small scenario, the efficiency comparison is futile. What is more important is how you write the code

Select Case被认为是更快If-Endif但对于这么小的场景，效率比较是徒劳的。更重要的是你怎么写代码

Below is another way. I love this way as things are broken down into smaller parts and everything is declared properly. I am not touching error handling below. See this for detailed analysis.

下面是另一种方式。我喜欢这种方式，因为事情被分解成更小的部分，并且一切都被正确地声明。我没有触及下面的错误处理。详细分析请看这里。

The below method is useful because

下面的方法很有用，因为

1. when you are looking at your code (say maybe after an year) and you know exactly what is happening since the code is commented.
2. Easy to maintain the code. For example if the Sheet name changes then you have to change it only at one place. The alternative is to also use Codenames
3. You can use the same code across all Excel platforms. If you hardcode your range, Ex: Range("B1048576")then the above code will not work in Excel 2003.

1. 当您查看您的代码时（比如可能在一年之后）并且您确切地知道代码被注释后发生了什么。
2. 易于维护代码。例如，如果工作表名称更改，则您只需在一处更改它。另一种方法是也使用Codenames
3. 您可以在所有 Excel 平台上使用相同的代码。如果您对范围进行硬编码，例如：Range("B1048576")那么上面的代码在 Excel 2003 中将不起作用。

Sample Code

示例代码

Sub addNewCust_Click()
    Dim ws As Worksheet
    Dim Lrow As Long
    Dim response

    '~~> Set the relevant worksheet
    Set ws = ThisWorkbook.Worksheets("CustomerList")

    With ws
        Do
            '~~> Get user response
            response = Application.InputBox(prompt:="", Title:="New customer name", Type:=2)

            Select Case response
                Case False: Exit Sub    '<~~ If user presses cancel or closes using 'X'
                Case "": MsgBox "Field is empty!" '<~~ If user enters a blank entry
                Case Else
                    '~~> Check if the entry exists
                    If WorksheetFunction.CountIf(.Range("B:B"), response) > 0 Then
                        MsgBox "'" & response & "' already exists on this sheet."
                    Else
                        '~~> Get last Row
                        Lrow = .Range("B" & .Rows.Count).End(xlUp).Row + 1
                        '~~> Add the new entry
                        .Range("B" & Lrow).Value = response
                        MsgBox "'" & response & "' successfully entered!"
                        Exit Do 'OR Exit Sub (As Applicable)
                    End If
            End Select
        Loop
    End With
End Sub