PDA

View Full Version : ActiveWorkbook vs Application.Workbooks



Tavvkind
08-04-2014, 05:08 AM
Hello all,

I've found a piece of VBA code and I don't understand part of that piece:

Sub Test2()
' rondebruin.nl/win/s9/win002.htm
Dim szSourceWorkbook As String
Dim wkbSource As Excel.Workbook

szSourceWorkbook = ActiveWorkbook.Name
Set wkbSource = Application.Workbooks(szSourceWorkbook)

MsgBox ActiveWorkbook.Name & " " & wkbSource.Name ' Book1 Book1

End Sub

Why would someone get the name of the active workbook and then return the workbook of the same name by using the .Workbooks collection (instead of just doing: Set wkbSource = ActiveWorkbook). When I run the subroutine above, the name of the workbook is the same ("Book1").

ranman256
08-04-2014, 06:12 AM
It doesnt do much...it sets the variable wkbSource to the current workbook. szSourceWorkbook holds the 'name' of it, but its not needed, since it gets assigned to wkbSource anyway.

The use would be: if you move away to a different workbook, then want to come back to the original, use wkbSource to come back by wkbSource.activate

Tavvkind
08-06-2014, 03:05 AM
I agree, perhaps it was made to make sure that the subroutine works on the same workbook, even if some other subroutine activates a different workbook while the exporting subroutine is still working (I don't know in an interruption like this is even possible TBH, but just for the reference). Regardless, retrieving the active workbook through the name also looks like an overhead, as there can't be two workbooks opened with the same name at the same time. So I guess the name always uniquely identifies the workbook? (unless, again, the workbook is closed and other with same name is opened while the subroutine works), but why use the name to uniquely refer to the workbook, when ActiveWorkbook does exactly the same (is also unique).

Thank you ranman256.