i've incorporated off-sheet dependents search using "showdependents" , "navigatearrow" vba methods. works painfully slow (for large number of dependents). there alternatives, way speed up? i've tried disabling screenupdating doesn't speed much. code based on: http://www.technicana.com/vba-for-checking-dependencies-on-another-sheet
consider following function supposed return true if cell pass has direct dependent on different sheet:
function leadsout(c range) boolean application.screenupdating = false dim long, target range dim ws worksheet set ws = activesheet c.showdependents on error goto return_false = 1 while true set target = c.navigatearrow(false, i) if c.parent.name <> target.parent.name ws.select activesheet.cleararrows application.screenupdating = true leadsout = true exit function end if = + 1 loop return_false: leadsout = false activesheet.cleararrows application.screenupdating = true end function sub test() msgbox leadsout(selection) end sub to test it, linked test sub command button on sheet1.
in a2 entered formula = a1 + 1, no other formulas on sheet1.
on sheet2 entered formula =sheet1!a2.
back on sheet1, if select a2 , invoke sub instantly pops "true". if select a1 , invoke sub returns "false" -- after delay of several seconds.
to debug it, put debug.print i right before i = + 1 in loop. immediate window, after running again, looks like:
32764 32765 32766 32767 weird!!!!! utterly stumped until replaced debug.print i
debug.print target.address(external:=true) which led output looks ends like:
[dependents.xlsm]sheet1!$a$1 [dependents.xlsm]sheet1!$a$1 [dependents.xlsm]sheet1!$a$1 [dependents.xlsm]sheet1!$a$1 navigatearrow(false,i) goes originating cell , stays there once exceeds number of dependents! seemingly undocumented , massively annoying. code linked written hasn't discovered this. kludge, should check when navigating arrows haven't returned starting point. following seems work instantly in cases, although haven't tested much:
function leadsout(c range) boolean application.screenupdating = false dim long, target range dim ws worksheet set ws = activesheet c.showdependents on error goto return_false = 1 while true set target = c.navigatearrow(false, i) if target.address(external:=true) = c.address(external:=true) goto return_false end if if c.parent.name <> target.parent.name ws.select activesheet.cleararrows application.screenupdating = true leadsout = true exit function end if = + 1 loop return_false: leadsout = false activesheet.cleararrows application.screenupdating = true end function the key lines 3 lines begin
if target.address(external:=true) = c.address(external:=true) adding such check in sub linked should make massive difference.
Comments
Post a Comment