{"id":2543,"date":"2021-05-31T09:15:42","date_gmt":"2021-05-31T09:15:42","guid":{"rendered":"https:\/\/help.hbsoftware365.com\/?p=2543"},"modified":"2021-07-05T10:35:37","modified_gmt":"2021-07-05T10:35:37","slug":"exsion-vba-tips-tricks","status":"publish","type":"post","link":"https:\/\/help.hbsoftware365.com\/smarttrade\/st-about-smarttrade-help\/st-intro\/exsion-help-eng\/exsion-eng-08-vba-tips-eng\/exsion-vba-tips-tricks\/","title":{"rendered":"VBA tips & tricks"},"content":{"rendered":"\n

\n\t\tAutomatic procedures using VBA\n\t<\/h1>\n\t

Exsion has the ability to perform your own VBA procedures before or after the data refresh. Furthermore, it is also possible the other way around to perform the “Refresh data” function from within the own procedure. Keep in mind that errors that occur within your own procedure can affect the operation of Exsion. In case of problems, always test first whether the error also occurs with standard use of Exsion, without own procedures.<\/p>\n\t

Call from your own procedure<\/h2>\n

The Refresh data function can be called from your own VBA procedure. Where you want to refresh the data in your own procedure, add the following line in the code via the VBA editor (ALT-F11):<\/p>\nApplication.Run “Exsion.xlam!MENU_DATA”\n\n

Executing this line has exactly the same effect as pressing the “Refresh data” button\u00a0 in the Exsion menu. This way you can also call the Formulas to values menu option:<\/p>\nApplication.Run “Exsion.xlam!MENU_VALUES”\n\n

To bypass Exsion’s warning message box use the following:<\/p>\nDim s_displayAlertsSetting As Boolean\ns_displayAlertsSetting = Application.DisplayAlerts\nApplication.DisplayAlerts = False\nOn Local Error Resume Next\nApplication.Run “Exsion.xlam!MENU_VALUES”\nOn Local Error GoTo 0\nApplication.DisplayAlerts = s_displayAlertsSetting\n\t

Data refresh execution procedure<\/h2>\n

It is possible to perform a procedure just before the data refresh. To do this, include the following code in a module. Instead of [Own procedure name], enter the name of the procedure you want to perform. This procedure must be present in the same project.<\/p>\nSub Exsion_beforerefresh()\n Call [Own procedure name]\nEnd Sub\n\n

To abort the Data refresh procedure you can exit your own procedure after entering this line:<\/p>\nErr.Source = “exsion_cancel”\n\t

Execute procedure after data refresh<\/h2>\n

In addition to performing a procedure just before the data refresh, it is also possible to perform a precedure right after the data refresh. To do this, include the following code in a module. Instead of [Own procedure name], enter the name of the procedure you want to perform. This procedure must be present in the same project.<\/p>\nSub Exsion_onrefresh()\n Call [Own procedure name]\nEnd Sub\n\n

\n\t\tExample for a Refresh after cell change\n\t<\/h2>\n\t 
\nPlace this macro in the ThisWorkbook section.\nPrivate Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)\n If Target = ActiveSheet.Range(“Subcategory”) Then\n\tApplication.Run “‘Exsion.xlam’!MENU_DATA”\n\tActiveSheet.Calculate\n End If\nEnd Sub\n\n\t

\u00a9HB Software B.V.<\/p>\n\n","protected":false},"excerpt":{"rendered":"

Automatic procedures using VBA Exsion has the ability to perform your own VBA procedures before or after the data refresh. Furthermore, it is […]<\/p>\n","protected":false},"author":6,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-site-content-layout":"","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"disabled","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[68],"tags":[],"class_list":["post-2543","post","type-post","status-publish","format-standard","hentry","category-exsion-eng-08-vba-tips-eng"],"_links":{"self":[{"href":"https:\/\/help.hbsoftware365.com\/smarttrade\/st-about-smarttrade-help\/st-intro\/wp-json\/wp\/v2\/posts\/2543","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/help.hbsoftware365.com\/smarttrade\/st-about-smarttrade-help\/st-intro\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/help.hbsoftware365.com\/smarttrade\/st-about-smarttrade-help\/st-intro\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/help.hbsoftware365.com\/smarttrade\/st-about-smarttrade-help\/st-intro\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/help.hbsoftware365.com\/smarttrade\/st-about-smarttrade-help\/st-intro\/wp-json\/wp\/v2\/comments?post=2543"}],"version-history":[{"count":18,"href":"https:\/\/help.hbsoftware365.com\/smarttrade\/st-about-smarttrade-help\/st-intro\/wp-json\/wp\/v2\/posts\/2543\/revisions"}],"predecessor-version":[{"id":2788,"href":"https:\/\/help.hbsoftware365.com\/smarttrade\/st-about-smarttrade-help\/st-intro\/wp-json\/wp\/v2\/posts\/2543\/revisions\/2788"}],"wp:attachment":[{"href":"https:\/\/help.hbsoftware365.com\/smarttrade\/st-about-smarttrade-help\/st-intro\/wp-json\/wp\/v2\/media?parent=2543"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/help.hbsoftware365.com\/smarttrade\/st-about-smarttrade-help\/st-intro\/wp-json\/wp\/v2\/categories?post=2543"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/help.hbsoftware365.com\/smarttrade\/st-about-smarttrade-help\/st-intro\/wp-json\/wp\/v2\/tags?post=2543"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}