Convert Excel formulas containing functions to a simple form

syedalisajjad

New Member
Joined
Sep 11, 2014
Messages
13
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

ShawnPCooke

Board Regular
Joined
May 14, 2007
Messages
151
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

New Member
Joined
Sep 11, 2014
Messages
13
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,651
Messages
5,838,577
Members
430,557
Latest member
MK15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top