Скачать или смотреть Efficiently Set Workbook Depending on the Open Workbook in Excel VBA

Learn how to dynamically set a worksheet reference in Excel VBA based on which workbook is currently open — streamline your macro execution now!
---
This video is based on the question https://stackoverflow.com/q/62382500/ asked by the user 'NorwegianLatte' ( https://stackoverflow.com/u/9627219/ ) and on the answer https://stackoverflow.com/a/62385933/ provided by the user 'Darren Bartrup-Cook' ( https://stackoverflow.com/u/4677305/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: set workbook depending on the workbook that is open

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Efficiently Set Workbook Depending on the Open Workbook in Excel VBA

When working with multiple Excel workbooks in a single macro, it’s common to encounter the need to reference a specific worksheet based on which workbook is currently open. For instance, if you have Air.xlsx and Ocean.xlsx, you might want to execute operations on either one depending on which is active. This guide will guide you through a simple solution to check which workbook is open and how to set the corresponding worksheet reference, ensuring your macros run smoothly without errors.

The Problem: Setting a Worksheet Based on the Open Workbook

If you attempt to set a worksheet using the following code:

[[See Video to Reveal this Text or Code Snippet]]

This approach presents a problem: an error will occur if only one of these workbooks is open. Instead, you need an efficient mechanism to determine which workbook is active and then set the worksheet accordingly.

Proposed Solution: Using a Custom Function

Step 1: The Macro Logic

You can achieve the desired functionality by implementing a macro that checks the status of your workbooks. The steps include:

Check if Air.xlsx is open.

If not, check if Ocean.xlsx is open.

Based on which one is open, set the relevant worksheet.

Step 2: Sample Code

Here’s a complete example of how the code might look:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Code

Main Macro (Test): This procedure checks if one of the two workbooks is currently open and accordingly sets the variable wrksht to the specific worksheet you want to work with.

Function to Check Workbook Status (WorkbookIsOpen): This function attempts to set a reference to the specified workbook. If it fails, it catches the error using On Error Resume Next, allowing it to return a Boolean indicating whether or not the workbook is open.

Error Handling

Notice the handling of the case when neither workbook is open. It’s vital to inform the user that their desired workbooks are not accessible, ensuring a smooth execution of your macro.

Conclusion

By following this structured approach, you can streamline your Excel VBA macros when working with multiple workbooks. This method is not only effective but also allows for greater flexibility and error handling within your VBA projects. Implement this solution in your macros and enhance your workflow efficiency today!