How to avoid select/active statements in VBA in this example?
excel, vba
Solution
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.
Problem
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 ```