“带有工作表("xxxx"")"仅在"xxxx"是活动的工作表

问题描述

我对Excel VBA还是很陌生.到目前为止，我已经在该站点上阅读和学习了很多东西，但是还没有找到解决我问题的方法.

I'm pretty new to Excel VBA. So far I've read and learned a lot on this site, but haven't found a solution for my problem.

作为宏的一部分，我有以下代码:

As part of a macro I have the following code:

With Worksheets("Oracle")
    On error resume next
    ActiveWorkbook.Names("bron").Delete
    ActiveWorkbook.Names.Add Name:="bron", RefersTo:= Range("A1", Range("A1").End(xlToRight).End(xlDown))
    .Cells.Select
    With Selection.Font
        .Name = "Verdana"
        .FontStyle = "Standaard"
        .Size = 8
    End With
    .Range("A1", Range("A1").End(xlToRight)).Font.Bold = True
    MsgBox "Tabblad ‘Oracle’ is klaar!", vbOKOnly
End With

我知道，对于代码的第一行，活动工作表实际上是什么并不重要.但是问题是，只有当Oracle是活动工作表时，它才有效.我做错了什么?

I understand that with the first line of the code it shouldn't matter what the active sheet actually is. But the problem is it only works when Oracle is the active sheet. What have I done wrong?

推荐答案

如果使用的是With Worksheets() ... End With，则意味着您要引用特定的工作表而不是ActiveSheet.在VBA中，这被认为是一种很好的做法.

If you are using With Worksheets() ... End With it means that you want to refer to a specific worksheet and not to the ActiveSheet. This is considered a good practice in VBA.

如@GSerg的注释中所述，您的代码不起作用，因为在所有Range前面都没有点.但是，您不会注意到这一点，因为您正在使用On Error Resume Next，它会忽略所有错误.

As mentioned in the comments by @GSerg, your code does not work, because you do not have a dot in front of all the Ranges. However, you cannot notice this because you are using On Error Resume Next, which ignores all errors.

在您的情况下，问题是您试图用此行.Range("A1", Range("A1").End(xlToRight)).引用同时在ActiveSheet和Oracle中的范围.因此错误是不可避免的.

In your case the problem is that you are trying to Refer to a range which is in both the ActiveSheet and in Oracle with this line .Range("A1", Range("A1").End(xlToRight)).. Thus the error is unevitable.

您有两种选择来确保您的代码可以正常工作:

You have two options to make sure your code works:

1. 只需激活工作表"Oracle"并运行代码.会没事的.
2. 尝试像这样重写它:

---

With Worksheets("Oracle")
    On Error Resume Next
    ActiveWorkbook.Names("bron").Delete
    ActiveWorkbook.Names.Add Name:="bron", _
                          RefersTo:=.Range("A1", .Range("A1").End(xlToRight).End(xlDown))

        With .Cells.Font
            .Name = "Verdana"
            .FontStyle = "Standaard"
            .Size = 8
        End With

    .Range("A1", .Range("A1").End(xlToRight)).Font.Bold = True
    MsgBox "Tabblad ‘Oracle’ is klaar!", vbOKOnly

End With

看看所有的Ranges都带有一个点，并且Select命令已不再使用.

Take a look that all the Ranges are referred with a dot and the Select command is not used any more.

这篇关于“带有工作表("xxxx"")"仅在"xxxx"是活动的工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！