VBA and multiple workbooks

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

VBA and multiple workbooks

Post by dasadler »

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.
Don

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA and multiple workbooks

Post by HansV »

Your code refers to a variable wsh. How is it set? There must be a line

Set wsh = ...
Best wishes,
Hans

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Re: VBA and multiple workbooks

Post by dasadler »

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.
Don

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA and multiple workbooks

Post by HansV »

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?
Best wishes,
Hans

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Re: VBA and multiple workbooks

Post by dasadler »

Sorry, I meant the tabs/worksheets with the workbooks had the same names. The workbooks each have a unique name.
Don

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA and multiple workbooks

Post by HansV »

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.
Best wishes,
Hans

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Re: VBA and multiple workbooks

Post by dasadler »

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.
Don

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA and multiple workbooks

Post by HansV »

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.
Best wishes,
Hans

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Re: VBA and multiple workbooks

Post by dasadler »

Thanks for the code to retrieve the active workbook name.

With regards to # 2...
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.
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.
Don

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA and multiple workbooks

Post by HansV »

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.
Best wishes,
Hans

User avatar
Goshute
3StarLounger
Posts: 397
Joined: 24 Jan 2010, 19:43
Location: Salt Lake City, Utah, USA

Re: VBA and multiple workbooks

Post by Goshute »

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 multiple workbooks, you need to fully qualify the workbook and sheet such as either (air code):
    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

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Re: VBA and multiple workbooks

Post by dasadler »

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.
Don

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA and multiple workbooks

Post by HansV »

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.
Best wishes,
Hans

User avatar
Goshute
3StarLounger
Posts: 397
Joined: 24 Jan 2010, 19:43
Location: Salt Lake City, Utah, USA

Re: VBA and multiple workbooks

Post by Goshute »

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).
(Repeating Hans explanation, but with some additional information which may help.)

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

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Re: VBA and multiple workbooks

Post by dasadler »

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.
Don

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA and multiple workbooks

Post by HansV »

In the screenshot below you see the Macros dialog invoked by pressing Alt+F8 from within Excel.
x17.png
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

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Re: VBA and multiple workbooks

Post by dasadler »

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?
Don

User avatar
Goshute
3StarLounger
Posts: 397
Joined: 24 Jan 2010, 19:43
Location: Salt Lake City, Utah, USA

Re: VBA and multiple workbooks

Post by Goshute »

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?
Personal workbooks generally contain macros and user-defined functions that can be used in any workbook.

Code specific to a set of workbooks should be kept in one master workbook within the set.
Goshute
I float in liquid gardens

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA and multiple workbooks

Post by HansV »

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.
Best wishes,
Hans

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Re: VBA and multiple workbooks

Post by dasadler »

Interesting, thank you.
Don