Recursively resolve $(variables) to get readable sourcecode

    This macro is able to resolve $(variables) with the source code behind and repeats as long as the result contains again a $(variable). At the end, the function ResolveDollarVariables will return the readable code instead of the $(variable) names.

     

    Attached is also an .QVW file with this macro and a practical example: It analyses an entire chart definition, by looping through all expressions in a given chart (up to 10 expressions) whlie writing the resolved code back into GUI variables in order to allow some statistics:

    • how many nestings the $(definitions) were deep
    • how many bytes/lines of code were found,
    • how many Aggr(), If(), Sum(), Count() ... functions were contained in each expression, something like this

     

    Below are two funtions which you may reuse elsewhere for resolving $(variables) into readable code. If you look for the chart-scanner as a such open the attached .QVW file.

     

    Enjoy

    Christof

     

     

    Dim gb_maxNestings


    '-----------------------------------------------------------------

    Function SubstringCount(txt, subtxt)

    '-----------------------------------------------------------------

        txt2 = Replace(txt, subtxt, "")

        SubstringCount = (Len(txt) - Len(txt2)) / Len (subtxt)

    End Function

     

     

    '-----------------------------------------------------------------

    Function ResolveDollarVariables(formula)

    '-----------------------------------------------------------------

        q = SubstringCount(formula, "$(")

        gb_maxNestings = 0

        Set doc = ActiveDocument

        Set app = doc.GetApplication

          

        Do While q > 0       

            gb_maxNestings = gb_maxNestings + 1

            skip_to_pos = 1

      

            For k = 1 To q

                startf = InStr(skip_to_pos, formula, "$(") + 1

                ' "nesting" holds the layers of brackets inside "$(=" ... ")"

                nesting = 0

                m = startf

                Do ' count open and close brackets until the nesting level 0

                    If Mid(formula, m, 1) = "(" Then nesting = nesting + 1

                    If Mid(formula, m, 1) = ")" Then nesting = nesting - 1

                    If nesting = 0 Then endf = m

                    m = m + 1

                Loop Until nesting = 0 Or m > Len(formula)

          

                If nesting <> 0 or endf = startf Then

                    Msgbox "Syntax error to evaluate function """ & formula & """"

                    Exit Function

                Else

              

                    subfunct = Mid(formula, startf+1, endf-startf-1)

                        subfunct = doc.Evaluate(Mid(formula, startf+1, endf-startf-1))

                    app.WaitForIdle

                    'subfunct = RemoveRemarks(subfunct)

                    skip_to_pos = Len(Left(formula, startf-2) & subfunct) + 1                   

                    formula = Left(formula, startf-2) & subfunct  _

                        & Mid(formula, endf+1)       

      

                End If                           

            Next

            q = SubstringCount(formula, "$(")

        Loop

     

        ' Return value of Function

        ResolveDollarVariables = formula

        ' The function also sets the global variable "gb_maxNestings" !

     

    End Function

     

     

    Note: It is not tested with $(=expressions), just with $(variables).