0

This

Range("Table1[#All]").Copy Destination:=Workbooks(5).ActiveSheet.Range("A1")

works as I understand to expect but this will not and gives Run-time-error '1004': Application-defined or object-defined error

Range("Table1[#All]").Copy Destination:=Workbooks.Add.ActiveSheet.Range("A1")

Tried understanding help here but just not getting there

What are the rules governing usage of brackets in VBA function calls?

mopwop
  • 41
  • 5
  • 3
    Why would you want to do that? – BigBen Oct 29 '20 at 19:46
  • Are there reasons not to do that other than it is potentially not possible? – mopwop Oct 29 '20 at 19:52
  • 1
    Chaining like that reads badly and also doesn't allow for good debugging and error handling. Create the workbook first, then copy to it. – BigBen Oct 29 '20 at 19:53
  • Ok but that is how the Macro Recorder does it for a Pivot Table. If it's possible would still like to understand how and the why behind it. – mopwop Oct 29 '20 at 19:55
  • 1
    The code produced by the Macro Recorder should not be taken at face value. The Pivot Table creation recorded code is bad. There are several questions here on SO due to it. In other words, macro recorder is not a programmer and does not follow programming practices. – BigBen Oct 29 '20 at 19:57
  • Referring to a workbook by the index is not a good idea either. When I just tried it the first 5 indices (I only had 3 books open so that was weird in itself) all returned my personal.xlsb file. – Warcupine Oct 29 '20 at 19:59
  • Which means? I get it may not be a "best practice" but understanding why it works and does not has value. – mopwop Oct 29 '20 at 19:59
  • The index is just a proof of concept for this example. – mopwop Oct 29 '20 at 20:01
  • I suppose the answer is that you just can't, just as you can't write `add.workbooks`. – SJR Oct 29 '20 at 20:03
  • 1
    Not at a computer right now, but I think `Workbooks.Add().ActiveSheet.Range("A1")` should work, as horrendous as it is... Please consider not inflicting this to your future self. Cheers! – Mathieu Guindon Oct 29 '20 at 20:41
  • 1
    @MathieuGuindon Yes, I can confirm that it works with or without brackets. – Domenic Oct 29 '20 at 20:45
  • 1
    Does not work in Excel 365 - fails with RTE 1004 Range method of _Global failed, I'm guessing due to `Range("Table1[#All]")`, since something like `Sheet1.ListObjects(1).Range` does work.......but as mentioned previously, should not be attempted anyway. – BigBen Oct 29 '20 at 20:59
  • Yeah 365 is what I have been forced to use. So it works with the brackets in earlier Excels? Had tried all sorts of parentheses shenanigans including proposed solution by Mathieu Guindon which still gets RTE 1004. Don't get the same message about Global's Range method that BigBen gets maybe due to testing it in the immediate window. – mopwop Oct 29 '20 at 22:06
  • Yeah @Mathieu so was driving to supper and realized when `Workbooks.Add()` makes a new Workbook it also sets the new Workbook as the `ActiveWorkbook` – mopwop Oct 30 '20 at 16:03
  • It does, but that's convenient (?) side-effect, not the output of the function, nor its purpose. By `Set`ting a local variable to the `Workbook` object the function returns, you work with the API, not its side-effects: the code is more reliable, and easier to diagnose when it fails. – Mathieu Guindon Oct 30 '20 at 19:44

1 Answers1

1

Alright so imagining it last night while driving to eat I wondered if Workbook.Add() setting the new workbook as active might be part of the problem by changing the ActiveWorkbook before the implicit Range("Table1[#All]") is evaluated.

So Workbooks("*Initial*").ActiveSheet.Range("Table1[#All]").Copy Destination:=Workbooks.Add().ActiveSheet.Range("A1")

does work.

I learned or reinforced 3 main things:

  1. Parentheses are needed for .Add even without using arguments to return a Workbook from the Workbooks collection just like would be required using an implicit call to the default member of Workbooks, Workbooks.Item() with an argument.
  2. Order of how operations are executed within a single instruction
  3. Watch out for Implicit references and secondary operations

Thanks to everyone that responded. Before I posted this question I expected the 1st response to be "Why?", and was not disappointed. I almost didn't even post.

Yes before I posted I knew I could just do Workbooks.Add 1st but then I would have just used the clipboard to copy the table 1st which would have been maybe slightly less desirable I almost did just that and would have kept trucking. More importantly I would not have learned what I have.

I'm always open to any advice. Many here are tried and true on the topic of Excel VBA whereas I maybe write a little macro once or twice a year. I didn't persist for stubbornness of doing something my way but to understand from my point of view.

mopwop
  • 41
  • 5