3

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

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?

cxw
  • 16,685
  • 2
  • 45
  • 81
PeterEZ
  • 45
  • 7
  • 1
    Welcome to the site! Check out the [tour](https://stackoverflow.com/tour) and the [how-to-ask page](https://stackoverflow.com/help/how-to-ask) for more about asking questions that will attract quality answers. You can [edit your question](https://stackoverflow.com/posts/48382421/edit) to include more information. Is the issue that it only works when `Oracle` is the active *sheet*, or is it that it only works when the *workbook* containing `Oracle` is active? If a different workbook is active, `Worksheets` might not give you what you want. **Edit** Also, what is the actual failure? – cxw Jan 22 '18 at 13:16
  • 2
    All `Range(...)` must have a `.` in front of them, not just some. – GSerg Jan 22 '18 at 13:16
  • 1
    Possible duplicate of [2 ways for "ClearContents" on VBA Excel, but 1 work fine. Why?](https://stackoverflow.com/questions/18962890/2-ways-for-clearcontents-on-vba-excel-but-1-work-fine-why) – GSerg Jan 22 '18 at 13:19
  • @cxw Thank you! The code concerns one workbook, no others are involved, so imho different workbooks is not the issue. A few weeks ago I had the same code, but then instead of With Worksheets(“xxxx”) I used Worksheet.Activate, wich worked fine – PeterEZ Jan 22 '18 at 13:25
  • Thanks for the response! I think @GSerg is probably right - references to `Range` (instead of `.Range`) refer to the current worksheet rather than the worksheet named in the `With` statement. I am going to join the close vote, but please don't hesitate to request reopening or ask a different question if you are still having trouble. – cxw Jan 22 '18 at 13:28
  • @GSerg Thanks for your answer. I know about the . , but didn't realise that it all Range must have one. – PeterEZ Jan 22 '18 at 14:02

1 Answers1

2

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.

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.

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. Simply activate Worksheet "Oracle" and run the code. It will work ok.
  2. Try to rewrite it like this:
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

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

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • Thanks very much for your detailed answer. Now I understand why my original code didn't work. – PeterEZ Jan 22 '18 at 14:11
  • 1
    @cxw, GSerg, Vityata. Thanks for your immediate help. I've run the code as suggested by Vityata, and it works perfectly! Thanks again. – PeterEZ Jan 22 '18 at 14:15