VBA:为什么变量在命名范围内不起作用? [英] VBA: Why isn't variable working in named range?

问题描述

这是较大代码的一部分，但是此代码段无效.我试图将两个像元设置为彼此相等，但是它不起作用.当我使用.Range("v1_copy")时，代码运行，但是当我命名该范围并将其作为变量(myCopyRange)放置时，代码无法运行，并且出现错误:编译错误:方法或数据找不到成员.任何帮助将不胜感激！

This is part of a larger code, but this snippet isn't working. I'm trying to just set two cells equal to each other, but it's not working. When I use the .Range("v1_copy"), the code runs, but when I name that range and place it as a variable (myCopyRange), the code doesn't run and I get the error: Compile error: Method or data member not found. Any help would be appreciated!

Sub copy_paste_test()

Dim myCopyRange As Range
Dim myPasteRange As Range
Dim myWS1 As Worksheet
Dim myWS2 As Worksheet

Set myWS1 = Sheets("Sheet1")
Set myWS2 = Sheets("Sheet2")

myCopyRange = Range("v1_copy")
myPasteRange = Range("v1_paste")

'myWS2.Range("v1_paste").Value = myWS1.Range("v1_copy").Value
' This line works, but the below line doesn't
 myWS2.myPasteRange.Value = myWS1.myCopyRange.Value
' This should be the exact same, just substituting the variable, but doesn't work

End Sub

推荐答案

对于myCopyRange和myPasteRange的Range对象引用分配，您缺少Set关键字.

You're missing the Set keyword for your Range object reference assignments to myCopyRange and myPasteRange.

但是对于检索命名范围，如果您想要完全显式的代码以执行所说的并说出所做的事情，最好的方法是从相应的Name中取消引用Name c5>集合.

But for retrieving a named range, the best place to go if you want fully explicit code that does what it says and says what it does, is to dereference the Name from the appropriate Names collection.

如果名称是工作簿范围的名称，请使用Workbook对象进行限定-这里是一个book对象变量，但是根据需要，ActiveWorkbook或ThisWorkbook也可以工作:

If the names are workbook-scoped, qualify with a Workbook object - here a book object variable, but depending on needs ActiveWorkbook or ThisWorkbook work just as well:

Set myRange = book.Names("name").RefersToRange

如果名称是工作表范围的，请使用Worksheet对象进行限定-这里是sheet对象变量，但是ActiveSheet同样有效:

If the names are worksheet-scoped, qualify with a Worksheet object - here a sheet object variable, but ActiveSheet works just as well:

Set myRange = sheet.Names("name").RefersToRange

这样，如果重命名工作簿或用户更改工作表的选项卡名称"，则代码不会中断.只要名称存在于查询的Names集合中，它就不会中断.

That way the code won't break if the workbook is renamed, or if the user changes the "tab name" of the sheet. It won't break as long as the name exists in the queried Names collection.

'myWS2.Range("v1_paste").Value = myWS1.Range("v1_copy").Value
' This line works, but the below line doesn't
 myWS2.myPasteRange.Value = myWS1.myCopyRange.Value
' This should be the exact same, just substituting the variable, but doesn't work

这应该是完全相同的-不. myWS1.myCopyRange是非法的:myWS1是Worksheet对象:Worksheet接口没有myCopyRange成员，因此未找到方法或数据成员.

This should be the exact same - no. myWS1.myCopyRange is illegal: myWS1 is a Worksheet object: the Worksheet interface doesn't have a myCopyRange member, hence method or data member not found.

由于myCopyRange是Range对象，所以它知道其所属的Worksheet是Parent:不需要限定它……也不需要再次取消引用它-这足够了:

Since myCopyRange is a Range object, it knows about its Parent which is the Worksheet it belongs to: there's no need to qualify it... and there's no need to dereference it again either - this is enough:

myPasteRange.Value = myCopyRange.Value

这篇关于VBA:为什么变量在命名范围内不起作用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！