How much faster / better are COM Add-Ins?

[gazpage]

Hi all,

Title says it all really. I'm trying to decide if it is worth the effort to chase through my company's policies to get VB Studio and learn how to write some Excel Com Add-Ins. I need to write some code that will take some time to run (information flow reports, unique formula analyser etc) and wondering how much faster such code would run if in a COM Add in rather than a regular excel add in.

I am an intermediate+ user of VBA (e.g. I have used dictionaries, class modules, userforms etc, but not so often that I am an expert) so I don't really have any concerns about being able to pick up how to do it from guides on the web.

Thanks
Gary

 

[Kyle123]

It depends where your bottle necks are. If you are simply automating Excel they're unlikely to be any faster - most bottle necks are caused by interacting with the Excel object model.

If you're doing other things then you may get significant improvements.

 

[gazpage]

Can you elaborate a little on the bottlenecks?

First code will basically be running through every formula and checking if it has been copied from the cell above or to the left. It will then produce an output showing where the unique formulae are.
Second code will run through every formula and look for links to other worksheets and then summaries in a grid showing precedents and dependents.

Not really what I think about as automation, ie not automating something I could manually do in excel.

Thanks for replying
Gary

 

[Kyle123]

Could you post your code? Reading and writing cells in a loop is about the slowest thing you can do so you're unlikely to get much performance benefit from a com add in

 

[gazpage]

I don't have the code yet unfortunately. I was trying to ascertain if I should try to write as a COM Add In from the start as I had read they were faster. If I write the code in excel VBA would it be very difficult to convert to the code for a COM Add in?

Pseudo code would be

For each sheet in workbook
For each cell in sheet
IsUniqueFormula(cell)
Somehow output result (maybe shade the cell if it is a unique formula
next cell
next sheet

function IsUniqueFormula(cell as range) as Boolean
Check if formula is a copy down or copy from the cell on the left (NB I haven't worked out how to do this yet)
IsUniqueFormula =True/False depending on above
end function


The second code would be the same but check the formula for links to other worksheets, and count them up.

Thanks
Gary