VBA and multiple workbooks
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
VBA and multiple workbooks
I have one workbook that serves as the 'master' and when I fill it in, I save it under a different name. This workbook has various coded procedures associated with it (most of which came from the fine people here).
Now, the problem I have is that when I have several of the workbooks open at the same time, a procedure that normally takes half a second now takes one minute 20 seconds to complete. I also found that if I have several of these open, I cannot effectively modify the code. That is, when I modify the range of a variable in the code (not sure I am using correct language here), it doesn't operate as though it is modified. For example, the code
Set myRange = wsh.Range("A9:A110, G9:G110, N9:N110,I1,J4,D123,J123,Q123,D118:D119,J118:J119,Q118:Q119,D2") - I would modify it to
Set myRange = wsh.Range("A9:A110, G9:G110, N9:N110,I1,J4,D124,J124,Q124,D118:D119,J118:J119,Q118:Q119,D2")
yet, when executed, it acts as if it were not modified.
Anyway, this is background info that has convinced me that having the same workbooks open, even under different names, is problematic... especially with regard to taking over one minute to clear some ranges.
Am I correct in my conclusion? If so, is there a work around since I really need to have these multiple workbooks open. I think maybe the VBA is getting confused with all the workbooks having the same code and variables. It makes me wonder if there is some way to fully qualify the variable names so they are associated only with the workbook that is active.
Now, the problem I have is that when I have several of the workbooks open at the same time, a procedure that normally takes half a second now takes one minute 20 seconds to complete. I also found that if I have several of these open, I cannot effectively modify the code. That is, when I modify the range of a variable in the code (not sure I am using correct language here), it doesn't operate as though it is modified. For example, the code
Set myRange = wsh.Range("A9:A110, G9:G110, N9:N110,I1,J4,D123,J123,Q123,D118:D119,J118:J119,Q118:Q119,D2") - I would modify it to
Set myRange = wsh.Range("A9:A110, G9:G110, N9:N110,I1,J4,D124,J124,Q124,D118:D119,J118:J119,Q118:Q119,D2")
yet, when executed, it acts as if it were not modified.
Anyway, this is background info that has convinced me that having the same workbooks open, even under different names, is problematic... especially with regard to taking over one minute to clear some ranges.
Am I correct in my conclusion? If so, is there a work around since I really need to have these multiple workbooks open. I think maybe the VBA is getting confused with all the workbooks having the same code and variables. It makes me wonder if there is some way to fully qualify the variable names so they are associated only with the workbook that is active.
Don
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA and multiple workbooks
Your code refers to a variable wsh. How is it set? There must be a line
Set wsh = ...
Set wsh = ...
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: VBA and multiple workbooks
Set wsh = Worksheets("EVENT-Work")
This is a worksheet (Tab name) within the workbook and all the workbooks are the same.... same names, I mean.
This is a worksheet (Tab name) within the workbook and all the workbooks are the same.... same names, I mean.
Don
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA and multiple workbooks
Excel doesn't allow you to open two workbooks with the same name in the same instance of Excel. The only way to open two workbooks with the same name (stored in different folders) is to start two separate instances of Excel, and open one workbook in each instance. But if you do that, the code in one instance doesn't "see" the workbook in the other instance.
Are you sure you are opening multiple workbooks with exactly the same name?
Are you sure you are opening multiple workbooks with exactly the same name?
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: VBA and multiple workbooks
Sorry, I meant the tabs/worksheets with the workbooks had the same names. The workbooks each have a unique name.
Don
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA and multiple workbooks
OK, in that case, try specifying the workbook as well as the worksheet:
Set wsh = Workbooks("ThisWorkbook.xlsx").Worksheets("EVENT-Work")
If you don't specify the workbook, VBA assumes that the worksheet is in the active workbook, which may not be what you intended.
Set wsh = Workbooks("ThisWorkbook.xlsx").Worksheets("EVENT-Work")
If you don't specify the workbook, VBA assumes that the worksheet is in the active workbook, which may not be what you intended.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: VBA and multiple workbooks
Thank you
1) Is there a VBA variable for filename so I can modify the master and subsequent savings (under new filenames) will automatically take that filename and use it in the code?
2) How is it possible for the active workbook to be anything other than the one I am working in? The only way I can imagine is if I have several workbooks open and the VBA editor was invoked from the workbook I was modifying then, before closing the editor, I used another of the open workbooks. I am not at all sure of this or even if it makes sense.
1) Is there a VBA variable for filename so I can modify the master and subsequent savings (under new filenames) will automatically take that filename and use it in the code?
2) How is it possible for the active workbook to be anything other than the one I am working in? The only way I can imagine is if I have several workbooks open and the VBA editor was invoked from the workbook I was modifying then, before closing the editor, I used another of the open workbooks. I am not at all sure of this or even if it makes sense.
Don
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA and multiple workbooks
1) You can get the name of the active workbook:
Dim strFile As String
strFile = ActiveWorkbook.Name
2) If you open an existing workbook or create a new workbook using code, that workbook becomes the active workbook. If you then use
Set wsh = Worksheets("EVENT-Work")
wsh will be a sheet in the active workbook, not in the workbook from which you run the code.
Dim strFile As String
strFile = ActiveWorkbook.Name
2) If you open an existing workbook or create a new workbook using code, that workbook becomes the active workbook. If you then use
Set wsh = Worksheets("EVENT-Work")
wsh will be a sheet in the active workbook, not in the workbook from which you run the code.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: VBA and multiple workbooks
Thanks for the code to retrieve the active workbook name.
With regards to # 2...
With regards to # 2...
Does this mean if I open workbooks A.XLSM, B.XLSM, and C.XLSM in that order that C.XLSM is the active workbook even if I go back to A.XLSM and run code (attached to a button)? Sorry, I don't understand. this concept. Basically, I may have all the three mentioned workbooks open at the same time and be working in A.XLSM when i decide I want to modify the code and try something (understand, please, that all three workbooks contains identical code). So I do ALT-F11, go into the VBA Editor and make the changes I want. Then I click the button in A.XLSM and it doesn't work correctly. When I look at the code, it is not changed although I just changed it. Can you explain that is the context of active worksheets? Please try again.2) If you open an existing workbook or create a new workbook using code, that workbook becomes the active workbook. If you then use
Set wsh = Worksheets("EVENT-Work")
wsh will be a sheet in the active workbook, not in the workbook from which you run the code.
Don
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA and multiple workbooks
If you open workbooks a.xlsm, open a macro in a.xlsm in the Visual Basic Editor, switch back to Excel and open b.xlsm and c.xlsm in that order, c.xlsm will be the active workbook.
If you then activate the Visual Basic Editor, you'll probably see the macro from a.xlsm that you opened earlier, and you can run this macro. But c.xlsm will still be the active workbook.
The most likely explanation for the behavior that you describe is that you have several identical or nearly identical copies of the same macro. The button may execute a different version than you expect.
If you then activate the Visual Basic Editor, you'll probably see the macro from a.xlsm that you opened earlier, and you can run this macro. But c.xlsm will still be the active workbook.
The most likely explanation for the behavior that you describe is that you have several identical or nearly identical copies of the same macro. The button may execute a different version than you expect.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 397
- Joined: 24 Jan 2010, 19:43
- Location: Salt Lake City, Utah, USA
Re: VBA and multiple workbooks
With multiple workbooks, you need to fully qualify the workbook and sheet such as either (air code):dasadler wrote:Set wsh = Worksheets("EVENT-Work")
Does this mean if I open workbooks A.XLSM, B.XLSM, and C.XLSM in that order that C.XLSM is the active workbook even if I go back to A.XLSM and run code (attached to a button)? Sorry, I don't understand. this concept. Basically, I may have all the three mentioned workbooks open at the same time and be working in A.XLSM when i decide I want to modify the code and try something (understand, please, that all three workbooks contains identical code). So I do ALT-F11, go into the VBA Editor and make the changes I want. Then I click the button in A.XLSM and it doesn't work correctly. When I look at the code, it is not changed although I just changed it. Can you explain that is the context of active worksheets
With Workbooks("C.XLSM").Worksheets("EVENT-Work").Range("SomeRange")
.<do something>
or set the range as an object INCLUDING the Workbook name, like this:
Set myRange = Workbooks("C.XLSM").Worksheets("EVENT-Work").Range("SomeRange")
myRange.<dosomething>
You should make full qualification a habit, even when your macro is specific only to one workbook.
Last edited by Goshute on 09 Feb 2010, 23:00, edited 1 time in total.
Goshute
I float in liquid gardens
I float in liquid gardens
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: VBA and multiple workbooks
I apologize for having such a tough time with this concept but this discussion (while probably tiring for everyone else) is really helpful for me and hopefully others.
Just to simplify...
I just did an experiment. I opened A.XLSM which contained code
Set myRange = wsh.Range("D124,J124,Q124)
Then, from within A.XLSM, I opened the Visual Basic Editor to confirm the code. Then closed the editor.
With A.XLSM still open, I opened B.XLSM which contained code
Set myRange = wsh.Range("D123,J123,Q123")
From withinB>XLSM, I opened the Visual Basic Editor and the code was
Set myRange = wsh.Range("D124,J124,Q124) - same as A.XLSM
So it seems that whichever is opened first establishes the code when you are working with identical workbooks (except for the data and workbook names).
Unless, of course, the word 'Open" is understood differently. When I say open, I mean to 'load the workbook' into Excel from the disk. I do not mean to simply select that workbook from those already open.
Just to simplify...
I just did an experiment. I opened A.XLSM which contained code
Set myRange = wsh.Range("D124,J124,Q124)
Then, from within A.XLSM, I opened the Visual Basic Editor to confirm the code. Then closed the editor.
With A.XLSM still open, I opened B.XLSM which contained code
Set myRange = wsh.Range("D123,J123,Q123")
From withinB>XLSM, I opened the Visual Basic Editor and the code was
Set myRange = wsh.Range("D124,J124,Q124) - same as A.XLSM
So it seems that whichever is opened first establishes the code when you are working with identical workbooks (except for the data and workbook names).
Unless, of course, the word 'Open" is understood differently. When I say open, I mean to 'load the workbook' into Excel from the disk. I do not mean to simply select that workbook from those already open.
Don
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA and multiple workbooks
Opening or activating the Visual Basic Editor from a workbook does not necessarily "open" the code in that workbook. The Visual Basic Editor "remembers" which code modules were open when the workbook was last saved.
Let's say that a code module in workbook A was open when A was last saved, but all code modules in workbook B were closed when B was last saved.
If you now open A and look at the Visual Basic Editor, you'll see the code module in A.
If you then open B and look at the Visual Basic Editor, you'll still see the code module in A. B also contains a code module, but it remains closed until you explicitly open it.
Let's say that a code module in workbook A was open when A was last saved, but all code modules in workbook B were closed when B was last saved.
If you now open A and look at the Visual Basic Editor, you'll see the code module in A.
If you then open B and look at the Visual Basic Editor, you'll still see the code module in A. B also contains a code module, but it remains closed until you explicitly open it.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 397
- Joined: 24 Jan 2010, 19:43
- Location: Salt Lake City, Utah, USA
Re: VBA and multiple workbooks
(Repeating Hans explanation, but with some additional information which may help.)dasadler wrote:So it seems that whichever is opened first establishes the code when you are working with identical workbooks (except for the data and workbook names).
No, all code from all open workbooks is "open" in the VBE but only one module is visible: whichever code is being displayed in the VBE code window is the code which will be run, IF that's where you are running code from. Look at the VBE project explorer on the left side - you can choose whichever module in whichever workbook you want, and if you change it to display a new module in the code window then run the code shown in the code window, that's what will be run. For a better understanding of the issue, go back to the worksheet view and press Alt-F8 and see how all the available code is listed - you'll see all the variations of your similar code in all the different workbooks.
Then you'll see why I recommended that you fully qualify your code with the Workbook name.
(There are times when you don't want to fully qualify, such as when you want a generic macro to run on whichever workbook is displayed, but with the type of code you are developing, you do need to fully qualify.)
Edited several times to be more precise about the difference between the code window and the modules available from all open workbooks.
Goshute
I float in liquid gardens
I float in liquid gardens
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: VBA and multiple workbooks
Okay, I think I get it. Maybe before when I had multiple workbooks open and was working on code, I may have been working on code from a module of a workbook other than than what I thought. As I look at the VBE, I see now that there are entries for each workbook open and I can see different code depending on what I select to view.
What do you mean by Alt-F8 and worksheet view? When I go to the worksheet and do Alt-F8, I get a list of macros. In the VBE, when I double click on a worksheet, I get an empty window.. all code seems to reside in module 1.
What do you mean by Alt-F8 and worksheet view? When I go to the worksheet and do Alt-F8, I get a list of macros. In the VBE, when I double click on a worksheet, I get an empty window.. all code seems to reside in module 1.
Don
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA and multiple workbooks
In the screenshot below you see the Macros dialog invoked by pressing Alt+F8 from within Excel.
Apart from several macros in my personal macro workbook Personal.xlsb, you see that there are four macros named Test, in four different workbooks. The one that isn't preceded by the workbook name and an exclamation mark ! is the one in the currently active workbook.
Double-clicking a worksheet in the Project Explorer in the Visual Basic Editor opens the worksheet module. This module is not intended for ordinary macros, but for so-called event procedures that are executed automatically when a specific action occurs. For example, you can create an event procedure named Worksheet_SelectionChange; this will be run each time the user moves to a different cell. For the moment, we don't have to go into the technical details, just remember that you should not store ordinary macros in these worksheet modules.
Apart from several macros in my personal macro workbook Personal.xlsb, you see that there are four macros named Test, in four different workbooks. The one that isn't preceded by the workbook name and an exclamation mark ! is the one in the currently active workbook.
Double-clicking a worksheet in the Project Explorer in the Visual Basic Editor opens the worksheet module. This module is not intended for ordinary macros, but for so-called event procedures that are executed automatically when a specific action occurs. For example, you can create an event procedure named Worksheet_SelectionChange; this will be run each time the user moves to a different cell. For the moment, we don't have to go into the technical details, just remember that you should not store ordinary macros in these worksheet modules.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: VBA and multiple workbooks
Okay, I guess the reason I was lost here is that I had only one workbook open when I did Alt-F8. Thanks for the picture; clears it up and I was able to duplicate it with several workbooks open.
Without going into the technical details, where should I put macros if not in the module? I think all my Excel code are various macros for different worksheets within the workbook. Should I move the code to their respective worksheets? Will that stop them from functioning initially? Must I do something special to ensure continued operation?
Additionally, apparently I have nothing in my personal workbook... what am I missing? What do people generally put in their personal workbooks, macro-wise?
Without going into the technical details, where should I put macros if not in the module? I think all my Excel code are various macros for different worksheets within the workbook. Should I move the code to their respective worksheets? Will that stop them from functioning initially? Must I do something special to ensure continued operation?
Additionally, apparently I have nothing in my personal workbook... what am I missing? What do people generally put in their personal workbooks, macro-wise?
Don
-
- 3StarLounger
- Posts: 397
- Joined: 24 Jan 2010, 19:43
- Location: Salt Lake City, Utah, USA
Re: VBA and multiple workbooks
Personal workbooks generally contain macros and user-defined functions that can be used in any workbook.dasadler wrote: ...where should I put macros if not in the module?
Additionally, apparently I have nothing in my personal workbook... what am I missing? What do people generally put in their personal workbooks, macro-wise?
Code specific to a set of workbooks should be kept in one master workbook within the set.
Goshute
I float in liquid gardens
I float in liquid gardens
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA and multiple workbooks
Macros belong in standard modules - the kind you create by selecting Insert | Macro (and also if you record a macro).
If you want a macro to be available in all workbooks, put it in a module in your personal macro workbook Personal.xls (or Personal.xlsb for Excel 2007 and later).
If a macro is intended for a specific workbook, put it in a module in that workbook.
Do *not* put macros in worksheet modules - those modules are for a very specific purpose; we won't need to go into that now (it could be the subject for a later thread). Neither do macros belong in the ThisWorkbook module.
Some examples of macros I have in my personal macro workbook, so that they are always available to me in Excel:
- A macro that replaces formulas within the selected range with their values.
- A macro that toggles the display of gridlines in the worksheet on and off.
- A macro that splits a name such as "mr. John D. Phillips, PhD" into parts (each part in a separate cell)
- A macro that removes unused custom number formats from a workbook.
If you want a macro to be available in all workbooks, put it in a module in your personal macro workbook Personal.xls (or Personal.xlsb for Excel 2007 and later).
If a macro is intended for a specific workbook, put it in a module in that workbook.
Do *not* put macros in worksheet modules - those modules are for a very specific purpose; we won't need to go into that now (it could be the subject for a later thread). Neither do macros belong in the ThisWorkbook module.
Some examples of macros I have in my personal macro workbook, so that they are always available to me in Excel:
- A macro that replaces formulas within the selected range with their values.
- A macro that toggles the display of gridlines in the worksheet on and off.
- A macro that splits a name such as "mr. John D. Phillips, PhD" into parts (each part in a separate cell)
- A macro that removes unused custom number formats from a workbook.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA