multithreading - Excel VBA QueryTable callback function after table refresh -

this question has answer here:

• excel vba - querytable afterrefresh function not being called after refresh completes 2 answers

i writing/maintaining excel vba application there multiple querytables linked ms sql server databases. users of application can alter sql query each table manipulating various ui controls on excel document.

one of issues have come across querytables there use of multi threading. each querytable on document has original state must restored after query ran. instance, if querytable1 had base query of

select * example_table 

and user selected inputs on controls create

select * example_table object_oid = '10' 

i need original state restored. code below snapshot of how accomplishing this

sub refreshdataquery() 'dependencies: microsoft scripting runtime (tools->references) dictionary (hashtable) object  dim querysheet worksheet dim interface worksheet  set querysheet = worksheets("qtable") set interface = worksheets("interface")  dim sh worksheet dim qt querytable dim qtdict new scripting.dictionary  set qtdict = utilfunctions.collectallquerytablestodict  set qt = qtdict.item("query fred2")  ''' building sql query string ''' dim sqlquerystring string dim originalquerycache string originalquerycache = qt.commandtext sqlquerystring = qt.commandtext  querybuilder.buildsqlquerystringfrominterface interface, sqlquerystring  msgbox sqlquerystring  qt.commandtext = sqlquerystring  if not qt nothing     qt.refresh else     'error messages , handling here     ' cut out keep code short end if   ''' clean '''  'restore original base sql query ' problem here ' this, or other altering statement, error out if query still refreshing qt.commandtext = originalquerycache ' other original state restoring code below...  ' free dictionary set qtdict = nothing   end sub 

ideally, if writing in modern language, create callback function or run refresh in own thread completion notifier. spent chunk of time researching how callback function qt.refresh call, having no luck. realize 'hack' around bit prefer not engage in bad practices many people have maintain in future.

this application must support excel 2010 versions , upward

so how can create callback function vba functions run in separate threads? or, should looking @ approach?

the querytables events aren't exposed except through custom class module , withevents keyword. first, create custom class module named cqtevents , put in it:

private withevents mqrytble querytable private msoldsql string  public property set qrytble(byval qrytble querytable): set mqrytble = qrytble: end property public property qrytble() querytable: set qrytble = mqrytble: end property public property let oldsql(byval soldsql string): msoldsql = soldsql: end property public property oldsql() string: oldsql = msoldsql: end property  private sub mqrytble_afterrefresh(byval success boolean)      me.qrytble.commandtext = me.oldsql  end sub 

that's 2 properties: 1 hold querytable , 1 store old sql. procedure like

sub refreshdataquery()      dim interface worksheet     dim qt querytable     dim qtdict new scripting.dictionary     dim clsqtevents cqtevents     dim sqlquerystring string      set qtdict = utilfunctions.collectallquerytablestodict     set qt = qtdict.item("query fred2")      sqlquerystring = qt.commandtext     querybuilder.buildsqlquerystringfrominterface interface, sqlquerystring      'create class events , store old sql     set clsqtevents = new cqtevents     set clsqtevents.qrytble = qt     clsqtevents.oldsql = qt.commandtext      qt.commandtext = sqlquerystring      if not qt nothing         qt.refresh 'after done, event in class fire     else         'error messages , handling here     end if  end sub 

because define mqrytble withevents, 2 events (beforerefresh , afterrefresh) exposed in class. setting cqtevents.qrytble querytable, class listens events on querytable. commandtext stored in oldsql before it's changed. when refresh done, event fires , commandtext restored. of course not refresh done in event, assume want old sql statement in there if it's refreshed or reprocessed.

next, should consider making collection class hold bunch of qtevents instances. presume code processes 1 example, you're doing more. can move collectallquerytables inside collection class , move buildsql part inside cqtevents class.