Speed Up Working With Comments in Excel VBA -

-

this example contrived, created explain problem i'm having. want code run faster does. on new sheet each loop of cell starts fast, if let run near completion, , run again, hit 100ms per cell. in sheet have 16000 cells lot of comments this, , manipulated individually every time code runs. in example same, in real application each 1 different.

is there anyway make process faster?

option explicit public declare ptrsafe function gettickcount lib "kernel32.dll" () long public sub breakthecommentsystem() dim integer dim t long dim cell range dim dr range set dr = range(cells(2, 1), cells(4000, 8))  dim rstr string rstr = "abcdefg hijk lmnop qrs tuv wx yz" & chr(10)  = 1 5     rstr = rstr & rstr next  each cell in dr     t = gettickcount     cell         if .comment nothing             .addcomment         else             .comment                 .shape.textframe.characters.font                     .bold = true                     .name = "arial"                     .size = 8                 end                 .shape.textframe.autosize = true                 .text rstr             end         end if      end     debug.print (gettickcount - t & " ms ") next  rstr = empty = empty t = empty set cell = nothing set dr = nothing   end sub

update 12-11-2015, wanted noted somewhere in case runs it, reason trying optimize because vsto not let me add workbook file these comments. after 6 months of working microsoft, confirmed bug in vsto , excel.

https://connect.microsoft.com/visualstudio/feedback/details/1610713/vsto-hangs-while-editing-an-excel-macro-enabled-workbook-xlsm-file

according msdn comments collection , comment object documentation, can reference comments within worksheet through indexed position , deal them directly rather cycle through each cell , determine whether contains comment.

dim c long activesheet    '<- set worksheet reference properly!     c = 1 .comments.count         .comments(c)             debug.print .parent.address(0, 0)  ' .parent cell containing comment             ' stuff .comment object         end     next c end

also according official docs range.specialcells method can determine subset of cells in worksheet using xlcelltypecomments constant type parameter.

dim comcel range activesheet    '<- set worksheet reference properly!     each comcel in .cells.specialcells(xlcelltypecomments)         comcel.comment             debug.print .parent.address(0, 0)  ' .parent cell containing comment             ' stuff .comment object         end     next comcel end

i'm still unclear reasoning behind filling non-commented cells blank comment if trying work comments on worksheet better work subset of commented cells rather cycling through cells looking comment.


Comments

Post a Comment

Popular posts from this blog

rewrite asp to php based on asp query -

-
i have seen several questions similar , tried solutions @ loss. need redirect example.com/products.asp?lob=health to example.com/services/healthcare/ i need several lob= different value . there way can redirect each url. maybe kind of rewrite condition on lob if value of lob = health, redirect here. if equals home, redirect somewhere else? not sure "correct" way works. rewritecond %{query_string} ^lob=value1$ [nc] rewriterule ^products.asp$ http://example.org/sample/one/? [r=301,l] and different value: rewritecond %{query_string} ^lob=value2$ [nc] rewriterule ^products.asp$ http://example.org/sample/two/? [r=301,l] and doesn't match value: rewriterule ^products\.asp$ [insert new url here] [r=301,l] the last example works if want redirect .asp url .php. keep in mind have other conditions , rules removing .php extension , adding / files: rewritecond %{request_filename} !-f rewriterule ^([^/]+)/$ $1.php rewriterule ^([^/]+)/([^/]+)/$ /$1...
Read more

android - questions about switching from C2DM to GCM -

-
i'm converting app c2dm gcm , have questions. i've modified gcmquickstart example found here: git clone https://github.com/googlesamples/google-services.git in class extends application, i've got: public class myapp extends application { @override public void oncreate() { super.oncreate(); if (this.checkplayservices()) { intent intent = new intent(this, registrationintentservice.class); startservice(intent); } else{ log.e(tag,"failed checkforgcm"); } } } afaik, starts service , it'll run forever or if operating system shuts down whereupon operating system restart it. correct? checkplayservices() makes sure android 2.3 or greater , google play services installed these requirements use gcm. correct? public class registrationintentservice extends intentservice{ private static final string tag = "regintentservice"; public registratio...
Read more

visual studio 2010 - Error on RDLC Expression -

-
i using exp calculate total fees paid on rdlc report: =sum(iif(fields!responsedescription.value ="approved successful",int(fields!amount.value), 0)) and #error in resulting column , can issue ? . and similar exp above work fine : =sum(iif(fields!responsedescription.value <> "",int(fields!amount.value), 0)) few notes: 1- amount integer , present. 2- responsedescription string , present. thank you you can use expression: =sum(cint(iif(fields!responsedescription.value ="approved successful", fields!amount.value, 0))) you have convert every possible values same type before aggregation. i think second expression works fine because in true case ( fields!responsedescription.value <> "" ) used expression converted integer .
Read more