VBA - excel closes the previous workbook on opening the new one -


i have strange problem, suscpect it's connected version of excel, i'm not sure @ all. can't figure out alone need help. have macro, operates on fresh workbook - it's not saved anywhere, worker save manually afterwards. macro .xlam format add-in, adding couple of buttons ribbon , these buttons start code. inside code have simple lines opening new workbook, chosen earlier user:

application.displayalerts = false     set wbmpa = workbooks.open(mpa_file) application.displayalerts = true

earlier, code sets active workbook object/workbook macro work on (tried both versions):

set dwb = application.activeworkbook

and later in code

dwb.activate

or:

dwb = activeworkbook.name

and then

workbooks(dwb).activate

the lines in separate subs, variable globally declared.

the code works fine until opening of wbmpa (watching in locals time). when try open new file code above, earlier workbook (dwb) closes unknown reasons. error 1st method this: error screenshot second 1 gives simple "subscipt out of range". errors, however, not problem. problem cause of them, closing of workbook unknown reasons.

it happens when open new workbook (using excel icon on start bar) - when file -> new -> blank workbook using opened workbook, error not occur.

another strange thing - me , colleague work use 2013 version of excel. never have error, has every time.

this general scheme of code, other things meaningless in case because there no other manipulations of workbooks/worksheets.

dim dwb object dim wbmpa object  sub_1()  set dwb = activeworkbook set wbmpa = workbooks.open(mpa_file)  call sub_2  end sub    sub_2()  dwb.activate  end sub

i error on activation of dwb in sub_2, because closes god knows reason on opening of wbmpa in sub_1.

if have only opened blank workbook (clicking excel toolbar, example) , open named workbook before making any changes blank workbook, blank workbook disappear. believe normal/expected behavior.

i can't speculate why happens on 1 computer not another, how have observed new/blank documents (excel, powerpoint, word) behave, , assume normal behavior. may have different option/configuration on excel environment changing default behavior, or maybe altering blank file before running macro, , co-worker isn't, etc.

a word of caution avoid relying on activeworkbook -- , in case if expectation set dwb new/blank workbook, best way explicitly create new/blank workbook, rather relying on user manually open new/blank target workbook.

set dwb = workbooks.add

if, on other hand dwb must assigned other known/existing workbook, should either providing file path open statement, or workbook name workbooks collection.

on related note, it's never necessary activate workbook, see here:

how avoid using select in excel vba macros

and further note: variables aren't globally scoped, they're scoped module using dim statement. public declaration uses public keyword, not dim keyword. both module-scoped , global-scoped should used caution (public moreso module-scoped) , in cases it's preferable pass objects/variables reference dependent subs , functions:

how make excel vba variables available multiple macros?


Comments

Post a Comment

Popular posts from this blog

inversion of control - Autofac named registration constructor injection -

does autofac support specifying registration name in components' constructors? example: ninject's namedattribute . you need use autofac.extras.attributed package on top achieve this so let's have 1 interface , 2 classes: public interface ihello { string sayhello(); } public class englishhello : ihello { public string sayhello() { return "hello"; } } public class frenchhello : ihello { public string sayhello() { return "bonjour"; } } then have consumer class, in want select instance injected: public class helloconsumer { private readonly ihello helloservice; public helloconsumer([withkey("en")] ihello helloservice) { if (helloservice == null) { throw new argumentnullexception("helloservice"); } this.helloservice = helloservice; } public string sayhello() { return this.helloservice.sayhello()...
Read more

verilog - Systemverilog dynamic casting issues -

i've code snippet following in testbench function void write_to_port( my_data_type_base data ); my_data_type_extended data_ext; if(!$cast(data_ext, data)); `uvm_error(get_type_name(), "failed cast"); `uvm_info(get_name(), $psprintf("data_ext :\n%s", data_ext.sprint()), uvm_medium) // write data_ext out port.... endfunction when run it, i'm getting uvm_error "failed cast." i'm not quire sure why $cast not returning 1. can see, i'm printing out extended class data item after casting uvm_info. can see it's being cast properly. if don't use $cast if condition, don't runtime error. isn't coding practice use if dynamic cast check if $cast returning 1 ? what might reason behind cast not returning 1 in above case? i think semicolon on line 'if' not belong? i think consumes if statement, , uvm_error executes regardless of how if evaluates: if(!$cast(data_ext, data)); <- no semico...
Read more

ios - Change Storyboard View using Seague -

i have following code checks connection server, , depending on want change views if no connection found. nsstring *connect = [nsstring stringwithcontentsofurl:[nsurl urlwithstring:@"http://myserver.com"] encoding:nsutf8stringencoding error:nil]; if (connect == null) { [self performseguewithidentifier:@"network_failure" sender:self]; } else { // other code here } my problem not change views. have added storyboard segue between 2 views identifier of network_failure . not have navigation controller or that, problem? thanks help! no not problem. first of all, in objectivec exists nil object oriented null . must use that. so: if(connect == nil) or better: if(!connect) so due fact nil not same of null , probably code does't enter in if scope . you have been able discover breakpoint. anyway: be sure set trigger segue (line) 2 view controller , , name network_failure (that not best name. should instead view1toview2 ) as, m...
Read more