函数返回值作为Excel公式 [英] Function return value as excel formula

问题描述

我正在尝试将函数创建的公式转换为作为公式而不是函数括号返回.如所附的屏幕截图所示.

I am trying to convert a formula created by function to return as a formula instead of function brackets. As shown in screenshot attached.

Function f2t2(rng As Range) As String

    Application.ScreenUpdating = False

    jGetFormula = rng.Formula
    jGetFormula = Replace(jGetFormula, "(", """" & "(" & """" & "&")
    jGetFormula = Replace(jGetFormula, ")", "&" & """" & ")" & """" & "&")
    jGetFormula = Replace(jGetFormula, "+", "&" & """" & "+" & """" & "&")
    jGetFormula = Replace(jGetFormula, "-", "&" & """" & "-" & """" & "&")
    jGetFormula = Replace(jGetFormula, "*", "&" & """" & Chr(215) & """" & "&")
    jGetFormula = Replace(jGetFormula, "/", "&" & """" & "/" & """" & "&")
    jGetFormula = Replace(jGetFormula, "^", "&" & """" & "^" & """" & "&")
    jGetFormula = Replace(jGetFormula, "&&", "&")

    If (Right(jGetFormula, 1) = "&") Then
        jGetFormula = Left(jGetFormula, (Len(jGetFormula) - 1))
    End If

    'MsgBox jGetFormula
    'recalcualting other formulas in the excel
    Application.Volatile

    'Returning to excel
     f2t2 = jGetFormula

    'f2t = jGetFormula
    Application.ScreenUpdating = True
    Application.StatusBar = ""
End Function

我正在尝试将函数创建的公式转换为作为公式而不是函数括号返回.如所附的屏幕截图所示:

I am trying to convert a formula created by function to return as a formula instead of function brackets. As shown in screenshot attached:

Sub Formula_Edit(Optional endAll As Boolean = False)

MsgBox "3"
Range("T101").Value = 5
If endAll Then End
MsgBox "4"
End Sub

Function call2()
MsgBox "1"
Call Formula_Edit(True)
MsgBox "2"
End Function

推荐答案

就像Peh已经提到的那样，这里是解析公式的解决方案. 该解决方案可能适合您的需求，但不是完全证明.函数中的某些函数将被评估为一个值.

Like already mentioned by Peh, here a solution with parsing the formula. This solution may suit your needs but is not full proof. Some functions in the functions will be evaluated as one value.

Function f2t2(rng As Range) As String
    x = rng.Formula
        For Each del In Array(";", " ", ".", "<", ">", "+", "-", "=", "/", "\", "*", "^") '":","(", ")"
            x = Replace(x, del, "|")
        Next del
            arr1 = Split(x, "|")
            arr2 = arr1

        For i = LBound(arr1) To UBound(arr1)
            On Error Resume Next
            arr2(i) = IIf(Application.Evaluate(arr1(i)) = "", "0", Application.Evaluate(arr1(i)))
            On Error GoTo 0
        Next i

    x = rng.Formula
        For i = LBound(arr1) To UBound(arr1)
            x = Replace(x, arr1(i), arr2(i))
        Next

    f2t2 = x

End Function

这篇关于函数返回值作为Excel公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！