选择请求返回值的案例函数

Question

我正在尝试编写"选择案例"函数，一个用于两个 excel 工作表，一个用于一系列单元格。这些函数用于为排序过程的工作表和范围编制索引。但是，函数要求我返回值，我不确定要返回什么值。我无法返回我的参数。基本上，我希望我的排序子遍历两个工作表和两个范围，并对函数中选择范围内的工作表进行排序。

如果有更好的方法，我也愿意接受建议。

这是我的代码：

Dim xlWB As Excel.Workbook = CType(Globals.ThisWorkbook.Application.ActiveWorkbook, Excel.Workbook)
Dim xlWSEmployee As Excel.Worksheet = CType(CType(xlWB.Sheets("byEmployee"), Excel.Worksheet), Excel.Worksheet)
Dim xlWSPosition As Excel.Worksheet = CType(CType(xlWB.Sheets("byPosition"), Excel.Worksheet), Excel.Worksheet)
    Public Function SheetsToSort(Index As Long) As Excel.Worksheet
            Select Case Index
                Case 1 : SheetsToSort = xlWSEmployee
                Case 2 : SheetsToSort = xlWSPosition

            End Select
            ***Return (?)***
        End Function
        Public Function GetRange(index As Long) As Excel.Range
            Select Case index
                Case 1 : GetRange = xlWSEmployee.Range("A1")
                Case 2 : GetRange = xlWSPosition.Range("W1")

            End Select
            ***Return (?)***
        End Function
        Sub Sort_Sheets()

            Dim refSheets As Excel.Worksheet
            Dim sortRange As Excel.Range
            Dim x As Long

            'This is Step 6 when the frmDataImportSplash is activated.
            'The key formulas only work if the data is sorted properly. The procedure
            'below sorts each sheet by the required field. Look at the public_Declarations module
            'for the pass byRef.
            For x = 1 To 2 Step 1
                refSheets = SheetsToSort(x)
                sortRange = GetRange(x)
                With refSheets
                    .Sort.SortFields.Clear()
                    .Sort.SortFields.Add(Key:=(refSheets.sortRange), SortOn:=XlSortOn.xlSortOnValues, Order:=Excel.XlSortOrder.xlAscending, DataOption:=XlsortDataoption.xlSortNormal)
                    With .Sort
                        .Header = XlYesNoGuess.xlYes
                        .MatchCase = False
                        .Orientation = Excel.XlSortOrientation.xlSortColumns
                        .SortMethod = Excel.XlSortMethod.xlPinYin
                        .Apply()

                    End With
                End With
            Next x
        End Sub

Answer 1

尝试使用 Return 语句而不是旧语句FunctionName = ReturnValue，然后，如果您跳过 Select 语句，那一定是一个错误，肯定吗？

Public Function SheetsToSort(Index As Long) As Excel.Worksheet
   Select Case Index
       Case 1 : Return xlWSEmployee
       Case 2 : Return xlWSPosition
   End Select
   Throw New ArgumentOutOfRangeException("Index")
End Function
Public Function GetRange(index As Long) As Excel.Range
   Select Case index
       Case 1 : Return xlWSEmployee.Range("A1")
       Case 2 : Return xlWSPosition.Range("W1")
   End Select
   Throw New ArgumentOutOfRangeException("index")
End Function