選擇案例語句中的數組是否可能？ (Is an array inside a select case statement possible?)

問題描述

選擇案例語句中的數組是否可能？ (Is an array inside a select case statement possible?)

我只是想知道我是否可以分配一個數組並將其用作 case 語句中的限定符？

Sub AccountCopy()
Dim Criteria1 As Variant
Dim Criteria2 As Variant
Dim Acct As Variant
Dim NR As Integer

Criteria1 = Array("Checking", "Savings")
Criteria2 = Array("Loans", "Credit Card")

MonthSheet.Range("T1") = "Title"
MonthSheet.Range("U1") = "Account"
MonthSheet.Range("V1") = "Description"
MonthSheet.Range("W1") = "Amount"
MonthSheet.Range("X1") = "Date"
MonthSheet.Range("Y1") = "Category"

With Range("T1:Y1")
    .Font.Name = "Calibri"
    .Font.Size = 8
    .Font.Bold = True
    .HorizontalAlignment = xlCenter
    .Style = "Title"
    .Columns.AutoFit
End With



For Each Acct In [AccountNameList]
    Select Case Acct.Offset(0, 1).Value
        Case Is = Criteria1
            NR = Range("T" & Rows.Count).End(xlUp).Row + 1 'Next Row
            'MonthSheet.Range 
        Case Criteria2
    End Select

不要太嚴厲地批評我，我仍然很漂亮對此很陌生。我不經常在fourms上發帖，但他們是一些非常有才華的人，我想問誰比那些已經編碼多年的人更好？提前致謝！

這就是我想要完成的：我想將“Criteria1”數組定義為我想要的維度。也許我想在列表中添加第三個標準。與其去改變 case 語句，我寧願在後面的行中添加到數組中以包含那個額外的限定符。也許我設置了錯誤的類型？我不 不知道？我覺得這可以很容易地完成，但我錯過了一個非常小的細節。

參考解法

方法 1:

Some remarks:

1. You are defining a 1 dimension array and you don't need a two/three dimensional array to accomplish what you want. Check Mathieu's tutorial to learn about this
2. Always fully qualify the objects to avoid code unexpected behaviors
3. Indent your code (you can use the free www.rubberduckvba.com to help you with that)
4. What you want is totally doable...in different ways, one is just use a helper function to find the match in an array.

Read the code's comments and adjust it to fit your needs

Helper function option

Option Explicit

Public Sub AccountCopy()

    Dim Criteria1 As Variant
    Dim Criteria2 As Variant
    Dim Acct As Variant ' This can be declared as a Range
    Dim NR As Integer ' Better to name the variables to something readable like: newRow

    ' Added this new variable
    Dim accountValue As String

    Criteria1 = Array("Checking", "Savings")
    Criteria2 = Array("Loans", "Credit Card")

    MonthSheet.Range("T1") = "Title"
    MonthSheet.Range("U1") = "Account"
    MonthSheet.Range("V1") = "Description"
    MonthSheet.Range("W1") = "Amount"
    MonthSheet.Range("X1") = "Date"
    MonthSheet.Range("Y1") = "Category"

    ' This next line should be something like: <TheCodeNameOfTheSheet>.Range("T1:Y1") to be fully qualified
    With Range("T1:Y1")
        .Font.Name = "Calibri"
        .Font.Size = 8
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
        .Style = "Title"
        .Columns.AutoFit
    End With


    ' In here, it's easier to understand if you use Thisworkbook.Names("AccountNameList")
    For Each Acct In [AccountNameList]

        ' Store the account next cell's value
        accountValue = Acct.Offset(0, 1).Value

        Select Case True
        Case IsInArray(accountValue, Criteria1)
            ' Do something
            Debug.Print "Something", Acct.Offset(0, 1).Address
        Case IsInArray(accountValue, Criteria2)
            ' Do something else
            Debug.Print "Something else", Acct.Offset(0, 1).Address
        End Select

    Next Acct

End Sub

Public Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
    ' Credits: https://stackoverflow.com/a/11112305/1521579
    IsInArray = (UBound(Filter(arr, stringToBeFound)) > ‑1)
End Function

Another approach is to loop through the array items

with something like this...

Add this inside the for each loop

needs a dim counter as long somewhere in the code

For counter = 0 To UBound(Criteria1)
    If Acct.Offset(0, 1).Value = Criteria1(counter) Then
        ' Do something
        Debug.Print "Something", Acct.Offset(0, 1).Address
    End If
Next counter

For counter = 0 To UBound(Criteria2)
    If Acct.Offset(0, 1).Value = Criteria2(counter) Then
        ' Do something else
        Debug.Print "Something else", Acct.Offset(0, 1).Address
    End If
Next counter

(by ToxicLove719、Ricardo Diaz)

參考文件

1. Is an array inside a select case statement possible? (CC BY‑SA 2.5/3.0/4.0)