How to avoid select/active statements in VBA in this example?
TL;DR: You can't avoid Select
in this case, because you need the late-bound behavior of ActiveSheet.ExportAsFixedFormat
, which apparently takes into account the selected sheets -- which the early-bound Worksheet.ExportAsFixedFormat
doesn't do.
ExportAsFixedFormat
is a member of Excel.Worksheet
.
ActiveSheet
is not an Excel.Worksheet
though, it's an Object
(so any member calls against are necessarily late-bound). This is an important detail.
When you do this:
ThisWorkbook.Sheets(Array("Sheet1", "Sheet2")).Select
The .Select
member call is not made against a Worksheet
object: given an array of sheet names, the Sheets
(or Worksheets
) property returns a Excel.Sheets
collection object, and that is what .Select
is being invoked against. This is also an important detail.
As you probably know, the objects you deal with in the Excel object model are COM objects. In COM, an interface is extensible, unless specified otherwise: that's how you can write this:
Debug.Print Application.Sum(2, 2)
And get an output - even if Sum
is not a compile-time member of the Application
class: the member call is resolved at run-time (that's what "late binding" is), and because the COM object is extended with a specific selection of WorksheetFunction
members, Application.Sum
works perfectly fine at run-time, although you get no compile-time validation for any of it: you're basically coding blindfolded, and any typo in the member name will raise error 438 at run time (but will compile perfectly fine even with Option Explicit
specified), and any error in the arguments (wrong type, wrong order, wrong number of arguments) will raise error 1004 at run time.
That's why you generally want to avoid implicit late-binding, and therefore coding against ActiveSheet
and Selection
: because these Object
objects (same for member calls against Variant
) define no compile-time interface, using them is writing code blindfolded, and that's very error-prone.
But early-bound code is not always 100% equivalent to the late-bound alternatives.
This is one such case: the ExportAsFixedFormat
member behaves one way at run-time when early-bound, and behaves differently when late-bound. With a late-bound call you can export a single PDF document with a page for each worksheet in the Sheets
collection, while an early-bound call against Worksheet.ExportAsFixedFormat
only exports that sheet, and since there's no Sheets.ExportAsFixedFormat
, you can't make that late-bound call directly against Sheets(Array(...))
to avoid the .Select
call and subsequent ActiveSheet
late-bound member call.
There are many other members, notably WorksheetFunction
members, that behave differently when late bound vs early bound.