Announcement

Collapse
No announcement yet.

Macro to perform evaluate function on each cell

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Macro to perform evaluate function on each cell



    Hi,

    I am looking to code a macro to seperate a formula by operands and evaluate each portion individually. I am familar with programming but not with VBA (I have a good idea of the algorithm but not enough knowledge of VBA syntax to code). I'm hoping someone here can help

    Basically what I want to do is to split a formula using the operand as delimiter and evaluating and resubstituting the results back to the original cell.

    Example if a formula in C1 contained

    =VLOOKUP(A1, RangeA, 2, FALSE) * VLOOKUP(B1, RangeB, 2, FALSE)

    lets say the first portion of this VLOOKUP (on range A) returns the number 2 and the second portion returns the number 3. I was hoping a macro could turn this cell into

    = 2 * 3

    Obviously an escape character (') will be required so 2 * 3 is not evaluated to 6.

    The macro would have to work with all sorts of formulas with or without brackets.

    The alogrithm I had in mind is

    LOOP worksheet for formula cells

    in each formula cell cast the content of the formula as a string and loop characters until a operand (+, -, *, /) is reached (keeping track of the number of brackets).

    If the number of "(" is equal to the number of ")" then the formula can be evaluated in a different cell and the result substituted back to the original formula.

    If the number of "(" is not equal to the number of ")" remove the outer ")" until they are equal.

    Repeat until the end of the formula is reached

    Then add (') to the start of the string to prevent excel from performing a calculation.


    I know this is a big ask but does anyone have any ideas on whether this is achievable and how I should code this?

    Thanks in advanced

  • #2
    Re: Macro to perform evaluate function on each cell

    anyone have any ideas?

    Comment


    • #3
      Re: Macro to perform evaluate function on each cell

      Hi qwertyjuan

      Welcome to ozgrid

      This is a big ask and no walk in the park. What are your reasons for wanting to do this, perhaps there is a better way?

      If the number of "(" is equal to the number of ")" then the formula can be evaluated in a different cell and the result substituted back to the original formula.
      You can convert formulae to its value by Copying it, then going to Edit>Paste Special - Values

      If the number of "(" is not equal to the number of ")" remove the outer ")" until they are equal.
      The parenthesis will have to be equal already as Excel would not allow its entry.
      Then add (') to the start of the string to prevent excel from performing a calculation
      You can show all Excel formulae on a Worksheet via Tools>Options>View - Formulas, or Ctrl+`

      Comment


      • #4
        Re: Macro to perform evaluate function on each cell

        Thanks for the reply.

        The objective I want to achieve is to breakup complex formulas into simple formulas so I could trace them through with ease.

        For example if my formula was

        =VLOOKUP(A1, RangeA, 2, FALSE) * VLOOKUP(B1, RangeB,3,FALSE) * VLOOKUP(C1,RangeC, 4, FALSE)

        across 2000 cells on a spreadsheet (where the ranges could vary) it would be very difficult to follow the formula step by step.

        Excel's evaluate function (Tools -> Evaluate) allows you step through a formula step by step however I don't want to be clicking on every cell to step them through.

        Instead I would like to breakup complex formulas into simple formulas evaluate them elsewhere and substitute the result back into the original formula.

        The example above if the first VLOOKUP on a particular cell returned 3 the second returned 2 and the third return 2, the cell that contained that formula or even a cell on a different worksheet I would want to display "=3*2*2".

        I understand that if the escape character (') is not added before an equals sign in excel the formula will be evaluated and I would get 8. Hence the need to attach the escape chacter (')

        Comment


        • #5
          Re: Macro to perform evaluate function on each cell

          Why not use Excel's Formula Auditing Toolbar?

          Comment


          • #6
            Re: Macro to perform evaluate function on each cell

            Originally posted by Dave Hawley
            Why not use Excel's Formula Auditing Toolbar?
            Because I am working with a spreadsheet with thousands of variables and jumping around worksheets is not ideal.

            Comment


            • #7
              Re: Macro to perform evaluate function on each cell

              Qwerty,

              I have been learning VBA for a few weeks and this would be good practice for me. I will start and then Dave or one of the others can join in if I get stuck.
              At least it will give you a idea of the syntax.

              I have tried to build something that will work just on one cell at the moment.

              Code:
              Option Explicit
              Dim Results(10) As String
              Dim Resultops(10) As String
              Dim ops As String
              Dim theformula As String
              Dim dump As Integer
              Dim dumpstr As String
              Dim opsno As Integer
              
              Sub formula_breakdown()
              Dim max_char As Integer
              Dim char_counter As Integer
              Dim cell_str As String
              cell_str = Range("c1").Formula
              max_char = Len(cell_str)
              Call cellresult(cell_str, 1, max_char, 0)
              dumpstr = "'"
              For dump = 1 To 10
              dumpstr = dumpstr & Results(dump) & Resultops(dump)
              Next dump
              Range("c1").Value = dumpstr
              End Sub
              
              Sub cellresult(cell_str As String, char_counter As Integer, max_char As Integer, resultcount As Integer)
              If char_counter > max_char Then Exit Sub
              ops = "*/+-"
              For opsno = 1 To Len(ops)
                      If Mid(cell_str, char_counter, 1) = Mid(ops, opsno, 1) Then
                          resultcount = resultcount + 1
                          Range("a1000").Formula = Left(cell_str, char_counter - 1)
                          Results(resultcount) = Range("a1000").Value
                          Resultops(resultcount) = " " & Mid(ops, opsno, 1) & " "
                          cell_str = "=" & Right(cell_str, max_char - char_counter)
                          max_char = Len(cell_str)
                          char_counter = 1
                      End If
              Next opsno
              If char_counter = max_char Then
                          resultcount = resultcount + 1
                          Range("a1000").Formula = Left(cell_str, char_counter)
                          Results(resultcount) = Range("a1000").Value
                          Resultops(resultcount) = " End "
              End If
              char_counter = char_counter + 1
              Call cellresult(cell_str, char_counter, max_char, resultcount)
              End Sub
              Attached Files
              locii Insight
              Locii Solutions. Business IT Training. Business Consulting. Excel Training. Business Intelligence using Excel 2010. Cyprus.

              Comment


              • #8
                Re: Macro to perform evaluate function on each cell

                You can restrict any loop to formulae cells only like below
                Code:
                Dim rCell As Range
                
                For Each rCell In Cells.SpecialCells(xlCellTypeFormulas)
                
                Next rCell

                Comment


                • #9
                  Re: Macro to perform evaluate function on each cell

                  Thanks Dave. That saved a me hours !!

                  We now have :-

                  Code:
                  Dim char_counter As Integer
                  Dim cell_str As String
                  Dim rCell As Range
                  For Each rCell In Cells.SpecialCells(xlCellTypeFormulas)
                      cell_str = rCell.Formula
                      max_char = Len(cell_str)
                      Call cellresult(cell_str, 1, max_char, 0)
                      dumpstr = "'"
                      For dump = 1 To 10
                          dumpstr = dumpstr & Results(dump) & Resultops(dump)
                      Next dump
                      rCell.Value = dumpstr
                  Next rCell
                  End Sub
                  
                  Sub cellresult(cell_str As String, char_counter As Integer, max_char As Integer, resultcount As Integer)
                  If char_counter > max_char Then Exit Sub
                  ops = "*/+-"
                  For opsno = 1 To Len(ops)
                          If Mid(cell_str, char_counter, 1) = Mid(ops, opsno, 1) Then
                              resultcount = resultcount + 1
                              Range("a1000").Formula = Left(cell_str, char_counter - 1)
                              On Error GoTo formerror
                              Results(resultcount) = Range("a1000").Value
                              Resultops(resultcount) = " " & Mid(ops, opsno, 1) & " "
                              cell_str = "=" & Right(cell_str, max_char - char_counter)
                              max_char = Len(cell_str)
                              char_counter = 1
                          End If
                  Next opsno
                  If char_counter = max_char Then
                              resultcount = resultcount + 1
                              Range("a1000").Formula = Left(cell_str, char_counter)
                              On Error GoTo formerror
                              Results(resultcount) = Range("a1000").Value
                              Resultops(resultcount) = " End "
                  End If
                  char_counter = char_counter + 1
                  Call cellresult(cell_str, char_counter, max_char, resultcount)
                  Exit Sub
                  formerror:
                  Results(resultcount) = "Formula Error"
                  Resume Next
                  End Sub
                  Qwerty,

                  One problem is that as it works through the sheet destroying the formulas , each new formula picks up the results of previous destructions. I guess you could create a two dimensional string array to keep the results in then drop then all at once at the end.

                  I could not figure how to calc the functions without putting them back into the sheet.

                  Note : The callresult sub calls itself as it walks through the formula. I am not sure if this is a good idea (Dave might tell us) but you could walk back through the formula with any lines after the call as all the all function loops close "on the way back". Puzzled me for a while.

                  HTH
                  Last edited by carlmack; November 20th, 2005, 18:52.
                  locii Insight
                  Locii Solutions. Business IT Training. Business Consulting. Excel Training. Business Intelligence using Excel 2010. Cyprus.

                  Comment


                  • #10
                    Re: Macro to perform evaluate function on each cell

                    carlmack,

                    many thanks for your effort, I tried your test file and it returned my desired result.

                    I think you have got gist of what I want to do. It would be ideal if the formula would work on a sheet by sheet basis. Some of the issues I guess would be:

                    A potential problem with replacing the existing formula (if the result of the formula is referred to by another worksheet) so perhaps the result of the breakdown is best kept on a seperate worksheet?

                    The issue with brackets. Formulas are often enclosed in brackets so we may need to deal with brackets by removing the outer brackets until the formula is evaluatable? i.e. By looping until a operand is reached we may have more "(" than ")"

                    Comment


                    • #11
                      Re: Macro to perform evaluate function on each cell

                      Originally posted by carlmack
                      I could not figure how to calc the functions without putting them back into the sheet.
                      I belive Application.Evaluate(" ") can evaluate a formula without copying it back to a cell on the sheet?

                      Comment


                      • #12
                        Re: Macro to perform evaluate function on each cell

                        Qwertyjuan,

                        Version 3 !!

                        - it works on the selected sheet only (I think it did before)
                        - adds new sheet called results
                        - processes brackets like the ops
                        - uses your application.evaluation



                        Code:
                        Option Explicit
                        Dim Results(10) As String
                        Dim Resultops(10) As String
                        Dim ops As String
                        Dim theformula As String
                        Dim dump As Integer
                        Dim dumpstr As String
                        Dim opsno As Integer
                        
                        Sub formula_breakdown()
                        Dim max_char As Integer
                        Dim char_counter As Integer
                        Dim cell_str As String
                        Dim rCell As Range
                        Dim resultssheet As Worksheet
                        Dim actsheet As String
                        actsheet = ActiveSheet.Name
                        Set resultssheet = Worksheets.Add
                        resultssheet.Name = "results" & ActiveWorkbook.Sheets.Count
                        Worksheets(actsheet).Select
                        For Each rCell In Cells.SpecialCells(xlCellTypeFormulas)
                            cell_str = rCell.Formula
                            max_char = Len(cell_str)
                            Call cellresult(cell_str, 1, max_char, 0)
                            dumpstr = "'"
                            For dump = 1 To 10
                                dumpstr = dumpstr & Results(dump) & Resultops(dump)
                                Results(dump) = ""
                                Resultops(dump) = ""
                            Next dump
                            resultssheet.Range(rCell.Address).Value = dumpstr
                        Next rCell
                        End Sub
                        
                        Sub cellresult(cell_str As String, char_counter As Integer, max_char As Integer, resultcount As Integer)
                        If char_counter > max_char Then Exit Sub
                        ops = "*/+-()"
                        For opsno = 1 To Len(ops)
                                If Mid(cell_str, char_counter, 1) = Mid(ops, opsno, 1) Then
                                    resultcount = resultcount + 1
                                   On Error GoTo formerror
                                    Results(resultcount) = Application.Evaluate(Left(cell_str, char_counter - 1))
                                    Resultops(resultcount) = " " & Mid(ops, opsno, 1) & " "
                                    cell_str = "=" & Right(cell_str, max_char - char_counter)
                                    max_char = Len(cell_str)
                                    char_counter = 1
                                End If
                        Next opsno
                        If char_counter = max_char Then
                                    resultcount = resultcount + 1
                                    On Error GoTo formerror
                                    Results(resultcount) = Application.Evaluate(Left(cell_str, char_counter))
                                    Resultops(resultcount) = " End "
                        End If
                        
                        char_counter = char_counter + 1
                        Call cellresult(cell_str, char_counter, max_char, resultcount)
                        Exit Sub
                        formerror:
                        If Left(cell_str, char_counter - 1) <> "=" Then
                        Results(resultcount) = "Formula Error"
                        End If
                        Resume Next
                        End Sub
                        Attached Files
                        Last edited by carlmack; November 20th, 2005, 20:21.
                        locii Insight
                        Locii Solutions. Business IT Training. Business Consulting. Excel Training. Business Intelligence using Excel 2010. Cyprus.

                        Comment


                        • #13
                          Re: Macro to perform evaluate function on each cell

                          Excellent,

                          Just a few things I noticed.

                          I don't think () should not be treated in the same manner as operands (i.e. as delimiters). I think this will cause formulas that depend on () to fail (e.g Vlookup).

                          Probably a better approach to a broken up formula that cannot be evaluated is to display the original formula rather than formula error.


                          Many thanks for your help
                          Last edited by qwertyjuan; November 20th, 2005, 20:41.

                          Comment


                          • #14
                            Re: Macro to perform evaluate function on each cell

                            I added the bracket counting

                            Code:
                            Dim max_char As Integer
                            Dim char_counter As Integer
                            Dim cell_str As String
                            Dim rCell As Range
                            Dim resultssheet As Worksheet
                            Dim actsheet As String
                            actsheet = ActiveSheet.Name
                            Set resultssheet = Worksheets.Add
                            resultssheet.Name = "results" & ActiveWorkbook.Sheets.Count
                            Worksheets(actsheet).Select
                            For Each rCell In Cells.SpecialCells(xlCellTypeFormulas)
                                cell_str = rCell.Formula
                                max_char = Len(cell_str)
                                Call cellresult(cell_str, 1, max_char, 0)
                                dumpstr = "'"
                                For dump = 1 To 10
                                    dumpstr = dumpstr & Results(dump) & Resultops(dump)
                                    Results(dump) = ""
                                    Resultops(dump) = ""
                                Next dump
                                resultssheet.Range(rCell.Address).Value = dumpstr
                            Next rCell
                            End Sub
                            
                            Sub cellresult(cell_str As String, char_counter As Integer, max_char As Integer, resultcount As Integer)
                            If char_counter > max_char Then Exit Sub
                            ops = "*/+-"
                            If Mid(cell_str, char_counter, 1) = "(" Then OpenBrackets = OpenBrackets + 1
                            If Mid(cell_str, char_counter, 1) = ")" Then CloseBrackets = CloseBrackets + 1
                            For opsno = 1 To Len(ops)
                                    If Mid(cell_str, char_counter, 1) = Mid(ops, opsno, 1) Then
                                        If OpenBrackets = CloseBrackets Then
                                            resultcount = resultcount + 1
                                            On Error GoTo formerror
                                            Results(resultcount) = Application.Evaluate(Left(cell_str, char_counter - 1))
                                            Resultops(resultcount) = " " & Mid(ops, opsno, 1) & " "
                                            cell_str = "=" & Right(cell_str, max_char - char_counter)
                                            max_char = Len(cell_str)
                                            char_counter = 1
                                        End If
                                    End If
                            Next opsno
                            If char_counter = max_char Then
                                        resultcount = resultcount + 1
                                        On Error GoTo formerror
                                        Results(resultcount) = Application.Evaluate(Left(cell_str, char_counter))
                                        Resultops(resultcount) = " End "
                            End If
                            
                            char_counter = char_counter + 1
                            Call cellresult(cell_str, char_counter, max_char, resultcount)
                            Exit Sub
                            formerror:
                            If Left(cell_str, char_counter - 1) <> "=" Then
                            Results(resultcount) = Left(cell_str, char_counter - 1)
                            End If
                            Resume Next
                            End Sub
                            The bracket logic only seems to work on simple formulas. Any ideas on how to extend the logic ?
                            Attached Files
                            Last edited by carlmack; November 20th, 2005, 21:52.
                            locii Insight
                            Locii Solutions. Business IT Training. Business Consulting. Excel Training. Business Intelligence using Excel 2010. Cyprus.

                            Comment


                            • #15


                              Re: Macro to perform evaluate function on each cell

                              Looking good!

                              . What is an example where the previous logic would fail? Probabaly where a evaluatable formula is enclosed in a condition statment?

                              Eg
                              =IF(VLOOKUP(A1,RangeA,2,FALSE)*VLOOKUP(B1,RangeB,2,FALSE) = C,"Yes","No")

                              I guess what we can do is if the formula cannot be evaluated discard text from the outer "(".

                              The objective is to evaluate the about as IF(A*B=C, "Yes", "No")
                              and not any further
                              Last edited by qwertyjuan; November 21st, 2005, 05:43.

                              Comment

                              Working...
                              X