Sumproduct Utility - willing testers required

[DonkeyOte]

This is I guess a bit of a duped thread but I kicked off a post a while back about building a utility to analyse Sumproduct Formulae ... given that the post now has 150 posts and over a 1000 hits (made up by about 5 of us!) I'm guessing the majority of MrE people won't be following it (I tend to avoid those as I know it's being dealt with by other people!) ... post is here: http://www.mrexcel.com/forum/showthread.php?t=348519

Anyway to cut a long story short I have now finally put the beta version of this code into a bog standard .xla file... the things is I now really need as many people to test out for me as possible so I can be sure it's working ... it has worked thus far on anything myself & Emma (CG) have thrown at it but you never know... therefore the more of you willing to do me a favour and test it out the better

The .xla file can be downloaded from here:

http://www.box.net/shared/6s1rdmtob0

There is also a sample .xls file which you can download if you wish, this file contains a mixture of sumproduct formulae (in various guises in green cells) on which the .xla can be tested (and output reviewed):

http://www.box.net/shared/73mg4fdj4t

In essence once the add-in has been installed you will get a very basic floating toolbar with "Analyse Sumproduct" on it... if you highlight any Sumproduct formula and hit the button you should get a new sheet appear with the breakdown of how the formula was computed in terms of a numerical matrix... should the "component" arrays be multi dimensional (ie > 1 column) you will find the answer column is itself a Sumproduct formula and can be drilled into also.

In short this is meant more as a training aid to help people understand how sumproduct works and the basic premise behind it was to automate xld's illustrations on the subject which can be found here:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

I personally will also find it useful to audit formulae etc where I'm getting unexpected results etc or if I am receiving error result etc...

Big thanks to Emma, Rory & Richard for their help on this and also xld who is doing some work on further developing the concept. I should add that without both Rory & Richard's expert insight in terms of some of the fundamentals (like how to really use the VBA Evaluate function!) this would never have got off the ground.

Thanks for reading and thanks also in advance to anyone who provides feedback be it good or bad.

Cheers,
Luke

 

[kanwal_no1]

Hi,

I am not able to find Sumproduct Analyse.xla. Is it still available for download.

Regards
Kanwaljit

 

[ZVI]

Hi Luke!

Very interesting idea with impressive result, thank you and all collaborators!

Will try to play with Add-In. But firstly I have investigated the issue mentioned by JugglingReferee and would confirm its presence.

The source of a problem is in a method the CommandBars("SPA") is installed. Add-in, being loaded first time, sets a full pathname to OnAction property of its command bar button. But uninstallation does not remove CommandBars("SPA") , just switches off its visibility. Thus, next time being reinstalled or reloaded from another folder the Add-In only sets visibility of already existing CommandBars("SPA") , but the old full pathname is still present in its OnAction property. As a result it cause to Add-In macro calling error (macro not found).

The issue can be solved by adding this fixing sub:

Private Sub Workbook_Open()
  On Error Resume Next
  With Application.CommandBars("SPA")
    .Visible = True
    .Controls(1).OnAction = "Analyse_SumProduct"  ' Without path!
  End With
End Sub

Removing of Add-Ins’ command bar at uninstallation is desirable too and could be made as follows:

Private Sub Workbook_AddinUninstall()
  On Error Resume Next
  Application.CommandBars("SPA").Delete
End Sub

User friendly auto positioning of Add-Ins’ command bar at installation could be added as well:

Private Sub Workbook_AddinInstall()
  With Application.CommandBars("SPA")
    .Visible = True
    .Position = msoBarTop
  End With
End Sub

Surely, all code is in ThisWorkbook module.

Thank you for cool tool ones more!

Regards,
Vladimir

 

[DonkeyOte]

Kanwaljit the .xla should still be available using the last link in the thread:

http://www.box.net/shared/zvugsqc5zv
Note: I've updated this today per Vladimir's post to account for the add-in being relocated post installation and/or being removed & reinstalled in different locations - so thanks for that Vladimir.

An .xls file is also still available for download which can be used to test/display the output of the above for various sumproduct formulae:
http://www.box.net/shared/73mg4fdj4t

Thanks for the interest -- please post back if you encounter any other bugs etc...

 

[kanwal_no1]

Hi,

Thanks for providing the link. A perfect tool I would say. Over the past half month I was looking for a tool which can provide the arrays evaluated in Sumproduct and it is just what I wanted. I have started using it and would respond as and when it is appropriate or desired.

Just a quick thought. It would look better if SPA is given some FACE ID or Icon instead of "Analyse Sumproduct". And that would save screen space too.

Regards
Kanwaljit

 

[irfananeeza]

Hello,

I downloaded Sumproduct_Analyses.xla from the below link. http://www.box.net/shared/zvugsqc5zv

I saved it on my desktop. When I open the file I see a blank grey screen with a little box on the right hand side that say's "SPA" and "Analyse sumproduct". When I clik on the "Analyse Sumproduct" I get an error message saying
"Run time error 91
Object variable or With block variable is not set"

Can you tell me what am I doing wrong and how can I make this add in working.?
Thanks.

 

[DonkeyOte]

OK an add-in does not work in the same way as a standard .xls(*) file.

I'll outline a basic process for installation but first...

a) Quit your current XL

b) Better to store the add-in somewhere other than your Desktop -- the most common place for these to reside is:

C:\Program Files\Microsoft Office\Office x\Library
Where x dependent upon the version you're running (Office 2007 = 12 for ex.)

Make a note of the location.

Once Add-in saved and location noted, open Excel -- then:

-- Tools -> Addins (or use ALT + T -> I)
-- Click Browse
-- Navigate to the Location of the Add-in File (eg in the previously mentioned folder)
-- Select Sumproduct_Analyse.xla
-- Click OK
-- Click OK (again)

Now the Add-in is ready to use.

Open a spreadsheet and select a cell with a Sumproduct Formula in it and press the button to get a breakdown of the calculation.

If you need examples use the additional file available for download (.xls) which has a variety of sumproducts available for testing/review.

 

[kanwal_no1]

Hi,

I have tested your marvellous utility. In one of my files the utility is not giving correct results. I can upload the file for you, but don't know how to do it.

Regards
Kanwaljit

 

[arkusM]

"that'll do Donkey, that'll do"...

Wow, nice ultility! The xla "install" worked perfectly.
I will see if I can teach people how to use the Sumproduct construct with this great little tool.

Cheers!

 

[DonkeyOte]

kanwal_no1 -- I have PM'd you such that you can mail me the offending formula etc so I can replicate / investigate... I've experienced a few issues myself when using named ranges but not had the time to look into in great depth.

arkusM, thanks... I hope it helps... kind of how I came up with the idea myself... I saw one of Aladin's formulae and couldn't get my head around what exactly it was generating and wanted to find out... just to reiterate this is not all my own work -- Rory (rorya), Richard (Schollar) & Emma (CornflakeGirl) all made MAJOR contributions... I was just the doley who had time on his hands to put it all together.

 

[DonkeyOte]

Just as a follow up in response to kanwal_no_1's posts... there is an issue using the tool if the Sumproduct formula contains INDIRECT references either in the formula itself or via Named ranges ... use of ROW() etc within referenced named ranges can also cause issues but not 100% of the time.

At present I can't think of an obvious solution to the above issues so will have to go back to the gurus (the R's) and see if they can think of a work around.

Thanks all.