VBA .AddComment makes script extremely slow?

[bertjeiv]

Hello all,
I have written a script that compares 2 files and makes a 3rd with the differences and it works fine. i takes about a minute to process the entire task. now i wanted to add some more info in the 3rd file by adding comments to each compared cell by adding this line (just for test variables will be added later)

Cells(r, c).AddComment.Text Text:="Comparator:"

but now the code is EXTREMELY slow. It takes about 30 minutes to process...
is the addcomment slow or am i doing something wrong?

thanks
Bert

 

[bertjeiv]

nope it always go to the last cell which contains data i check on all sheets
I'm going to upload to a webhost so you dont need to do all that "free users" mumbo jumbo.

I'm also going to run the script without the formatting but i'm pretty sure its only the commenting that makes it so slow maybe i should try to save it as a .xls file instead of .xlsx maybe something changed on how they handle comments.

 

[sous2817]

a file saved in 2007 or greater format will be considerably smaller in file size than a 2003 and earlier. If you can upload it to another file sharing site, I'll try and take a look.

 

[bertjeiv]

here better download links
so with comments 12mb
http://www.bert-sam.com/Compare.xlsx
without 1mb
http://www.bert-sam.com/Compare.xls

 

[sous2817]

Sorry to be a bother, but can you post a sheet that has the VBA in it but before you run the code? I looked through the 1meg file but didn't see any VBA.

 

[bertjeiv]

I'm sorry but i cannot because it contains sensitive data but the entire code is as i posted.

Now i'm testing without comment but with validation input message which works fine until at some point it stops and gives me a 1004 error without any other info

With Cells(r, c).Validation
        .Delete
        .Add Type:=xlValidateInputOnly
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = "test"
        .ErrorTitle = ""
        .InputMessage = "body"
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With