完美Excel

近期正在利用Excel开发一套工作管理系统，已初步有了结果，并开始使用。前不久，在一篇博客文章中，我贴出了该系统的主界面。在这里，随便谈谈在开发过程中的一些心得体会，供大家参考。

• 不要将所有任务全部集中在一个工作簿中

在同一工作簿中不要试图处理所有需要实现的任务。当然，如果您的系统只需实现少有的几项功能，在一个工作簿中集中实现这几项功能是简单方便的。但是，如果需要实现多项任务，特别是需要处理大量数据的情形下，建议将任务进行整合分类，分别放在不同的工作簿中来实现这些任务。
此时，需要规划好各工作簿要实现的功能任务，合理设计各个工作簿之间的关系，理顺各工作簿之间的数据调用，并且使用一个主工作簿来调用各个工作簿，这样使得系统非常灵活、精简，也不致于因使用而致使工作簿体积快速不断增大。
要注意的是，对于存在数据调用的工作簿，一定要清楚调用的顺序，避免因为某一工作簿数据的更换而影响其他相关工作簿数据的准确性。例如，工作簿B需要调用工作簿A中的数据，如果工作簿A中的数据发生变化，那么要及时更新工作簿B中的数据。

• 利用工作簿的自定义属性来确定工作簿

为工作簿添加自定义文档属性（即CustomDocumentProperties
属性），从而利用该属性来查找工作簿，或者判断是否为要查找的工作簿，或者能够合并同类工作簿。特别是在有大量名称会发生变化的工作簿时。
单击菜单“文件”─—“属性”，选择“自定义”选项卡，在“名称”中输入相应的名称，在“取值”中输入“Yes”，然后单击“添加”按钮加入属性，如下图所示。

这样，在多个工作簿相互调用时，可以避免因工作簿名称改变而出现的无法找到工作簿的错误。

• 避免无谓的循环

记得及早、适时地退出循环，特别是在存在大量数据而不得不逐一进行循环时。例如，下面的代码，必须在工作表中的第3行至第31行、第2列至第32列依次循环，当满足特定条件后，使用Exit Sub语句退出，从而避免继续循环。如果在更大范围内循环时更应如此。
        For i = 3 To 31
            ‘循环每一列
            For j = 2 To 32
                If Target.Value = “” Then
                    If iTotalRow > 1 Then
                        For k = 2 To iTotalRow
                            If Target.Row = Worksheets(”Data”).Range(”A” & k) And Target.Column = Worksheets(”Data”).Range(”B” & k) Then
                                Worksheets(”Data”).Rows(k).EntireRow.Delete
                                bDecide = True
                                Exit Sub
                            End If
                        Next k
                    End If
                End If
            Next j
        Next i

• 记得清除垃圾

有时，需要在工作表中反复汇总或操作来自另一工作表或工作簿中的数据。记住，在将其他地方的数据复制到工作表中之前，记得清除以前的数据，避免产生重复数据或带来错误。

• 积累通用代码，方便调用，提高开发效率

在我们编写程序的过程中，总有些代码适合于不同的程序开发。我们可以将这些程序代码做成通用程序，当需要时直接调用，从而节省时间，便于组织，提高了开发效率。这里，举几个例子。
（1）判断某文件是否存在
‘判断文件是否存在
Private Function FileExists(fname) As Boolean
    ‘如果存在则返回True
    FileExists = (Dir(fname) <> “”)
End Function
（2）判断工作表是否存在
‘判断工作表是否存在
Private Function SheetExists(sName) As Boolean
    ‘如果当前工作簿中存在该工作表则返回True
    Dim x As Object
    On Error Resume Next
    Set x = ActiveWorkbook.Sheets(sName)
    If Err = 0 Then
        SheetExists = True
    Else
        SheetExists = False
    End If
End Function

（3）返回满足查找条件的所有单元格组成的区域
‘通用的查找函数
Function FindAll(SearchRange As Range, FindWhat As Variant, _
    Optional LookIn As XlFindLookIn = xlValues, Optional LookAt As XlLookAt = xlWhole, _
    Optional SearchOrder As XlSearchOrder = xlByRows, _
    Optional MatchCase As Boolean = False) As Range
