Скачать или смотреть Resolving Excel Error 1004: How to Run Macros from Your Add-In

Discover how to fix Excel's Error `1004` and learn effective techniques to run macros from your Add-Ins effortlessly, even with restricted macro settings.
---
This video is based on the question https://stackoverflow.com/q/73580083/ asked by the user 'rmrico' ( https://stackoverflow.com/u/17148694/ ) and on the answer https://stackoverflow.com/a/73581326/ provided by the user 'FaneDuru' ( https://stackoverflow.com/u/2233308/ ) 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: Error 1004 Can not run the macro. The macro may not be available in this workbook or all macros may be disabled

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.
---
Resolving Excel Error 1004: How to Run Macros from Your Add-In

If you're an Excel user, encountering errors while trying to run macros can be frustrating. One such common issue is Error 1004: "Cannot run the macro. The macro may not be available in this workbook or all macros may be disabled." This error often crops up when attempting to use an add-in installed in your Excel while using VBA (Visual Basic for Applications).

Understanding the Problem

In this scenario, you may have an add-in stored in a separate folder that you want to utilize with a VBA macro. However, trying to run it using a command like Application.Run "'Addintorun.xla'" results in the error message mentioned above.

Common Reasons for the Error:

Macros are disabled: Your Excel settings might prevent macros from running entirely, which is especially common in corporate or secured environments.

Add-in reference issues: The macro you're trying to access may not be available, or it is required to be referenced properly within your VBA project.

Solutions to Error 1004

Fortunately, there are a couple of ways to resolve this error and enable the use of your add-in effectively:

1. Adding the Add-In as a Reference

One way to resolve this issue is by adding the add-in as a reference to your workbook that will be calling its procedures. Here's how you can do that:

Navigate to the VBA Editor (press Alt + F11).

Click on Tools in the menu bar and select References.

Look for your add-in (e.g., 'Addintorun.xla') in the list and check it.

This allows you to call the subroutines or functions directly as if they were part of your current workbook.

Example of Calling a Macro

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

Note: If there are macros with the same name in both the add-in and your workbook, be sure to specify the add-in’s name.

2. Running Non-Private Subs Directly

If you don’t want to modify references or it’s not feasible, another approach is to manually invoke non-private subs from your add-in. This can be done as follows:

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

Important Syntax Note

Quotes: The single quotes are necessary only if the add-in’s name includes spaces. Even when there are no spaces, it’s a good practice to include quotes for consistency.

Calling Functions from Add-Ins

If you're interested in getting a result from a non-private function within your add-in, you can do it like this:

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

Parameter Requirements

Ensure that the parameters provided to the function match the expected data types (in the example above, two Long parameters are passed).

Conclusion

Error 1004 can be a nuisance, but with a clear understanding of how to properly reference and call macros and functions from your add-ins, you can overcome these challenges. Whether you add the add-in as a reference or call the functions directly through Application.Run, both methods are effective ways to bring the full power of your Excel Add-Ins to your VBA projects.

With these solutions at your disposal, you should feel confident to tackle any macro-running issues in Excel. Happy coding!