excel - 使用 VBA 直接引用范围 Excel

Question

我在使用直接引用时遇到问题。我试图在不使用选择或激活的情况下将值放入根工作簿。但是，如果需要将数据复制到的工作表未处于事件状态，我的代码会出错。如果工作表处于事件状态，则代码可以正常工作...

这是我的代码

Workbooks(root).Activate
Dim wb As Workbook
Set wb = Application.Workbooks(root)

wb.Sheets("d_eff_stress").Cells(1, n).Value = filename
wb.Sheets("d_eff_stress").Range(Cells(3, n), Cells(100, n)).Value = varray1
wb.Sheets("eff_stress").Cells(1, n).Value = filename
wb.Sheets("eff_stress").Range(Cells(3, n), Cells(100, n)).Value = varray2

解决方案:完全引用范围......我本可以知道

Workbooks(root).Activate
Dim wb As Workbook
Set wb = Application.Workbooks(root)

wb.Sheets("d_eff_stress").Cells(1, n).Value = filename
wb.Sheets("d_eff_stress").Range(wb.Sheets("d_eff_stress").Cells(3, n), wb.Sheets("d_eff_stress").Cells(100, n)).Value = varray1
wb.Sheets("eff_stress").Cells(1, n).Value = filename
wb.Sheets("eff_stress").Range(wb.Sheets("eff_stress").Cells(3, n), wb.Sheets("eff_stress").Cells(100, n)).Value = varray2

Answer 1

您需要将工作表 parentage 分配给 Cells范围内:

wb.Sheets("d_eff_stress").Range(wb.Sheets("d_eff_stress").Cells(3, n), wb.Sheets("d_eff_stress").Cells(100, n)).Value

要减少打字，请使用 With block :

Workbooks(root).Activate
Dim wb As Workbook
Set wb = Application.Workbooks(root)

With wb.Sheets("d_eff_stress")
    .Cells(1, n).Value = filename
    .Range(.Cells(3, n), .Cells(100, n)).Value = varray1
End With

With wb.Sheets("eff_stress")
    .Cells(1, n).Value = filename
    .Range(.Cells(3, n), .Cells(100, n)).Value = varray2
End With