arrays - 将数组传递给函数时，Variant 与 String 类型不匹配

Question

我有一个函数可以返回当前工作表的列表:

Function getListOfSheetsW() As Variant
  Dim i As Integer
  Dim sheetNames() As Variant

  ReDim sheetNames(1 To Sheets.Count)
  For i = 1 To Sheets.Count
    sheetNames(i) = Sheets(i).name
  Next i

  getListOfSheetsW = sheetNames
End Function

然后我有一个函数返回 TRUE 或 FALSE，具体取决于 needle 是否在 haystack 中。

Function IsInArray2(ByVal needle As String, haystack() As String) As Boolean
  Dim element As Variant
  For Each element In haystack
    If element = needle Then
      IsInArray = True
      Exit Function
    End If
  Next element
  IsInArray = False
End Function

我的目标是创建新的子例程，该子例程将首先检查具有给定名称的工作表是否已存在，如果不存在，则创建新的工作表。我尝试过以下操作:

Sub CreateNewSheet(ByVal dstWSheetName As String)
  Dim srcWSheetName As String

  ' Dim sheetNames() As String
  Dim sheetNames() As Variant

  sheetNames = getListOfSheetsW()
  Dim sheetCount As Integer

  If IsInArray2(dstWSheetName, sheetNames) Then
    MsgBox "Sheet with following name: " & dstWSheetName & " already exists"
  Else
    srcWSheetName = ActiveSheet.name
    sheetCount = Sheets.Count

    ' CREATE NEW SHEET
    ' Worksheets(dstWsheetName).Delete
    Sheets.Add.name = dstWSheetName
    ' Q: why 6 instead of 5
    ' Worksheets("Test").Move after:=Worksheets("Sheet5")
    Worksheets(dstWSheetName).Move After:=Worksheets(sheetCount + 1)

    ' SWITCH TO SRC SHEET
    Worksheets(srcWSheetName).Activate
  End If
End Sub

我这样调用它:

Sub CallCreateNewSheet()
  Call CreateNewSheet("test")
End Sub

我猜问题出在 DimsheetNames() As String 或 DimsheetNames() As Variant。

当我使用 DimsheetNames() As String 时，我得到

Run-time error '13': Type mismatch

当我使用 DimsheetNames() As Variant 时，我得到:

Compile error: Type mismatch: array or user-defined type expected

我有类似的问题before但将 sheetNames 定义为数组并没有帮助。问题是什么？这两个不同的错误意味着什么？

Answer 1

如果您从类型化数组切换到变体数组，您将避免所有这些问题。

在第一个函数中，删除此行:

 Dim sheetNames() As Variant

更改第二个函数的定义行:

Function IsInArray2(ByVal needle As String, haystack() As String) As Boolean

...对此:

Function IsInArray2(ByVal needle As String, haystack) As Boolean

在您的子目录中，更改此行:

Dim sheetNames() As Variant

...对此:

Dim sheetNames