VBA代码未运行，定义工作簿和工作表时出错

Question

我需要一个解决问题的解决方案。

我正在尝试运行以下代码，但我总是收到"运行时错误 9：下标超出范围"。

问题出在这一行：设置源 = 工作簿(文件名(。片材(2(

Sub TesteNum()
Dim appExcel As Application
Dim objWorkbook As Workbook
Dim i, k, n, userAns As Integer
Dim timeCount, timeTotal As Double
Dim source, target As Worksheets
'On Error GoTo ErrorHandle
If IsEmpty(pathFolder) = True Then
Call GetFilePath
Else
userAns = MsgBox("Encontrado caminho do ficheiro, quer selecionar outro ficheiro?", vbYesNo + vbQuestion, "Empty Sheet")
If userAns = vbYes Then
Call GetFilePath
Else
End If
End If
'create new excel application object
Set appExcel = New Application
'set the applications visible property to false
appExcel.Visible = True
'open the workbook with data
Set objWorkbook = appExcel.Workbooks.Open(pathFolder)
Set source = Workbooks(fileName).Sheets(2)
Set target = ActiveWorkbook.Sheets(1)
'numRowsImport = Worksheets("NewRawData").Range("A2", Range("A2").End(xlDown)).Rows.Count
numRowsImport = source.Range("A" & source.Rows.Count).End(xlUp).Row

For k = 3 To numRowsImport
timeCount = source.Cells(k, 9).Value
timeTotal = timeTotal + timeCount
Next k
'MsgBox "O valor total é " & Format(timeTotal, "h:mm"), vbOKOnly, "Caminho do ficheiro"
target.Range("U11").Value = timeTotal
'close the workbooks
objWorkbook.Close
'close the application
appExcel.Quit
MsgBox "O valor total é " & timeTotal, vbOKOnly, "Caminho do ficheiro"

'ErrorHandle:
'MsgBox "Erro. Contacte o administrador."
End Sub

你们能帮我吗？

谢谢。

Answer 1

你声明

Dim source, target As Worksheets

不会将"源"声明为工作表。它将源声明为变体，将目标声明为工作表。将声明分开。