在此示例中，如何避免VBA中的select/active语句? [英] How to avoid select/active statements in VBA in this example?

问题描述

需要将所有工作表导出到一个PDF文件中，因此我找到了这段代码，它可以正常工作(为每个工作表导出一个带有页面的PDF).但是我不想使用select/active语句，我更喜欢使用存储对象的变量.

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.

问题: 如何避免在此代码中使用select/ActiveSheet?

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

推荐答案

TL; DR:在这种情况下，您无法避免使用Select，因为您需要ActiveSheet.ExportAsFixedFormat的后期绑定行为，这显然考虑了记入选定的工作表 －早期绑定的Worksheet.ExportAsFixedFormat不会这样做.

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是Excel.Worksheet的成员.

ActiveSheet不是Excel.Worksheet，但它是Object(因此，对它的任何成员调用都必须是后期绑定的).这是一个重要的细节.

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.

执行此操作时:

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

.Select成员调用不是针对Worksheet对象进行的:给定工作表名称数组，Sheets(或Worksheets)属性返回Excel.Sheets集合对象，而 是.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.

您可能知道，在Excel对象模型中处理的对象是COM对象.在COM中，接口是可扩展的，除非另有说明:这是您可以这样编写的方式:

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)

并获得输出-即使Sum不是Application类的编译时成员:成员调用在运行时解析(这就是后期绑定"的意思)，并且因为COM对象通过特定选择的WorksheetFunction成员进行 extended 扩展，Application.Sum在运行时可以很好地运行，尽管您没有对其进行任何编译时验证:您基本上是被蒙住眼睛编码，并且成员名称中的任何拼写错误都会在运行时引发错误438(但即使指定了Option Explicit也会完美编译)，并且参数中的任何错误(错误的类型，错误的顺序，错误的参数数目)都将引发错误1004在运行时.

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.

这就是为什么通常要避免隐式的后期绑定，并因此针对ActiveSheet和Selection进行编码:因为这些Object对象(与针对Variant的成员调用相同)定义没有编译时接口，使用它们编写被蒙住眼睛的代码，这很容易出错.

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.

就是这种情况:ExportAsFixedFormat成员在早期绑定时在运行时表现为一种方式，而在后期绑定时则表现为不同的方式.通过后期绑定调用，您可以导出一个单独的PDF文档，其中包含Sheets集合中每个工作表的页面，而针对Worksheet.ExportAsFixedFormat的早期绑定调用仅导出该那个工作表，并且没有Sheets.ExportAsFixedFormat，您不能直接针对Sheets(Array(...))进行该后期绑定调用，以避免.Select调用和随后的ActiveSheet后期绑定成员调用.

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.

还有许多其他成员，尤其是WorksheetFunction成员，它们在后期绑定与早期绑定之间的行为有所不同.

There are many other members, notably WorksheetFunction members, that behave differently when late bound vs early bound.

这篇关于在此示例中，如何避免VBA中的select/active语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！