vba - Performance hit when selecting dynamic menu items for an Excel custom UI -


i have stitched excel workbook dynamically populates custom ui drop down menus.

here's code populates menu

sub getfilterbyteam(control iribboncontrol, byref content)  content = "<menu xmlns=""http://schemas.microsoft.com/office/2009/07/customui""><button id=""team1"" label=""team1"" onaction=""filterteam""/><button id=""team2"" label=""team2"" onaction=""filterteam""/><button id=""team3"" label=""team3"" onaction=""filterteam""/><button id=""team4"" label=""team4"" onaction=""filterteam""/><button id=""team5"" label=""team5"" onaction=""filterteam""/><button id=""team6"" label=""team6"" onaction=""filterteam""/></menu>"  end sub

when select value in dynamicmenu macro fired. macro grabs id of dynamic button

here's code

sub filterteam(byref control iribboncontrol)      on error resume next     worksheets("filter").listobjects("filtertable").databodyrange.rows.delete      call addtofilterlist(paramteamname:=replace(control.id, "_", chr(32)))     call filter  end sub

short explanation of above

  1. on error skip on errors table "filter" empty
  2. addtofilter explained below
  3. filter runs advancedfilter macro on main table criteria in table populated addtofilter

here's problem.

performance great first item in dynamicmenu. rounds down 0 milliseconds. however, other buttons in dynamicmenu have performance on order of 200 milliseconds.

by measuring calls, identified 

call addtofilterlists(....

part what's causing. i've tried passing simple string (for testing purposes) instead of control.id doesn't help

i @ lose. think i'm making small mistake can't figure out life of me.

here's code addtofilterlist

sub addtofilterlist( _     optional param1 string = "", _     optional param2 string = "", _     optional param3 string = "", _     optional param4 string = "", _     optional param5 string = "", _     optional param6 string = "", _     optional param7 string = "", _     optional param8 string = "", _     optional param9 string = "", _     optional param10 string = "", _     optional param11 string = "", _     optional param12 string = "", _     optional param13 string = "", _     optional param14 string = "")      dim lo listobject     dim onewrow listrow      set lo = sheets("filter").listobjects("filtertable")      set onewrow = lo.listrows.add(alwaysinsert:=true)      onewrow.range.cells(1, column1).value = param1     onewrow.range.cells(1, column2).value = param2     onewrow.range.cells(1, column3).value = param3     onewrow.range.cells(1, column4).value = param4     onewrow.range.cells(1, column5).value = param5     onewrow.range.cells(1, column6).value = param6     onewrow.range.cells(1, column7).value = param7     onewrow.range.cells(1, column8).value = param8     onewrow.range.cells(1, column9).value = param9     onewrow.range.cells(1, column10).value = param10     onewrow.range.cells(1, column11).value = param11     onewrow.range.cells(1, column12).value = param12  end sub

commenting out of onewrow = lines helps can't figure out why performance dependent on value select in dynamicmenu. , effect repeatable , quantifiable.

maybe change (not tested) - send row range object

option explicit  public sub addtofilterlist(byref rng range)      dim lo listobject, nr long, rc long      if rng.rows.count = 1 , rng.columns.count <= 12          worksheets("filter")              set lo = .listobjects("filtertable")              nr = lo.databodyrange.row + lo.databodyrange.rows.count 'new row below table              rc = rng.columns.count - rng.column              .range(.cells(nr, lo.databodyrange.column), .cells(nr, rc)).value2 = rng.value2          end     end if end sub

Comments

Post a Comment

Popular posts from this blog

resizing Telegram inline keyboard -

telegram's inline keyboard great feature lots of different use cases. inline buttons added list of items this: inline_keyboard = [[inlinekeyboardbutton(text="button", callback_data="button"), inlinekeyboardbutton(text="reset",callback_data="reset")]] inline_keyboard_markup = inlinekeyboardmarkup(inline_keyboard) update.message.reply_text("hi", reply_markup=inline_keyboard_markup) the above code adds 2 buttons each half width of chat screen. i know the normal keyboard button there resize_keyboard parameter somehow can used. my question is there way resize inline buttons ? example make full width or quarter width. unfortunately, can't now. :( you can suggest @botsupport , might add feature next version.
Read more

command line - How can a Python program background itself? -

consider following python command-line program. doesn't work because incomplete, think illustrates problem: #!/usr/bin/python3 import multiprocessing pool = multiprocessing.pool() task in expensive_iterator_with_user_interaction(): pool.call_async(expensive_computation, (task,)) pool.close() print("dear user, may press ctrl-z , bg script.") pool.join() thus, problem scripts needs user input while it's launching expensive parallel processes. there moment when user interaction not needed anymore. then, ask user background process shell actions. however, can achieved program itself? (side note: core of problem entanglement of user input firing “expensive computations”. therefore, cannot separate both in 2 scripts, , letting 1 spawning other daemon.) the command screen may you. use screen -l create new session , run application. after inputting data, can press " ctrl-a " , press " d " on keyboard detach session. now...
Read more

php - "cURL error 28: Resolving timed out" on Wordpress on Azure App Service on Linux -

Image
my wordpress installation on azure app service on linux using custom docker container has slow response times. pages take around 20-40 seconds load. i have troubleshooting plugin installed indicates problem "curl error 28: resolving timed out after n milliseconds" when making requests following urls https://api.wordpress.org/core/version-check/1.7/ http://api.wordpress.org/core/version-check/1.7/ https://api.wordpress.org/plugins/update-check/1.1/ http://api.wordpress.org/plugins/update-check/1.1/ https://api.wordpress.org/themes/update-check/1.1/ http://api.wordpress.org/themes/update-check/1.1/ curl works fine on scm-site command line. example works ok. curl -x post http://api.wordpress.org/core/version-check/1.7/ edit if ssh container , run php code, works fine. <?php $url = 'http://api.wordpress.org/core/version-check/1.7/'; $fields = array( 'version' => urlencode('4.8.1'), 'php' => urlencode(...
Read more