Problem when using an array returend by a function

[Grand]

Hi
I am getting error (Can't assign to array); also "empty array" error when trying to use an array that contains two values and is returned by a function in the following code :

Function ImportSourceFile()

Dim NymberOfUpdates As String
Dim NumberOfSheets As String
Dim CheckUpdateStatus(1) As Integer

Set SourceWorkBook = ActiveWorkbook

 CheckUpdateStatus = CheckIfAnyNewUpdate(SourceWorkBook, NewOld, DMSCCNR)' this gives me the error or Can't sign to array
 NumberOfSheets = CheckUpdateStatus(0) ' number of sheets containing data
 NymberOfUpdates = CheckUpdateStatus(1) ' number of newly updated stuff

End Function

Function CheckIfAnyNewUpdate(MyWB As Workbook, MyStatus As String, MYdocNrs() As String) As Integer
Dim TempString As String
Dim MyWSheets As Worksheet
Dim MySheets() As String
Dim TRowsSourceFile As Long
Dim SheetCounter As Integer
Dim ReturnValues(1) As Integer
Dim NumberOfDocs As Long

     SheetCounter = 0
     NumberOfDocs = 0
     
     For Each MyWSheets In MyQBIQWB.Worksheets
       If InStr(1, Left(MyWSheets.Name, 5), "Sheet", vbTextCompare) > 0 Then ' will check for names starting with "Sheet"..
           SheetCounter = SheetCounter + 1


          
           TRowsSourceFile = MyWB.Sheets(MyWSheets.Name).UsedRange.Rows.Count
           
           If MyWB.Worksheets(MyWSheets.Name).AutoFilterMode = False Then
            MyWB.Worksheets(MyWSheets.Name).Rows("1:1").AutoFilter
           End If
           
           MyWB.Worksheets(MyWSheets.Name).Range("A2:J" & TRowsSourceFile).AutoFilter Field:=4, _
           Criteria1:=MyStatus, Operator:=xlFilterValues   
       
           MyWB.Worksheets(MyWSheets.Name).Range("A2:J" & TRowsSourceFile).AutoFilter Field:=1, _
           Criteria1:=MYdocNrs, Operator:=xlFilterValues   
         

           
           NumberOfDocs = NumberOfDocs + MyWB.Worksheets(MyWSheets.Name).Range("A1:A" & TRowsSourceFile).Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
       End If
     Next MyWSheets



ReturnValues(0) = CInt(SheetCounter)
ReturnValues(1) = CInt(NumberOfDocs)

 CheckIfAnyUpdate = ReturnValues()
End Function

Thanks so much for any help

 

[Norie]

Declare CheckUpdateStatus as Variant and don't set its dimensions.

Dim CheckUpdateStatus As Variant

 

[Grand]

Did that and get the same error "Can't assign to array".at the same line as before:

Dim CheckUpdateStatus() As Variant
CheckUpdateStatus = CheckIfAnyNewUpdate(SourceWorkBook, NewOld, DMSCCNR)' gives error

 

[Kyle123]

Dim CheckUpdateStatus As Variant

Isn't the same as:

Dim CheckUpdateStatus() As Variant

 

[Grand]

Totally on spot. Sorry for not being attentive enough. Beside that, I found out actually that I was not returning anything as the function name is "CheckIfAnyNewUpdate" and not "CheckIfAnyUpdate". I am no sure why I did change the name as updates are usually new. Anyways, it works now and I am thankfull.