excel - #GETTING_DATA message never resolves when calculation triggered from VBA -

i'm trying build range-level refreshing of cube functions large cube-formula based workbook, since build in batching of these functions invariably performs dreadfully on cube available when whole workbook refreshed (hours , hours of waiting).

the code i'm trying use pretty simple:

public sub refreshrange()     dim rngtarget excel.range     dim lngrescue long     dim blncalccheck boolean      set rngtarget = selection             rngtarget.dirty         rngtarget.calculate         doevents               '' used reluctantly in case vba blocks event                                '' causes recalc         blncalccheck = blncalccheck or thisworkbook.connections("myconnection").oledbconnection.refreshing         lngrescue = lngrescue + 1         sleep 200              '' api sleep function. tried application.wait.         if lngrescue >= 200 debug.assert false      loop until not fblnisgettingdata(rngtarget) '' function doesn't                                                  '' break things. evaluates                                                 '' formula rngtarget checks                                                 '' if of cells read                                                  '' #getting_data  end sub 

this doesn't work. formulas calculate, resolving #getting_data, connection never begins process of collecting data go in them. begins, varying degrees of reliability if i

• recalculate sheet (shift-f9) manually. works around 50% of time.
• recalculate whole workbook manually. works around 80% of time.
• set calculation mode automatic. works (so far), workbook-level.

it may begin working if call application.calculateuntilasyncqueriesdone, i'm not sure workbook level function , more's point, consistently seems crash workbook (i'd guess because i'm using udfs in used names, not sure).

does have idea how manually fire whatever event kicks off whole "running background query" message range? or alternatively, how can watch excel.exe process in order find out , potentially fire same thing via api?

the reason doesn't work because, whatever reason, post calculation events trigger asynchronous connections go , data cube value formulae cannot trigger whilst vba running. includes vba doevents called (hence code above not work).

however, event scheduled , fire - once, irrespective of how many times calculate has been called - once vba finishes doing whatever doing. makes coding around complex, not impossible, using timers (i settled on application.ontime in order able class up, api settimer work. simple approach solving problem (possible mistakes here, since i'm unused using static):

public static sub docalcevery(optional strinterval string = vbnullstring, optional wkstarget excel.worksheet, blncontinue boolean = true)     dim strinterval_inner string     dim wkstarget_inner excel.worksheet     dim blncontinue_inner boolean     dim lngcalccount long     dim datnewcalctime date     dim datpreviouscalctime date      '' update static values if new arguments provided     '' stopping little tricky - hence blncontinue     if strinterval <> vbnullstring strinterval_inner = strinterval     if not wkstarget nothing set wkstarget_inner = wkstarget     if lngcalccount = 0          blncontinue_inner = blncontinue     elseif blncontinue <> true         blncontinue_inner = false     end if      '' clear out previous ontime instances     '' (always?) errors, looks wrapped in      '' on error elsewhere, guessing in specific case, error safely     '' ignored.     on error resume next     application.ontime datpreviouscalctime, "docalcevery", , false     on error goto 0      wkstarget.calculate     lngcalccount = lngcalccount + 1     if blncontinue_inner         datnewcalctime = + cdat(strinterval)         application.ontime datnewcalctime, "docalcevery"         datpreviouscalctime = datnewcalctime     else         debug.print "calculation complete. " & lngcalccount & " iterations before stopped."     end if end sub 

this should work, , allow docalcevery called @ arbitrary time stop calculation. however, if approach used there things bear in mind if you're not familiar ontime:

• ontime valid entirety of excel session unless cancelled means if leave excel running more 24 hour, expect macro fire again. told may open closed workbooks in order accomplish effect, warned. ontime events invalidated when excel closes though.
• this means if halt code clicking stop, on error elsewhere in vba, or other means, have no way know ontime events scheduled (if know how find this, i'd love know)

however, these provisos in mind, approach allow right events fire set off cube functions. detecting when they're done, of course, different kettle of fish.