vba 在选择其他工作表或工作簿时尝试使用 ThisWorkbook 设置范围值时出现问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17712866/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Issue when trying to set range values using ThisWorkbook when other sheet or workbook is selected
提问by Patrick Foran
Why doesn't the code work when executing the macro if a different sheet or workbook is selected other than sheet1? I need the code in this format so I can change the values in the cell entries to variables later on. Is there any way to work around this?
如果选择了 sheet1 以外的不同工作表或工作簿，为什么在执行宏时代码不起作用？我需要这种格式的代码，以便稍后将单元格条目中的值更改为变量。有没有办法解决这个问题？
Sub testhsa()
ThisWorkbook.Sheets("Sheet1").Range(Cells(1, 1), Cells(2, 2)).Value = 1
End Sub
回答by CuberChase
It doesn't work because you don't qualify the Cellscomponent of the statement.
它不起作用，因为您没有限定Cells语句的组件。
You need the following - notice the .before the Cells:
您需要以下内容 - 注意.之前的Cells：
With ThisWorkbook.Sheets("Sheet1")
.Range(.Cells(1,1), .Cells(2, 2)).Value = 1
End With
Edit: Further clarification for the comment.
By leaving the .off a worksheet/range/cell, you are telling Excel you want the Active parent. Ie Cells(1, 1)is the same ActiveSheet.Cells(1,1)and Range("A1:D4")is the same as ActiveSheet.Range("A1:D4").
编辑：进一步澄清评论。通过离开.工作表/范围/单元格，您告诉 Excel 您想要活动父级。即Cells(1, 1)相同ActiveSheet.Cells(1,1)且Range("A1:D4")与 相同ActiveSheet.Range("A1:D4")。
The Withstatement effectively tells Excel that anything that follows is associated 'with' that object so my 3 lines of code is exactly the same as:
该With语句有效地告诉 Excel 后面的任何内容都与该对象“关联”，因此我的 3 行代码与以下内容完全相同：
ThisWorkbook.Sheets("Sheet1").Range(ThisWorkbook.Sheets("Sheet1").Cells(1,1), ThisWorkbook.Sheets("Sheet1").Cells(2, 2)).Value = 1
This clarifies to Excel that no matter what is the ActiveWorkbookyou want the code access the range in the workbook the code is running from.
这向 Excel 阐明，无论ActiveWorkbook您希望代码访问运行代码的工作簿中的范围是什么。
Finally, if you are using ranges often you'll want them assigned to a variable as HeadofCatering has rightly suggested.
最后，如果您经常使用范围，您会希望将它们分配给 HeadofCatering 正确建议的变量。
回答by Jon Crowell
I strongly recommend the use of variables for your objects. It makes it much easier to figure out what you're doing, and cleans up your code dramatically.
我强烈建议为您的对象使用变量。它可以更容易地弄清楚你在做什么，并显着清理你的代码。
You can use the following code as a template:
您可以使用以下代码作为模板：
Sub OperateOnInactiveSheet()
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Dim writeThis as string
Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet1")
Set rng = ws.Range(ws.Cells(1, 1), ws.Cells(2, 2))
writeThis = "foo"
rng.Value = writeThis
End Sub
