将数组传递给函数返回“Compile error：Type mismatch：array或user-defined type expected” [英] Passing array to function returns "Compile error: Type mismatch: array or user-defined type expected"

问题描述

  Sub printArray（arr（）As Variant）
 Dim i，lowerBound，upperBound As Integer 
 lowerBound = LBound（arr）
 upperBound = UBound（arr）
 
 MsgBoxlowerBound：& lowerBound 
 MsgBoxupperBound：& upperBound 
 
对于i = lowerBound To upperBound 
 MsgBox i& ：& arr（i）
 Next i 
 End Sub 
  

这是按预期工作的我以下列方式打电话：

  Sub callPrintArray（）
 Dim arr（3）As Variant 
 arr（0）=John
 arr（1）=Star
 arr（2）=＃6/30/2010＃'雇用日期
 
'打印一个没有这个线的额外元素
'Dim arr（3）As Variant MEANS INDEXING FROM 0 TO 2 
 arr（3）=LAST
调用printArray（arr）
 End Sub 
  

然后我有函数返回现有的表作为数组：

 函数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 
结束函数
  

我想打印这个函数返回的表格离子子程序 printArray 。我试过：

  Sub callGetListOfSheetsW（）
 Dim arr As Variant 
 arr = getListOfSheetsW（） 
 
'工作
 MsgBox arr（1）
 MsgBox arr（2）
 
'不工作
'调用printArray（arr） 
 End Sub 
  

当我打印特定元素时，它正在工作，但是当我想打印整个数组使用 printArray 它不是，我得到：

编译错误：类型不匹配：数组或用户定义的类型。

我做错了什么？

解决方案

变体（可能包含数组）与Variant数组不同。您需要更改此行：

  Dim arr As Variant 
  

：

  Dim arr（）As Variant 
  

I have following subroutine which prints the array elements:

Sub printArray(arr() As Variant)
  Dim i, lowerBound, upperBound As Integer
  lowerBound = LBound(arr)
  upperBound = UBound(arr)

  MsgBox "lowerBound: " & lowerBound
  MsgBox "upperBound: " & upperBound

  For i = lowerBound To upperBound
    MsgBox i & " : " & arr(i)
  Next i
End Sub

This is working as expected and I'm calling it in the following way:

Sub callPrintArray()
  Dim arr(3) As Variant
  arr(0) = "John"
  arr(1) = "Star"
  arr(2) = #6/30/2010# ' Hire Date

  ' PRINTS ONE EXTRA ELEMENT WITHOUT THIS LINE
  ' Dim arr(3) As Variant MEANS INDEXING FROM 0 TO 2
  arr(3) = "LAST"
  Call printArray(arr)
End Sub

Then I have function which return existing sheets as array:

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

I want to print the sheets returned by this function with mentioned subroutine printArray. I've tried:

Sub callGetListOfSheetsW()
    Dim arr As Variant
    arr = getListOfSheetsW()

    ' Working
    MsgBox arr(1)
    MsgBox arr(2)

    ' Does not working
    ' Call printArray(arr)
End Sub

When I print particular element it is working, but when I want to print whole array using printArray it is not and I get:

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

What I'm doing wrong?

解决方案

A Variant (which may happen to contain an array) is not the same as an array of Variants. You need to change this line:

Dim arr As Variant

to this:

Dim arr() As Variant

这篇关于将数组传递给函数返回“Compile error：Type mismatch：array或user-defined type expected”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！