My loops are too slow

[cheeseboy]

hello all,

I've created a loop which is supposed to read through a series of cells and change all the html tags to be the actual font representation of them. So a bit of text with the html bold tags around such as <b>hello world </b> becomes hello world. However, the loops I've used to do this are incredibly slow. The code is below, can anyone suggest a better way of doing this please ?

Many thanks

Please Login or Register  to view this content.

[Ikaabod]

For starters you can try putting at the beginning of each sub:

Please Login or Register  to view this content.

and at the end of each sub:

Please Login or Register  to view this content.

[Richard Buttrey]

You don't say how many cells are involved, but don't forget that the way you've written it, you are jumping backwards and forwards between the VBE and the Excel Application every time you update a cell. There is a big time overhead attached to this if you're dealing with lots of cells.

I've experienced this before and found that in situations like this it is best to read the whole set of cells into a virtual array within the VBE. Then do your processing on the array entirely within the VBE, and at the very end write the revised array back in one go to the Excel application.

HTH

[Dav]

You also aew using many if statements, when only one of the conditions could be true in that instance, for example

Please Login or Register  to view this content.

could be

Please Login or Register  to view this content.

this would perhaps speed up the code as it would stop evaluating the subsequent expressions

also

wordlen = Len(Excel.ActiveCell)

the expression you are looking to match is at least 3 charactes long so this could be changed to
wordlen = Len(Excel.ActiveCell)-2 as it is impossible to get a match in the last 2 cell


Regards

Dav

[avveerkar]

Hi Cheeseboy,

Perhaps we could do something.

Just want to know- Will any cell text have have mutiple HTML tags? Meaning would it have more than one <B> or <I> etc or could it have mutiple of all the tags? If we know that no cell will have more than one tag, then we can reduce the execution time. But if a cell is likely to have mutiple and all combinations of the tags then the loops could be longer.

Veerkar