问与答89：如何识别含有硬编码的公式单元格？

Q：我有一个工作表，上面含有许多公式，有些公式中含有“硬编码”，例如=SUM(1,2,3)，其中的数字就是“硬编码”。如何判断含有硬编码的公式单元格？如下图1所示，含有硬编码的公式单元格返回TRUE。

图1

注：这是在chandoo.org论坛上看到的一个问题，觉得很好，特别是编写代码的思路，故辑录于此，供参考。

A：尝试使用显示公式、定位选择常量、公式审核等Excel内置的功能都不能实现上述任务。此时，是该VBA发挥作用了。

下面自定义的hasConstants函数能够判断指定的单元格中的公式是否含有硬编码。hasConstants函数完整的代码如下：

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

Const COMMA = ','Const OPERATORS = '+-*/%^&><=' 'thisCell中是否是含有常量的公式Public Function hasConstants(thisCell As Range) As Boolean Dim formula As String Dim args As Variant Dim i As Long Dim testRange As Range formula =replaceOperators(Mid(thisCell.formula, 2)) args =Split(formula, COMMA) For i = LBound(args) To UBound(args) If Not(Len(args(i)) = 0 Or Right(args(i), 1) = '(' Or args(i) =')') Then If Not nameExists(CStr(args(i))) Then hasConstants = True Exit Function EndIf End If Next i End Function '使用逗号连接公式中的各元素Function replaceOperators(formula As String) As String Dim char AsLong For char =1 To Len(OPERATORS) formula= Replace(formula, Mid(OPERATORS, char, 1), COMMA) Next char formula =Replace(formula, '(', '(' & COMMA) formula =Replace(formula, ')', COMMA & ')') replaceOperators = formulaEnd Function '判断是否是定义的名称Function nameExists(name As String) As Boolean Dim testR As Range On Error GoTo last Set testR =Range(name) nameExists= True Set testR =Nothing last:End Function

代码将传递的公式中的符号使用逗号替换，然后使用Split函数拆分成数组，遍历该数组判断其中是否含有常量，如果是则表明公式中有硬编码，返回TRUE，否则返回FALSE。