翻译Excel 2013 Power Programming with VBA 第14章的前两部分

Developing Excel Utilities with VBA

使用VBA开发Excel实用程序
 
In This Chapter

在这一章

● Exploring Excel utilities and utilities in general

探究Excel 实用程序和一般的实用程序

● Developing utilities with VBA

使用VBA开发Excel实用程序

● Creating good utilities

创建好的实用程序

● Manipulating text in cells

处理单元格中的文本

● Finding additional Excel utilities

寻找额外的Excel实用程序
 
About Excel Utilities

关于Excel实用程序
 
A utility, in general, is something that enhances software, adding useful features or making existing features more accessible. A utility isn’t an end product, such as a quarterly report. Rather, it’s a tool that helps you produce an end product. An Excel utility is (almost always) an add-in that enhances Excel with new features or capabilities.
 
一般来讲，实用程序是能增强软件功能，增加有用的特性或者使已有的特性更容易使用的程序。实用程序不是像季度报表一样的最终产品，而是一个帮助你制作最终产品的工具。Excel实用程序是（而且几乎都是）通过增加新特性或者新功能从而增强Excel性能的插件。
 
Excel is a great product, but many users soon develop a wish list of features that they’d like to see added to the software. For example, users who work with  dates may want a pop-up calendar feature to facilitate entering dates into cells. And some users desire an easier way to export a range of data to a separate file or to save a chart as a graphics file. These are all examples of features that aren’t currently available in Excel. You can, however, add these features by creating a utility.
 
Excel是一款伟大的产品，但是许多用户很快就把他们想要增加的特性列上愿望清单。比如，经常使用日期的用户可能想要一个弹出式日历的特性以便在单元格里输入日期。还有一些用户渴望有一种更简易的方法把一系列数据导出到一个单独的文件里或者把图表保存为一个图像文件。这些都是目前Excel中不存在的特性的例子。然而，你可以通过创建实用程序来添加这些特性。
 
Utilities don’t need to be complicated. Some of the most useful ones are very simple. For example, have you noticed that Excel 2013 doesn’t have a Ribbon command to toggle the page break display in a worksheet? If you don’t like to see those dotted lines in your worksheet, a trip to the Excel Options dialog box to turn them off is required. Even worse, you can’t add that command to the Ribbon or Quick Access toolbar.
 
实用程序不需要很复杂。一些最有用的实用程序其实很简单。比如，你注意到了吗， Excel2013没有一个用于在工作表中切换显示分页符的功能区命令。如果你不喜欢在工作表中看到那些虚线，就需要逛逛Excel选项对话框然后从那里关掉它们。更糟的是，你不能把那条命令添加到功能区或者快捷工具栏。

Here’s a simple VBA macro that toggles the page break display:

这是一个能够切换分页符显示的简单的VBA宏。

Sub TogglePageBreaks()

With ActiveSheet

.DisplayPageBreaks = Not .DisplayPageBreaks

End With

End Sub

You can store this macro in your Personal Macro Workbook so that it’s always available. Or you may prefer to package your favorite utilities in an add-in. For quicker access, you can assign your utility macros to a shortcut key or a right-click shortcut menu or modify your Quick Access toolbar or the Ribbon.

你可以把这个宏保存到个人宏工作簿中以便日后使用。或者你更愿意将最喜欢的实用程序打包成一个插件。为了更加快速地访问，你可以把工具宏赋给一个快捷键或快捷菜单，亦或者修改快捷工具栏或功能区。

As you’ll see,creating utilities for Excel is an excellent way to make a great product even better.

你会看到，为Excel创建实用程序是为一款伟大的产品锦上添花的绝妙方法。
 
 
Using VBA to Develop Utilities
使用VBA开发实用程序
 
Excel 5, released in 1992, was the first version of Excel to include VBA. WhenI received the beta version of Excel 5, I was impressed by VBA’s potential. VBA was light-years ahead of Excel’s powerful (but cryptic) XLM macro language, andI decided that I wanted to explore this new language and its capabilities.

Excel 5 发布于1992年，它是第一个包含VBA的Excel版本。当我拿到Excel5的测试版后，VBA的潜能给我留下了深刻的印象。VBA的出现远远早于Excel功能强大（但是神秘）的XML宏语言。我决定要探究这一新语言和它的能力。
 
 
In an effort to learn VBA, I wrote a collection of Excel utilities by using only VBA. I figured that I would learn the language more quickly if I gave myself a tangible goal. The result was a product that I call the Power Utility Pak for Excel, which is available to you at a discounted price as a benefit of buying this book. (Use the coupon in the back of the book to order your copy.)

在学习VBA的过程中，我仅仅使用VBA开发了一些实用工具。我发现如果给自己设定一个明确的目标，我就会更快地学习这一语言。结果，我开发了一个我称之为Excel实用工具包的产品。作为购买本书的一个益处， 你能以折扣价购买该工具包（请使用本书后面的优惠券订购）。
 
I learned several things from my initial efforts on this project:

我从这个项目初始的工作中学到了几样东西：

● VBA can be difficult to grasp at first, but it becomes much easier with practice.

VBA刚开始有可能难掌握，但是通过练习它就会变得容易多了。

●  Experimentation is the key to mastering VBA. Every project that I undertake usually involves dozens of small coding experiments that eventually lead to a finished product.

试验是掌握VBA的关键。我做的每一个项目都包含很多小的代码试验，这些试验最终构成一个完整的产品。
 
● VBA enables you to extend Excel in a way that is consistent with Excel’s look and feel, including custom worksheet functions and dialog boxes. And, if you’re willing to step outside VBA, you can write XML code to customize the Ribbon automatically when your application is opened.
 
VBA使你能够扩展Excel，这种扩展与Excel的外观和使用上的感觉保持一致，这包括各种自定义的工作表功能和对话框。而且，如果不愿使用VBA, 你还可以写XML代码用来在程序打开时自动地自定义设置功能区。
 
Excel can do almost anything. When you reach a dead end, chances are that another path leads to a solution, especially if you’re creative and know where to look for help.

 

Excel几乎可以做任何事情。当走入了死胡同时，如果你有创造力并且知道从哪里寻求帮助，就有可能从另外一条路找到解决方法。

 

Few other software packages include such an extensive set of tools that enable the end user to extend the software.

 

没有多少其它的软件包含这么大量的工具可以让最终用户扩展软件。