”””””””””””””””””””””””””””””””””””””””””””””
‘ 返回SearchRange区域中含有FindWhat所代表的值的所有单元格组成的Range对象
‘ 其参数与Find方法的参数相同
‘ 如果没有找到单元格,将返回Nothing.
”””””””””””””””””””””””””””””””””””””””””””””
  Dim FoundCell As Range
  Dim FoundCells As Range
  Dim LastCell As Range
  Dim FirstAddr As String
  With SearchRange
    Set LastCell = .Cells(.Cells.Count)
  End With
  Set FoundCell = SearchRange.Find(what:=FindWhat, after:=LastCell, _
    LookIn:=LookIn, LookAt:=LookAt, SearchOrder:=SearchOrder, MatchCase:=MatchCase)
  If Not FoundCell Is Nothing Then
    Set FoundCells = FoundCell
    FirstAddr = FoundCell.Address
    Do
      Set FoundCells = Application.Union(FoundCells, FoundCell)
      Set FoundCell = SearchRange.FindNext(after:=FoundCell)
    Loop Until (FoundCell Is Nothing) Or (FoundCell.Address = FirstAddr)
  End If
  If FoundCells Is Nothing Then
    Set FindAll = Nothing
  Else
    Set FindAll = FoundCells
  End If
End Function
大家在平时可以积累这些程序，既方便学习，又可以重用。

今天收到了人民邮电出版社寄来的《Excel VBA实战技巧精粹》样书，在此作个记号。也希望购买了此书的朋友多提建议和意见，我将努力改进之。

在Excel中，我们可以使用“数据有效性”功能来创建下拉列表，供用户从中选择。例如，如下图所示：

①选择主数据工作表中的B列。
②调出“数据有效性”对话框，选择“允许”下拉列表框中的“序列”。
③在“来源”中输入要提供用户选择的文本，例如：请假,出差,年休。
④单击“确定”后，在主数据工作表的B列中任一单元格单击，出现带有数据的可供选择的列表，如下图。

当然，也可以将需要提供的数据放置在工作表中的某单元格区域内，然后在“来源”中输入该单元格区域地址。
上述方法对于固定的或者说事先知道在列表中提供的数据时，非常合适。然而，如果下拉列表中的数据需要不断增加，或者需要调整，仍然按照上述方法的话，则需要重复上述步骤。
下面提供一种方法，能够动态改变列表中的数据，而无需每次调出“数据有效性”对话框。先看看下图：

①在基础数据工作表中，在列A单元格中依次输入想要显示在下拉列表中的数据，如“请假”、“出差”、“换休”。
注：将这些数据输入在单独的工作表中，能够避免在主工作表中操作时无意中修改这些数据。
②调出“新建名称”对话框，在“名称”中输入“List”。
③在“范围”列表中选择“工作簿”。
④在“备注”中输入一些说明文字。
⑤在“引用位置”输入公式“=OFFSET(基础数据!$A$1,0,0,COUNTA(基础数据!$A:$A),1)”。
现在，回到主数据工作表，如下图所示。

①选择列C。
②调出“数据有效性”对话框，选择“允许”下拉框中的“序列”。
③在“来源”中输入公式“=List”，即与前面定义的名称连接。
此时，单击列C中的任一单元格，将出现下拉列表框，包括在基础数据工作表中列A的数据，如下图所示。

现在，您在基础数据表中添加数据，如加班，此时在主数据工作表的列C中单元格的下拉列表中会自动出现添加的“加班”项，如下图所示。

最近，使用Excel VBA开发了一套工作管理系统，效果不错，极大地简化了工作，提高了工作效率。当然，还不是太完善，需要在使用过程中进一步优化。下图是这套系统的界面设计：

在Excel工作表中使用图片，不仅用于导航，而且也能创造出良好的视觉效果。
当然，在开发过程中，有许多心得体会，以后再和大家慢慢分享。

Excel条件格式功能是一项十分强大且便利的功能。下面介绍自已在使用Excel过程中，利用条件格式功能的其中一个应用场景。
在一个工作表中，有许多条记录，如何使Excel自动标识出满足特定条件的记录呢？
例如：下图所示的工作表中有多条记录，要求当字段“是否完成”列中为“否”时，突出标识该行（本例中将该行突出显示为红色），使用户清楚没有完成的工作；当为“是”时，突出标识的背景色自动消失。

现在开始实现这样的效果，先看看下图：

①选择工作表的A列至E列。
②单击菜单“格式”——“条件格式”。
③在“条件格式”对话框中，选择“条件1”下拉框中的“公式”项。
④在“公式”框右侧的文本框中输入公式“=$E1=”是””。
⑤单击下方的“格式”按钮，在“单元格格式”对话框的“图案”选项卡中，选择颜色，这里是红色。
⑥单击“确定”按钮，关闭“条件格式”对话框。此时的效果如下图：

此时，当将E列中单元格的数据改为“是”时，该行的红色底纹会自动消失；如果为“否”，则该行会自动加上红色底纹。