0

While writing this code 

Worksheets.Add after:=Worksheets(Worksheets.Count)

VBA gives option of left parenthesis after the ADD method. But if I enclose 

after:=Worksheets(Worksheets.Count)

in parenthesis is shows an error.

Why?

Community
  • 1
  • 1
  • Possible duplicate of [What is behind this difference in parentheses effect in VBA?](https://stackoverflow.com/questions/45747604/what-is-behind-this-difference-in-parentheses-effect-in-vba) – YowE3K Aug 19 '17 at 18:15
  • Possible duplicate of [Why aren't parentheses required for add method of dictionary class?](https://stackoverflow.com/questions/19126312/why-arent-parentheses-required-for-add-method-of-dictionary-class) – Doug Glancy Aug 19 '17 at 18:22
  • 1
    As mentioned in the SO question I cited above, my favorite discussion of this is this [Daily Dose of Excel post](http://dailydoseofexcel.com/archives/2012/05/01/quick-vba-tip-parentheses/). – Doug Glancy Aug 19 '17 at 18:24
  • Thanks all for the help –  Apr 13 '19 at 14:48

2 Answers2

0

One use is to set a variable on-the-fly.

dim nuws as worksheet
set nuws = Worksheets.Add(after:=Worksheets(Worksheets.Count))

Because you are doing something with the returned value (e.g. setting it to a var), the parameters are required to be in parenthesis.

Another use is to rename the new worksheet in-line.

Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = "New WS"

Again, because you are doing something to the object newly created (e.g. naming it), the parameters are required to be in parenthesis.

0

Call Worksheets.Add as a sub. No error.

Worksheets.Add after:=Worksheets(Worksheets.Count)

Call Worksheets.Add as a function but not using the returned value. Produces error. Basically, you're telling it to return a value but giving it nowhere to store that value.

Worksheets.Add(after:=Worksheets(Worksheets.Count))

Call Worksheets.Add as a function and ignore the returned value. No error.

Call Worksheets.Add(after:=Worksheets(Worksheets.Count))

Call Worksheets.Add as a function and capture the returned value. No error.

SomeVar = Worksheets.Add(after:=Worksheets(Worksheets.Count))
thx1138v2
  • 566
  • 3
  • 6