漂亮地打印Excel公式?

这个VBA代码不会赢得任何奖项,但它可以快速查看典型的公式。它只不过是手工处理parens或separator。将它插入一个代码模块,然后从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)

您也可以用普通字符串来调用它。

试用Excel公式美化剂 http://excelformulabeautifier.com/ . 它漂亮地打印(又称美化)Excel公式。

(我帮助维护这一点,总是寻求反馈以使其更好。)

以下是一个商业解决方案,可能对您有用:

http://www.uts.us.com/ItemDetails.asp?ItemID=1100-40-0000-00

很明显有试用版。

上述代码片段的这个版本现在也有不同的handels引用字符,这意味着,它将它们单独存在,如果它们位于字符串中,则不会让它们影响缩进,如:

"This ({)},;+*-/ won't lead to a linebreak" 

它由布尔变量binsideQuotes控制。 它也使用

.FormulaLocal

使最终用户能够看到他们知道的东西。

Public Function prettyPrintFormula(f As Variant) As String
    Dim formulaStr As String
    Dim ppf As String

    If IsObject(f) Then
        Debug.Assert TypeOf f Is Range

        Dim rng As Range
        Set rng = f

        formulaStr = rng.FormulaLocal
    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
    Dim bInsideQuotes As Boolean
    bInsideQuotes = False
    For i = 1 To Len(formulaStr)
        c = Mid$(formulaStr, i, 1)

        If InStr("""", c) > 0 Then
            bInsideQuotes = Not bInsideQuotes
        End If
        If InStr("({", c) > 0 And Not bInsideQuotes 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 And Not bInsideQuotes Then
            tabNum = tabNum - 1
            tabOffset = 0

            ppf = ppf & c & vbCrLf & Space(tabs(tabNum))
        ElseIf InStr("+-*/^,;", c) > 0 And Not bInsideQuotes Then
            tabOffset = 0

            ppf = ppf & c & vbCrLf & Space(tabs(tabNum))
        Else
            ppf = ppf & c

            tabOffset = tabOffset + 1
        End If
    Next i
    prettyPrintFormula = ppf
End Function