Convert Excel formulas containing functions to a simple form

[syedalisajjad]

Hello Everyone,

I am a beginner with VBA. I want write a macro to convert a formula which contains functions to a simple form. e.g. D12+SUM(E22:E26) should be converted to D12+E22-E26 or D12+SUM(E22:E26)-SUM(F12:F15)-D20+SUM(G12:G14) should be converted to D12+E22+E23+E24+E25+E26-F12+F13+F14+F15-D20+G12+G13+G14.

I have been trying a lot but no success......Can someone please help me out.......or if someone has already done such sort of thing?

Any help would be highly appreciated.

Thanks

 

[ShawnPCooke]

Here are some possibilities for you.

1) First, you have to parse the existing formula. I assume you know that you can get to the formula in a cell using the Range.Formula property. Assign that to a text value and chop off the equal sign. Next, scan through for the operators + and -. (I'm assuming there is no multiplication or division in any of this?) Load everything between those operators into an array of strings, and the operators into a different array. (Assume the first operator is positive.)

So for example:

=SUM(E6:E10)+F10-SUM(D10:D14)

This would yield two arrays:

{SUM(E6:E10),F10,SUM(D10:D14)}
{+,+,-}

2) Now, you want to look through that first array to expand it out, if necessary. I'm just going to discuss expanding SUM, but it would work similarly if you have other functions you need to expand.

First, search the string to see if the term SUM is found. If not, assume it cannot be decomposed, and move on to the next string in the array.

If you did find it, then get rid of the SUM( at the beginning and the ) at the end, and define a variable of type Range using the text left over as the range.

Now, use a For Each/Next statement to iterate through each cell in that range. For that cell, use the Range.Address property to get the text of that cell address. You can specify whether you want to use relative or absolute cell reference.

Start assembling a new string based on the operator from the second array (the + or -) and each cell reference returned. You would wind up with "+E6+E7+E8+E9+E10", for example.

3) Once you've done that for all of the elements of your array, simply concatenate them together. Make sure that no operators get duplicated in this, but if you're careful it ought to work.

 

[syedalisajjad]

Hi Shawn,

Thanks for your suggestions. I will give it a try and then get back to you guys.

Anyone else here with other ideas?