Excel VBA- do you have to declare an object to open a new workbook?

Factual Questions
1

I am using Excel 2010. I am running a macro that pulls some data together, opens a new workbook, pastes the data into the new workbook, emails it out, closes the new workbook, and then starts over. It creates and emails about 25 workbooks. In my code, I just use Workbooks.Add and ActiveWorkbook.Close to open and close the workbook, but someone told me I need to declare the object and then set it to equal nothing when I close the workbook.

I have watched the task manager while the code is running in break mode, and when the workbook closes the amount of memory used by Excel goes back down to where it was before the new workbook opened every time, so I believe I am not hurting anything by doing it the way I am. The Excel documentation lists it as an acceptable way to do it.

Is there any reason why I should declare an object?

2

You don’t need to, but if you assign the result of Workbooks.Add to a variable, it’s cleaner than ActiveWorkbook.Close, especially when debugging.

So:

dim CurrentWorkbook as Workbook

…

<loop through workbooks>
set CurrentWorkbook = Workbooks.Add()
…
CurrentWorkbook.Close
<end loop>

That way, if you have to stop partway through for some reason and you accidentally activate another workbook, you won’t close the wrong one. I try to avoid using ActiveWorkbook, ActiveSheet, and so on when I can – it just makes it easier to stop/start a process and do any necessary debugging. (I haven’t tried the above code, so the actual syntax may be slightly different.)

3

I agree with RitterSport. Obviously you don’t have to declare the workbook, because you don’t and your code works. The issue is not resource usage but reliable code.

However, I always declare workbooks, worksheets, and cells for the reasons **RitterSport **mentions. Anonymous objects cause problems when you get more complex code. An object may be activated in one part of the code but another part of the code doesn’t know that, and assumes that the Active object is something different than what it really is. Anonymous objects can be OK if you are creating, using, and releasing them all within a few lines of code but usually it’s not such a good practice.

Whether or not you assign an object to a variable will have no impact on resource usage as seen in Task Manager. Although it’s not well documented, I believe that Workbook.Close will deallocate the object, regardless of whether you have assigned it to a variable. (Some developers will also assign Nothing to the variable after closing the workbook to explicitly force garbage collection but I’m not sure it’s necessary.)