Dla danej funkcji Excela (np. VLOOKUP) muszę przeanalizować ciąg formuły komórki na:

  • preFunctionStr = Ciąg poprzedzający funkcję WYSZUKAJ.PIONOWO
  • ExcelFn = "WYSZUKAJ.PIONOWO"
  • Arguments = Zbiór wszystkich argumentów w funkcji WYSZUKAJ.PIONOWO (które same mogą zawierać funkcje)
  • postFunctionStr = Ciąg następujący po ostatnim „)” w ciągu formuły

Moim głównym powodem, dla którego tego chcę, jest możliwość konwertowania formuł Excela bez zmiany ich odpowiedzi. Na przykład przekonwertuj SUMIF na SUMIFS i przekonwertuj VLOOKUP na kombinację INDEX i MATCH.

W moim przykładzie jestem w komórce zawierającej formułę =A4+VLOOKUP(2,$E$4:$F$8,MATCH("Value(1)",$E$4:$F$4,0),0) + 2000, którą muszę przeanalizować na składniki opisane powyżej.

Chociaż mogłem znaleźć mnóstwo materiałów na temat analizowania formuł Excela, nie mogłem znaleźć takiego, który dzieliłby go na te składniki.

-1
Malan Kriel 14 listopad 2018, 15:43

1 odpowiedź

Najlepsza odpowiedź

W moim rozwiązaniu tworzę Class module z właściwościami, których wymagam, aby podzielić ciąg formuły na ExcelFormulaParser:

Option Explicit

Public ExcelFn As String
Public Arguments As New Collection
Public preFunctionStr As String
Public postFunctionStr As String

Sub SetMeUp(formulaStr As String, FormulaToParse As String)

    Dim FormulaStartPos As Integer
    Dim OpenBracketCounter As Integer
    Dim OpenBracketCount As Integer
    Dim ClosedBracketCount As Integer
    Dim WithinQuote As Boolean
        ' whether we are within quotation marks
    Dim i As Integer

    Dim strChr As String
    Dim Arg_i As String
    Dim Arg As String

    Me.ExcelFn = FormulaToParse

    FormulaStartPos = InStr(1, formulaStr, FormulaToParse)

    Me.preFunctionStr = Mid(formulaStr, 1, FormulaStartPos - 1)
    formulaStr = Mid(formulaStr, FormulaStartPos + Len(FormulaToParse), Len(formulaStr) - Len(FormulaToParse))

    If Left(formulaStr, 1) = "(" Then
        OpenBracketCounter = 1
        formulaStr = Mid(formulaStr, 2, Len(formulaStr) - 1)
    Else
        MsgBox ("Not the full FormulaToParse")
        End
    End If

    i = 0
    Arg_i = ""
    Do While OpenBracketCounter > 0
        i = i + 1
        strChr = Left(formulaStr, 1)
        If Len(formulaStr) > 0 Then
            formulaStr = Right(formulaStr, Len(formulaStr) - 1)
        End If

        If strChr = Chr(34) Then
            WithinQuote = Not (WithinQuote) ' toggle WithinQuote on or off
            ' don't count brackets within quotation marks
        ElseIf strChr = "(" And WithinQuote = False Then
            OpenBracketCounter = OpenBracketCounter + 1
        ElseIf strChr = ")" And WithinQuote = False Then
            OpenBracketCounter = OpenBracketCounter - 1
        End If

        If OpenBracketCounter = 1 And strChr = "," Then
            Arg = Arg_i
            Me.Arguments.Add Arg
            Arg_i = ""
        ElseIf OpenBracketCounter = 0 Then
            Arg = Arg_i
            Me.Arguments.Add Arg
            Arg_i = ""
            Me.postFunctionStr = formulaStr
        Else
            Arg_i = Arg_i & strChr
        End If
    Loop

End Sub

Aby podać przykład, jak wywołać i użyć Class module, stworzyłem następujący moduł w ramach tego samego projektu VBA:

Sub TestFormulaParser()

    Dim ParsedForm As ExcelFormulaParser
    Set ParsedForm = New ExcelFormulaParser
    Dim StrToParse As String
    StrToParse = ActiveCell.Formula
        ' formula contains:
        '' =A4+VLOOKUP(2,$E$4:$F$8,MATCH("Value(1)",$E$4:$F$4,0),0) + 2000
    Call ParsedForm.SetMeUp(StrToParse, "VLOOKUP")

    preFunctionStr = ParsedForm.preFunctionStr
        ' returns the prefunction string i.e. =A4+
    ExcelFn = ParsedForm.ExcelFn
        ' returns the excel function we parsed i.e. VLOOKUP
    Arg1 = ParsedForm.Arguments(1)
        ' returns the first argument of the VLOOKUP function i.e. 2
    Arg2 = ParsedForm.Arguments(2)
        ' returns the second argument of the VLOOKUP function i.e. $E$4:$F$8
    Arg3 = ParsedForm.Arguments(3)
        ' returns the third argument of the VLOOKUP function i.e. MATCH("Value(1)",$E$4:$F$4,0)
    Arg4 = ParsedForm.Arguments(4)
        ' returns the fourth argument of the VLOOKUP function i.e. 0
    postFunctionStr = ParsedForm.postFunctionStr
        ' returns the post function string i.e.  + 2000

End Sub
1
Malan Kriel 14 listopad 2018, 15:43