Sharpen your Edge with


SeGuruCool
The Largest Independant Solid Edge Resource Outside UGS


Solve Solid Edge problems using Excel

Tushar Suradkar
www.oocities.org/SeGuruCool

  segurucool @ indiatimes.com


SeGuruCool's Newsletter

Stay updated when new tutorials and articles are posted.

(Note: Do not change first text box)
List Name:
Your Email:


SE Customization eBook
 
  • 79 Seventy-nine chapters
  •  
  • Cust. using VB and VB.NET
  •  
  • Insight Customization
  •  
  • Excel Cust w.r.t. Solid Edge
  •  
  • Coding for Custom Sensors
  •  
  • Coding for Multiple SE versions
  •  
  • XML & BOM-Database connectivity


  •   Download FREE 6 chapters & source code



    In this tutorial you learn :

  • How to drive a Solid Edge part from an Excel Spreadsheet
  • About the Excel Solver Add-In
  • Use Solver for design optimization

  • The Scenario

    I am contacted by a manufacturer of aluminium cans.

    They supply around 30,000 cans per week to the local soft-drink bottling plants.

    The cans are made of premium quality aluminium sheets, so raw material is a concern.

    Also, the minimum volume to hold is 300 ml.




     

    The Excel Solver Add-In

    First of all, I want to introduce you to the Excel Solver.

    It is an add-in that comes with Excel.

    It is not installed by default, so chances are you may not have it if you did a Typical install of Excel.

    There is an easy way to find out.

    Start Excel and look in the Tools menu.

    If Solver is listed, you are ready to go, else, you will need to run the Microsoft Office Setup program to add it.






    Problem definition

    As shown in figure, enter a few labels and values to describe the parameters of a can (cylinder).

    Right now enter any values for eg. 5 for the radius of can and 12 for height.

    The radius and height are plain values, while, the volume and surface area are formulae.

    The volume of cylinder is given by :

    v = pi . r2 . h

    and surface area :

    a = ( The curved surface ) + 2 ( cap surface )

    therefore, a = (2 . pi . r . h) + 2 . (pi . r2 )

    Accordingly,

    The formula in cell B4 ie. for volume will be 3.14159*B1*B1*B2

    And that in cell B5 ie. for surface area will be =2*3.14159*B1*B2+(2*3.14159*B1*B1)

    This will result in 942.48 for the volume and 534.07 as surface area.

    We want to minimize surface area for a volume of 300.




    Design Optimization

    Since, raw material (aluminium sheets) is to be kept minimum, surface area for the can should be minimum.
    This is the target.

    Also, volume of cylinder should not be less than 300 ml.
    This is the constraint.

    We need to vary the radius and height of the cylinder such that surface area is minimum and at the same time, volume should always stay above 300 ml.

    The Excel Solver also speaks in terms of targets, constraints and variables.

    Let us see, How...





    Specifying the Target

    Assuming that you have entered both the formulae correctly, proceed as follows...

    1. Select Tools > Solver and when the Solver Parameters dialog appears, click the small cell-picker shown with label 1 in figure.

    2. The dialog turns into a thin strip. Select cell B5 for the surface area as shown in figure and press Enter at the keyboard.

    3. The address of the cell appears in the Set Target Cell area.

    4. Next, click the Min radio button.

    In the four steps above, we have told Solver that the target is the surface area which is to be kept minimum.





    Specifying the Variables

    1. Click the cell-picker of By Changing Cells as shown in figure.

    2. The dialog turns into a thin strip again and press and drag to select the range B1 to B2 and press Enter at the keyboard.

    3. The range labels appear in the textbox to the left of the cell-picker.

    In the three steps above, we have specified the variables.





    Specifying the Constraints

    1. Click the Add button in the Subject to Constrain area.

    2. The Add Constraints dialog box appears.

    3. Again click the cell-picker for Cell Reference and select cell B4 for the volume. Press Enter.

    4. From the drop-down list, select >=

    5. Finally, in the Constraint textbox, type 300

    6. Click OK

    In the six steps above, we have specified the constraints.

    Back to the Solver dialog, click Solve and then click OK for the keep solution option.

    The radius and height are calculated for volume >= 300 and surface area minimum.

    The next few steps illustrate how to link the radius and height cells to a Solid Edge part.

    So keep scrolling ...





    In to Solid Edge

    Fire up Solid Edge and in the part environment, select the x-y plane for extrusion.

    In the sketcher window, draw a circle and dimension its radius.

    Select Tools > Variable > Variable...

    The variable table appears.

    Using the Select Tool, pick the dimension. It will be highlighted in the variable table as shown in figure.

    Select the name and type r for the variable name as shown in figure.






    Putting it all Togther

    Using Alt + Tab, switch to Excel.

    Copy the radius value by right-clicking in the cell and selecting Copy from the menu.

    Swich back to Solid Edge and in the variable table, right click on the button on the extreme left of the radius row.

    Select Paste Link from the menu appear that appears.

    The cell address from Excel is now linked to the radius value in Solid Edge and its full path and filename appears in the formula area of the variable table.

    Click Finish and proceed to return to the part environment.

    Click in the graphics area to specify an arbritrary height for th eprotrusion.

    Just before clicking Finish for the protrusion process, link the height dimension to the height cell in Excel.

    Save both the Excel workbook and the Solid Edge part document and you are done.

    The cells and the part will be linked together and remain to be so happily ever after ...





    Tushar Suradkar     segurucool @ indiatimes.com

    Also Visit :

    CadGuruCool   |   SeGuruCool   |   ProeGuruCool