excel - 转换为后期绑定(bind)会导致运行时 1004 错误 - Outlook

Question

我有一个工作 Outlook 宏，它将当前用户的任务列表导出到 Excel 电子表格，但我想将其更改为使用后期绑定(bind)以便于分发(即我不必向其他用户解释设置库引用等)

我按照例子Convert Early Binding VBA to Late Binding VBA : Excel to Outlook Contacts将我的 Excel 变量设置为对象。

下面是我如何声明绑定(bind)更改前/后变量的比较:

'Late binding variables and their early binding equivilants
  Dim objExcel As Object 'Dim objExcel As New Excel.Application
  Dim exWB As Object 'Dim exWb As Excel.Workbook
  Dim sht As Object 'Dim sht As Excel.Worksheet
  Dim Range As Object 'Dim Range As Excel.Range
  Dim r As Object 'Dim r As Range
  Dim cell As Object 'Dim cell As Range

  'set application
  Set objExcel = CreateObject("Excel.Application")

我现在在代码的以下部分中收到运行时 1004 错误:

    With objExcel.ActiveSheet
  Set r = .Range(.Cells(2, col), .Cells(.Rows.Count, col).End(xlUp)) 'runtime 1004 error here after late binding modification
End With
For Each cell In r
 s = cell.Text
 If Len(Trim(s)) > 0 Then
   iloc = InStr(1, s, sChar, vbTextCompare)
   If iloc > 1 Then
     s1 = Left(s, iloc - 1)
     cell.Value = s1
   Else
     If iloc <> 0 Then
      cell.ClearContents
     End If
   End If
 End If
Next cell
        y = y + 1
        stat_string = ""
       End If

  Next x


'Autofit all column widths

For Each sht In objExcel.ActiveWorkbook.Worksheets
    sht.Columns("A").EntireColumn.AutoFit
    sht.Columns("B").EntireColumn.AutoFit
    sht.Columns("C").EntireColumn.AutoFit
    sht.Columns("D").EntireColumn.AutoFit
    sht.Columns("E").EntireColumn.AutoFit
    sht.Columns("F").EntireColumn.AutoFit
Next sht

exWB.Save

exWB.Close

Set exWB = Nothing
'this kills the excel program from the task manager so the code will not double up on opening the application
'sKillExcel = "TASKKILL /F /IM Excel.exe"
'Shell sKillExcel, vbHide
objExcel.Application.Quit

我已将其余代码包含在错误行之后，因此，如果存在进一步的运行时问题，它们可能会被 SO 上令人难以置信的人们发现。

我假设声明“范围”的方法不正确，但我不太确定原因，因此不确定如何修复它。

有人有建议吗？

谢谢!

Answer 1

xlUp 是在 Excel 库中定义的 Excel 常量。如果您删除了引用，则 xlUp 将是一个未声明的变量。

如果您设置了Option Explicit，那么您应该在编译时发现这一点。