How to avoid select/active statements in VBA in th

Question

Need to export all sheets in one PDF file, so I found this piece of code, it works (exports a single PDF with a page for each sheet). But I don't want to use select / active statements, I prefer to use variables, that store the objects.

Question: How to avoid select/ ActiveSheet in this code?

ThisWorkbook.Sheets(Array("Sheet1", "Sheet2")).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\tempo.pdf", Quality:= xlQualityStandard, IncludeDocProperties:=True, _
     IgnorePrintAreas:=False, OpenAfterPublish:=True

Answer 1

What about this? Does this work:

For Each w In ThisWorkbook.Sheets(Array("Sheet1", "Sheet2"))
  w.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\tempo.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, _
 IgnorePrintAreas:=False, OpenAfterPublish:=True
Next w

Answer 2

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.