variables puzzle (public, static, named ranges. How???)

[wassita]

Hi all,
I am working in a macro that has two parts. The first one (call it macro1) summarizes and organize the data and the second (call it macro2) update it according to the users wish. The first part is ready, but to successfully write the second part I need some object variables defined in macro1. Those variables are public but they are cleared when macro1 finishes. I have tried to use the instruction static and to name a range in excel. It didn’t work.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
The VBA doesn’t read the named range and doesn’t accept the instruction static to a public variable.<o></o>
I really don’t know how to solve it. I would appreciate any help I could get around this.

 

[GlennUK]

Describe the order of events ... do you run macro1 to completion and then try to run macro2? If so the Public variables will be available .... but NOT if you close the workbook between runs OR if you have any crash in VBA between runs. As for named ranges not being picked up ... that's not possible whatever occurs between runs ( apart from closing the workbook where the named range exists without saving ).

 

[wassita]

Hi GlennUK.
Macro1 is run to completition, then macro2 is ran. The workbook is not closed but the object variables for macro2 are cleared. If i run macro2 before macro 1 is ended then the variables are there but it will not solve the problem I have...

 

[GlennUK]

Show your macros.

 

[wassita]

GlennUK, thanks for your help. You are right. when I was preparing the macros to post I understood. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Sometimes when you are tired you don’t see clearly. I had a problem evaluating a formula and I thought it was the variables…<o></o>
Thanks.<o></o>