英文:
Is there a known problem with using .UsedRange to reset the range in Excel, when the parent is a Worksheet object?
问题
ActiveSheet.UsedRange '可以正常工作
Dim My_Sheet as Worksheet
Set My_Sheet = ActiveSheet
My_Sheet.UsedRange 'VBA编译器不喜欢这样
英文:
I'm trying to ensure the UsedRange is accurate, having experienced it being substantially different to what is actually used in a worksheet. Having <worksheet object>.UsedRange is meant to reset the used range to what is in use. I'm finding it doesn't unless structured in a very particular way.
ActiveSheet.UsedRange ' Works OK
Dim My_Sheet as Worksheet
Set My_Sheet = ActiveSheet
My_Sheet.UsedRange ' VBA compiler doesn't like this
答案1
得分: 0
@Geoff_B，我没有任何问题。你介意分享一下截图吗？我尝试了不同的变化，一切看起来都正常。不太可能是编译器混淆了表达式。
Sub test()
Debug.Print ActiveSheet.UsedRange.Address ' 正常运行
Dim My_Sheet As Worksheet
Set My_Sheet = Sheets(ActiveSheet.Name)
Debug.Print My_Sheet.UsedRange.Address
'=====================和=====================
Dim My_Sheet As Object
Set My_Sheet = ActiveSheet
Debug.Print My_Sheet.UsedRange.Address
End Sub
英文:
@Geoff_B I don't have any issues. Do you mind sharing the screenshot? I tried different variations everything seems fine. unlikely the compiler confused with the expression.
Sub test()
Debug.Print ActiveSheet.UsedRange.Address ' Works OK
Dim My_Sheet As Worksheet
Set My_Sheet = Sheets(ActiveSheet.Name)
Debug.Print My_Sheet.UsedRange.Address
'=====================and=====================
Dim My_Sheet As Object
Set My_Sheet = ActiveSheet
Debug.Print My_Sheet.UsedRange.Address
End Sub
答案2
得分: 0
谢谢Tim Williams，将dimming作为对象解决了。在我看来，这似乎是一个错误。
英文:
Thanks Tim Williams, dimming as Object has resolved it. Looks like a bug to me.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论