通过直接引用范围而不是通过中间变量来引用Excel VBA运行时错误450 [英] Excel VBA run time error 450 from referencing a range directly rather than through an intermediate variable

问题描述

当我尝试直接引用一个范围内的值时，出现运行时错误450，但是如果我使用中间变量，它将起作用.我不明白为什么，所以我担心将来在某个时候会再次收到该错误，而不知道为什么.

I am getting a Run Time Error 450 when I try to reference a value in a range directly, but if I use an intermediate variable it works. I do not understand why so I am worried I will get the error again at some point in the future without knowing why.

我尝试使用With ... End With块，当我直接引用该范围时，它仍然不起作用.

I have tried using a With ... End With block and it still does not work when I reference the range directly.

此代码给出了错误

Public Sub Test_PT()
Dim lol As String

    lol = Worksheets(1).PivotTables("PivotTable2").RowRange(2, 1).Value
    MsgBox (lol)

End Sub

虽然此代码可以正常工作

While this code works fine

Public Sub Test_PT()
Dim lol As String
Dim rng As Range

    Set rng = Worksheets(1).PivotTables("PivotTable2").RowRange
    lol = rng(2, 1).Value

    MsgBox (lol)

End Sub

我不知道为什么它可以在第二个代码块中工作，但不能在第一个代码块中工作.我尝试了设置中间变量的哪个级别的变体.如果我创建数据透视表变量并进行了适当设置，则代码可以正常运行，但是如果我创建工作表变量并尝试使用工作表变量引用相同的范围，则它将无法正常工作.

I do not know why it works in the 2nd code block but not in the first. I have tried variations of which level to set an intermediate variable. If I create a pivot table variable and set this appropriately, the code runs fine, but if I create a worksheet variable and try to reference the same range using the worksheet variable it does not work.

虽然我可以使用中间变量来使这段代码正常工作，但我真的很想理解为什么要在其他情况下出现这种情况.

While I can get this chunk of code working by using an intermediate variable, I really want to understand WHY I need to do this in case it comes up in other contexts.

推荐答案

RowRange属性返回一个Range对象，但是它没有任何参数，因此您不能传递任何参数内.

The RowRange property returns a Range object, but it doesn't take any arguments, so you can't pass any in.

lol = Worksheets(1).PivotTables("PivotTable2").RowRange.Cells(2, 1).Value 

对我来说很好，

lol = Worksheets(1).PivotTables("PivotTable2").RowRange()(2, 1).Value

这篇关于通过直接引用范围而不是通过中间变量来引用Excel VBA运行时错误450的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！