Скачать или смотреть Solving the Compile Error in Excel VBA with Solver

Learn how to fix the `Compile error: Sub or function not defined` message in Excel VBA when using Solver by creating the correct reference.
---
This video is based on the question https://stackoverflow.com/q/71565574/ asked by the user 'riski1991' ( https://stackoverflow.com/u/18535487/ ) and on the answer https://stackoverflow.com/a/71565705/ provided by the user 'Solver Max' ( https://stackoverflow.com/u/14996368/ ) 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: Compile issue using Solver

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 the Compile Issue with Solver in Excel VBA

If you've recently ventured into programming in Excel using Visual Basic for Applications (VBA), you may have encountered the message: "Compile error: Sub or function not defined" while trying to use the Solver function. This error can be frustrating, especially if you're not familiar with how to correctly set up references in your VBA environment. In this guide, we will break down the issue and provide a clear solution to get you back on track.

Understanding the Problem

When you attempt to run a macro that utilizes the Solver functions (like SolverOk or SolverSolve), VBA needs to recognize the Solver object. If it doesn't, you'll see a compile error that indicates the subroutine or function is not defined. In layman's terms, VBA can't find or doesn't have access to the functions you're trying to use.

Why Does This Error Occur?

The Solver Add-In is not activated in Excel.

The necessary references to the Solver object in VBA are missing.

Step-by-Step Solution

To resolve this compile error and enable your macro to use Solver, follow these steps:

Step 1: Activate the Solver Add-In

Open Excel.

Go to the File tab and select Options.

Click on Add-Ins from the left menu.

At the bottom of the window, in the Manage dropdown, select Excel Add-ins and click Go.

In the Add-Ins box that appears, check the box next to Solver Add-in.

Click OK to activate it.

Step 2: Create a Reference in VBA

Open the VBA Editor by pressing ALT + F11 in Excel.

In the VBA Editor, go to the Tools menu and select References.

In the References dialog box, scroll down and find Solver.

Check the box next to Solver, and click OK.

This will link your VBA environment to the Solver functionality.

Step 3: Test Your Macro

Now that you have activated the Solver and set the reference, you can run your macro again. Here’s a corrected version of your original macro for clarity:

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

Conclusion

Having the right references in your VBA project is crucial for utilizing various functionalities offered in Excel. By activating the Solver Add-In and ensuring that the reference is correctly set in your VBA environment, you can easily eliminate the compile error and make effective use of optimization solutions in Excel. If you continue to face issues or have further questions, feel free to ask for assistance. Happy coding!