Call Solver With Macro
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Call Solver With Macro as PDF for free.
More details
- Words: 511
- Pages: 9
On Calling Solver From a Macro Solver is a wonderful tool but one that can't be called from an Excel macro without some setup work in advance. The worksheets in this workbook are: Solver Demo - Exposition See Solver work under macro control. Solver's Solution An archive workbook with Solver's solution to the problem recorded. Call Solver from VBA A description of how to set up Solver as an "available reference" to VBA.
9151945.xls
Excel Solver Demo: Forecast for 4 quarters
Target Cell
Changing Cells
Constraints
Product Price: Product Cost:
Solve for Product P
$40.00 $25.00
Seasonality
Q1 0.9
Q2 1.1
Q3 0.8
Q4 1.2
Total
Units Sold Sales Revenue Cost of Sales Gross Margin
3,592 $143,662 89,789 53,873
4,390 $175,587 109,742 65,845
3,192 $127,700 79,812 47,887
4,789 $191,549 119,718 71,831
15,962 $638,498 399,061 239,437
Salesforce Advertising Overhead Total Costs
8,000 10,000 21,549 39,549
8,000 10,000 26,338 44,338
9,000 10,000 19,155 38,155
9,000 10,000 28,732 47,732
34,000 40,000 95,775 169,775
Product Profit Profit Margin
$14,324 10%
$21,507 12%
$9,732 8%
$24,099 13%
$69,662 11%
To return to the original worksheet: The changing cells values were 10,000 each quarter.
Page 2
Return m origina
9151945.xls
Solve for maximum Product Profit in Q1
Return model to original values.
Page 3
9151945.xls
Excel Solver Demo: with Solver's Solution
Target Cell
Changing Cells
Product Price: Product Cost:
Constraints
$40.00 $25.00
Seasonality
Q1 0.9
Q2 1.1
Q3 0.8
Q4 1.2
Total
Units Sold Sales Revenue Cost of Sales Gross Margin
4,465 $178,605 111,628 66,977
3,970 $158,819 99,262 59,557
2,888 $115,505 72,190 43,314
4,331 $173,257 108,286 64,971
15,655 $626,186 391,366 234,820
Salesforce Advertising Overhead Total Costs
8,000 $17,093 26,791 51,884
8,000 $7,636 23,823 39,459
9,000 $7,636 17,326 33,961
9,000 $7,636 25,989 42,624
34,000 40,000 93,928 167,928
Product Profit Profit Margin
$15,093 8%
$20,099 13%
$9,353 8%
$22,347 13%
$66,892 11%
The completed "Solver Parameters" dialog for this problem.
The constraint de
Page 4
9151945.xls
The constraint definition dialog for this problem.
Page 5
Setup so you can Call Solver from a Macro How to: Create your model with Solver as usual. Save it. Open the VBA Editor (ALT+F11) and choose Tools, References to open the "References - VBAProject" dialog shown below.
Find "Solver" in the list of "Available References" and check it 'on'. Click OK. Save your workbook. Look in the VBA Project window for your project. There should now be a folder with the name "References". If you open this folder, you should see an item that reads "Reference to SOLVER.XLA". This reference is stored with your workbook and macros so you don't need to re-establish it every time you want to run the macro that calls Solver that's in your workbook.
The 12 Solver-related functi use in VBA code:
Now in this workbook you can write a macro that invokes Solver and the invocation can occur under macro control. Without this setup step, you can record a macro that invokes Solver but the macro won't run. You'll get an error message like the one below.
The 12 Solver-related functions available for use in VBA code:
9151945.xls
Excel Solver Demo: Forecast for 4 quarters
Target Cell
Changing Cells
Constraints
Product Price: Product Cost:
Solve for Product P
$40.00 $25.00
Seasonality
Q1 0.9
Q2 1.1
Q3 0.8
Q4 1.2
Total
Units Sold Sales Revenue Cost of Sales Gross Margin
3,592 $143,662 89,789 53,873
4,390 $175,587 109,742 65,845
3,192 $127,700 79,812 47,887
4,789 $191,549 119,718 71,831
15,962 $638,498 399,061 239,437
Salesforce Advertising Overhead Total Costs
8,000 10,000 21,549 39,549
8,000 10,000 26,338 44,338
9,000 10,000 19,155 38,155
9,000 10,000 28,732 47,732
34,000 40,000 95,775 169,775
Product Profit Profit Margin
$14,324 10%
$21,507 12%
$9,732 8%
$24,099 13%
$69,662 11%
To return to the original worksheet: The changing cells values were 10,000 each quarter.
Page 2
Return m origina
9151945.xls
Solve for maximum Product Profit in Q1
Return model to original values.
Page 3
9151945.xls
Excel Solver Demo: with Solver's Solution
Target Cell
Changing Cells
Product Price: Product Cost:
Constraints
$40.00 $25.00
Seasonality
Q1 0.9
Q2 1.1
Q3 0.8
Q4 1.2
Total
Units Sold Sales Revenue Cost of Sales Gross Margin
4,465 $178,605 111,628 66,977
3,970 $158,819 99,262 59,557
2,888 $115,505 72,190 43,314
4,331 $173,257 108,286 64,971
15,655 $626,186 391,366 234,820
Salesforce Advertising Overhead Total Costs
8,000 $17,093 26,791 51,884
8,000 $7,636 23,823 39,459
9,000 $7,636 17,326 33,961
9,000 $7,636 25,989 42,624
34,000 40,000 93,928 167,928
Product Profit Profit Margin
$15,093 8%
$20,099 13%
$9,353 8%
$22,347 13%
$66,892 11%
The completed "Solver Parameters" dialog for this problem.
The constraint de
Page 4
9151945.xls
The constraint definition dialog for this problem.
Page 5
Setup so you can Call Solver from a Macro How to: Create your model with Solver as usual. Save it. Open the VBA Editor (ALT+F11) and choose Tools, References to open the "References - VBAProject" dialog shown below.
Find "Solver" in the list of "Available References" and check it 'on'. Click OK. Save your workbook. Look in the VBA Project window for your project. There should now be a folder with the name "References". If you open this folder, you should see an item that reads "Reference to SOLVER.XLA". This reference is stored with your workbook and macros so you don't need to re-establish it every time you want to run the macro that calls Solver that's in your workbook.
The 12 Solver-related functi use in VBA code:
Now in this workbook you can write a macro that invokes Solver and the invocation can occur under macro control. Without this setup step, you can record a macro that invokes Solver but the macro won't run. You'll get an error message like the one below.
The 12 Solver-related functions available for use in VBA code:
Related Documents
Call Solver With Macro
November 2019 1
Solver
July 2020 10
Solver
October 2019 15
Solver
April 2020 9
Sudoku Solver
December 2019 13