这段VBA代码可能不会获得任何奖项,但它可以快速查看典型公式。它只是简单地模拟手动使用括号或分隔符的方法。将其放入代码模块中,并从VBA即时窗口命令行调用它。(编辑:最近我需要查看一些公式,所以我对原始答案进行了改进,因此我回来修改了它。)
Public Function ppf(f) As String
Dim formulaStr As String
If IsObject(f) Then
Debug.Assert TypeOf f Is Range
Dim rng As Range
Set rng = f
formulaStr = rng.Formula
Else
Debug.Assert VarType(f) = vbString
formulaStr = f
End If
Dim tabs(0 To 99) As Long
Dim tabNum As Long
tabNum = 1
Dim tabOffset As Long
Dim i As Long
Dim c As String
For i = 1 To Len(formulaStr)
c = Mid$(formulaStr, i, 1)
If InStr("({", c) > 0 Then
ppf = ppf & c
tabNum = tabNum + 1
tabs(tabNum) = tabs(tabNum - 1) + tabOffset + 1
tabOffset = 0
ppf = ppf & vbCrLf & Space(tabs(tabNum))
ElseIf InStr(")}", c) > 0 Then
tabNum = tabNum - 1
tabOffset = 0
ppf = ppf & c & vbCrLf & Space(tabs(tabNum))
ElseIf InStr("+-*/^,;", c) > 0 Then
tabOffset = 0
ppf = ppf & c & vbCrLf & Space(tabs(tabNum))
Else
ppf = ppf & c
tabOffset = tabOffset + 1
End If
Next i
End Function
如果您这样调用它:
?ppf([q42])
您不必担心转义双引号等问题。您将获得类似于以下输出:
AC6+
AD6+
(
IF(
H6="Yes",
1,
IF(
J6="Yes",
1,
0)
)
)
+
IF(
X6="Yes",
1,
0)
您也可以使用纯字符串调用它。