Challenging Macro to perform evaluate function on each cell

[qwertyjuan]

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

 

[qwertyjuan]

anyone have any ideas?

 

[Greg Wilson]

I don't think you got an answer because IMHO the logic seems flawed. As you
are aware, formula often involve complex nesting with worksheet functions
contained within other worksheet functions serving as arguments. Operands of
course can also be contained within these nested structures. Your post seems
to imply that the entire formula needs to be dissected and all functions
reduced to their results.

So, if one goes through the process of reducing functions to their results
and substituting these in the original formula, what is the point if these
are child to other functions which in turn get reduced? Ultimately, after a
complex process, all one is left with are the results of first level
functions and the first level operands (if any) separating them. Why not just
evaluate first level functions and display these? Or am I missing something?

If you intend to pursue this, a few suggestions:

1. Instead of looping you can find cells containing formula using the
SpecialCells method:

Dim r As Range, c As Range
On Error Resume Next
Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
For Each c In r.Cells
MsgBox c.Address
Next
On Error GoTo 0

2. Instead of using a single quote character you can format the cell as text:

Range("J10").NumberFormat = "@"

3. Instead of transfering portions of the original formula to other cells
and using the cells to calculate the results you can do this
programmatically. For example, the expression: Application.Evaluate("= 5 * 2
+ 12") returns 22. The equals sign is not actually necessary.

Demo:
Assume J10 contains the formula "=VLOOKUP(A1, RangeA, 2, FALSE) + 5 * 2 +
12". Suggested is that you paste this formula to J10 and run the following
code:

Dim t As String
Dim pos As Integer
t = "Cell formula:" & vbCr & Range("J10").Value
MsgBox t
pos = InStrRev(t, ")")
t = "= " & Right(t, Len(t) - pos - 3)
MsgBox "Reduced formula:" & vbCr & t
With Application
MsgBox "Reduced formula result:" & vbCr & .Evaluate(t)
t = Left(t, Len(t) - 5)
MsgBox "Second reduction of formula:" & vbCr & t
MsgBox "Reduced formula result:" & vbCr & .Evaluate(t)
End With

Hope this was of some benefit.

Regards,
Greg

 

[Greg Wilson]

If the complexity I described doesn't exist or you are happy with displaying
only the results of first level functions with their operands then this
shouldn't be a big deal. I don't think anybody attempted it for the reason I
described.

Regards,
Greg

 

[qwertyjuan]

Thanks for your reply greg,

You made an excellet point with the formula becoming the argument t
another formula. (It didn't really occured to me and probably would o
occured to me when I started coding)

Maybe an alternative is to evaluate only the active sheet by copyin
the results onto a different sheet? The results then would not interfe
with the overall model of the spreadshee

 

[Greg Wilson]

I would be more insightful if I understood the intended goal. Are you trying
to create some kink of worksheet formula analyser?

Greg