翻译《Excel 2013 Power Programming with VBA》段落

The Module1 VBA module

The Module1 VBA module contains the declarations, a simple procedure that starts the utility, and a procedure that handles the undo operation.
Declarations in the Module1 VBA module
Following are the declarations at the top of the Module1 module:
Public Const APPNAME As String = “Text Tools Utility”
Public Const PROGRESSTHRESHOLD = 2000
Public UserChoices(1 To 8) As Variant ‘stores user’s last choices
Public UndoRange As Range ‘ For undoing
Public UserSelection As Range ‘For undoing


I declare a Public constant containing a string that stores the name of the application. This string is
used in the UserForm caption and in various message boxes.
The PROGRESSTHRESHOLD constant specifies the number of cells that will display the progress indicator.
When this constant is 2,000, the progress indicator will be shown only if the utility is working
on 2,000 or more cells.
The UserChoices array holds the value of each control. This information is stored in the Windows
Registry when the user closes the dialog box and is retrieved when the utility is executed again. Iadded this convenience feature because I found that many users tend to perform the same operation every time they use the utility.


Two other Range object variables are used to store information used for undoing.
The ShowTextToolsDialog procedure in the Module1 VBA module
The ShowTextToolsDialog procedure follows:
Sub ShowTextToolsDialog()
 Dim InvalidContext As Boolean
 If Val(Application.Version) < 12 Then
 MsgBox “This utility requires Excel 2007 or later.”, vbCritical
 Exit Sub
 End If
 If ActiveSheet Is Nothing Then InvalidContext = True
 If TypeName(ActiveSheet) <> “Worksheet” Then InvalidContext = True
 If InvalidContext Then
 MsgBox “Select some cells in a range.”, vbCritical, APPNAME
 Else
 UserForm1.Show vbModeless
 End If
End Sub


The procedure starts by checking the version of Excel. If the version is prior to Excel 2007, the user is 
informed that the utility requires Excel 2007 or later.
Note 
For simplicity, I made this utility an application for Excel 2007 or later. However, you can 
design this utility so that it also works with previous versions of Excel. 
If the user is running the appropriate version of Excel, the ShowTextToolsDialog procedure checks to 
make sure that a sheet is active, and then it makes sure that the sheet is a worksheet. If either one 
isn’t true, the InvalidContext variable is set to True. The If-Then-Else construct checks this variable 
and displays either a message (see Figure 14-4) or the UserForm. The Show method uses the 
vbModeless argument, which makes it a modeless UserForm (that is, the user can keep working in 
Excel while it’s displayed).
Part IV: Advanced Programming Techniques
Figure 14-4: This message is displayed if no workbook is active or if the active sheet isn’t a worksheet.
Note that the code doesn’t ensure that a range is selected. This additional error handling is included 
in the code that’s executed when the Apply button is clicked.

译文：

模块1:VBA模块
VBA模块包含声明,一个启动程序的简单过程,以及一个处理撤销操作的过程。
模块1 VBA模块的声明部分。
以下是在模块1顶部的声明内容:
Public Const APPNAME As String = “Text Tools Utility”
Public Const PROGRESSTHRESHOLD = 2000
Public UserChoices(1 To 8) As Variant ‘stores user’s last choices
Public UndoRange As Range ‘ For undoing
Public UserSelection As Range ‘For undoing

我声明一个包含存储应用名字的公共常量。这个字符串用在UserForm标题和各种消息框中。
PROGRESSTHRESHOLD常量描述进度指示器显示的单元格数量。
如果数字是2000,那么进度指示器只显示有2000或更多单元格的程序。
UserChoices数组保存每个控件的值。当用户关闭对话框这些信息存储在windows注册表中，再

打开程序的时候，信息会再次被检索到。我只所以添加这种方面的特性,是因为我发现许多用

户每次使用该程序的时候，更倾向于执行相同的操作。

另外两个对象变量是用来存储撤销操作的信息。

VBA模块中的内容展示工具对话框
内容展示工具条对话框过程如下:
Sub ShowTextToolsDialog()
 Dim InvalidContext As Boolean
 If Val(Application.Version) < 12 Then
 MsgBox “This utility requires Excel 2007 or later.”, vbCritical
 Exit Sub
 End If
 If ActiveSheet Is Nothing Then InvalidContext = True
 If TypeName(ActiveSheet) <> “Worksheet” Then InvalidContext = True
 If InvalidContext Then
 MsgBox “Select some cells in a range.”, vbCritical, APPNAME
 Else
 UserForm1.Show vbModeless
 End If
End Sub

程序首先检查的是Excel版本。如果是Excel 2007之前的版本,该程序将会告诉用户需要Excel 

2007或更高版本。

笔记：为了简单起见,我把这个程序升级到Excel 2007或更高版本。当然,您也可以设置下这个

程序让它支持以前版本的Excel。

如果用户正在使用的是当版本的Excel,ShowTextToolsDialog过程会检测，确保表单功能是是

正常工作的，然后确保该表单是一个工作表。如果其中任何一个是假的,InvalidContext变量

将设置为true。if - then - else结构检查该变量。并将结果以一个消息(见图14-4)或

UserForm的方式展示。使用vb非静态参数的方法展示一个非模态的UserForm(也就是说,信息展

示的时候，用户可可以继续操作Excel)。

第四部分: 高级编程技术

图14-4：如果没有工作表被激活或者激活的不是工作表，信息会被展示

请注意,不能确保一系列代码都被选中。这些额外的错误会在单击应用按钮的时候被处理。