You have a square or rectangular sheet of raw material. Maybe it’s steel, aluminum, or a piece of yellow construction paper. You need to cut a specific shape out of the raw material with wasting as little of it as possible. Maybe you get some cash back for selling the scrap material but it’s not as much as the raw material itself. How do you know what orientation of the part is best so as to use as much of the material as possible? Use the blank layout optimizer macro to find out what the best orientation is to maximize your material yield.

In this tutorial, you’ll learn:

- How to use extremum points in CATIA
- How to create parameters and link them to a measure
- How to automatically update CATIA measurements
- How to export CATIA data to Excel
- How to rotate a sketch with a CATScript macro
- How to highlight a row in Excel based on a cell’s value by vba

To see the end result to get an idea of what we are working towards, watch the video below:

**Part One: Geometry Creation and Setup**

Before we begin coding, we need to setup our CATPart template. Imagine unfolding a sheet metal bracket into a flat shape. That unfolded shape needs to be cut out of a rectangular blank sheet of steel before it is folded into its final shape.

- Create a new CATPart
- Create a new point at the origin (0,0,0)
- Create a new axis system at the newly created point ( z axis should match the compass z axis direction)
- Create a new sketch on the XY plane. This is your blank layout, so you could also use software such as FastBlank to generate your unfolded shape. In this example, I have just made up a shape (don’t ask me what the final formed version would look like).

5. Now create a rotate of this Sketch about the Z axis. You can leave the rotation at 0 degrees for now.

6. Next, in GSD under point, use the Extrenum tool to create four points that will automatically adjust when we later rotate this sketch.

The element will be your rotate. For the first point, direction will be X axis and use Max. For the second point, same thing except choose Min. Then repeat but change the x axis to the Y axis and do Max and Min for a total of four points on the furthest X and Y distances of the rotated sketch.

7. Create another sketch on the XY plane. This will be a sketch of our rectangular blank or starting material. Maybe it is a sheet of steel. Draw a rectangle. Constrain each side of the rectangle to each of the four extrenum points. You can make this distance whatever you want. In my case, I have linked the two y axis direction constraints to a “Vertical Addendum” parameter, and the max and min X points to a “Horizontal Addendum” parameter. When you rotate the sketch, this rectangle will also rotate and update automatically.

8. In order to compare area measurements later on, we need to fill in the sketch. Use the Fill tool.

9. Great! The geometry is finished. Now we need to add parameters and measurements. Create two new parameters for the length and width of the blank by clicking Formula then New Parameter of type Length.

10. Double click the newly create Width parameter. In the value field, right click and then select Measure Between.

11. Select the two corner points to measure the width. Repeat this process for the length parameter.

12. Make another parameter, this time of type Area. Create a formula to make the area equal to the length multiplied by the width parameters.

13. Create another parameter to measure the part area. Double click the parameter, right click in the value entry area and select “Measure Item.” Measure the area of the Fill you created earlier. Your final parameter and measure spec tree should look like this:

14. It’s very important for the macro that you check this option before we continue. Go to Tools >Options>Parameters and Measure>Measure Tools. Under Update, ensure that Automatic update in part is selected. This is to automatically update the measurements that we just linked to the parameters. As the sketch is rotated, the rectangle will also update as will the length and width measurements.

Finally! Now our CATPart is setup and we are ready to begin the fun part – coding.

**Part Two: Programming the CATScript Macro**

Now that our CATPart is finally created and the geometry is all setup we can finally begin programming the macro.

- Create a new CATScript macro (or possibly CATVBA if you prefer)
- We’re going to spit out the results in Excel, so we’re going to need to launch a new session of Excel, a process I have explained here.
- Add our headers (top row labels) to the blank Excel sheet:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
'--headers into Excel Excel.Cells(1,1)="Rotation (deg)" Excel.Cells(1,2)="Length(mm)" Excel.Cells(1,3)="Width(mm)" Excel.Cells(1,4)="Area(mm^2)" Excel.Cells(1,5)="Vertical Addendum(mm)" Excel.Cells(1,6)="Horizontal Addendum(mm)" Excel.Cells(1,7)="Part Area(mm^2)" Excel.Cells(1,8) = "Scrap Area(mm^2)" Excel.Cells(1,9) = "Material Yield(%)" Excel.Cells(1,11) = "Minimum Area(mm^2)" 'Define the second row in the spreadsheet Dim RowNum as Integer RowNum = 2 |

4. Next, we’ll set the CATPart as the active document and define the rotation angle, which we’ll initially set at 0 degrees. If you run the macro and the rotate geometry is at 45 degrees, it will always change it to start at 0.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
Dim partDocument1 As Document Set partDocument1 = CATIA.ActiveDocument Dim part1 As Part Set part1 = partDocument1.Part Dim hybridBodies1 As HybridBodies Set hybridBodies1 = part1.HybridBodies Dim hybridBody1 As HybridBody Set hybridBody1 = hybridBodies1.Item("Geometrical Set.1") Dim hybridShapes1 As HybridShapes Set hybridShapes1 = hybridBody1.HybridShapes Dim hybridShapeRotate1 As HybridShape Set hybridShapeRotate1 = hybridShapes1.Item("Rotate.1") Dim angle1 As Angle Set angle1 = hybridShapeRotate1.Angle Dim rotationangle as integer rotationangle = 0 |

5. Now we’ll define all the parameters we made so that we can export the parameter data from CATIA into Excel later.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
'---get the length, width, and area parameters----- Dim parameters1 As Parameters Set parameters1 = part1.Parameters Dim LengthLong As Parameter Set LengthLong = parameters1.Item("Length.1") Dim LengthWide As Parameter Set LengthWide = parameters1.Item("Length.2") Dim oArea As Parameter Set oArea = parameters1.Item("Area.1") Dim oVert As Parameter Set oVert = parameters1.Item("Vertical Addendum") Dim oHorz As Parameter Set oHorz = parameters1.Item("Horizontal Addendum") Dim oPartArea As Parameter Set oPartArea = parameters1.Item("Part Area") |

6. The most critical part of the code is creating a For Next loop to rotate the sketch one degree, export all the measurement data, then rotate again, all the way from zero to ninety degrees.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
'rotate the blank from 0 to 90 degrees in 1 degree increments For i = 0 to 90 angle1.Value = rotationangle 'update all the measurements before exporting to Excel part1.Update 'column A – show the rotation angle (will be a value between 0 and 90) Excel.Cells(RowNum,1)= rotationangle 'Column B – show the length measurement Excel.Cells(RowNum,2) =LengthLong.Value 'Column C – show the Width measurement Excel.Cells(RowNum,3) =LengthWide.Value 'Column D – show the rectangle area measurement (length x width) Excel.Cells(RowNum,4) =oArea.Value * 1000000 'Vertical addendum Excel.Cells(RowNum,5) = oVert.Value 'Horiztonal addendum Excel.Cells(RowNum,6) = oHorz.Value 'Part area – will not change Excel.Cells(RowNum,7) = oPartArea.Value * 1000000 'calculate the scrap area (rectangle area – sketch area) Excel.Cells(RowNum,8).Formula = "=D" & RowNum & " - G" & RowNum 'calculate the percentage of material used (the higher the better) Excel.Cells(RowNum,9).Formula = "=G" & RowNum & "/D" & RowNum 'next rotation angle - one degree increments rotationangle = rotationangle + 1 'next Excel row RowNum = RowNum + 1 Next 'Find the minimum value for the best material yield Excel.Cells(2,11).Formula = "=MIN(D2:D92)" |

7. To make it easy to find the best blank layout orientation, we should highlight the Excel row that contains the smallest blank area value. Use the code below to highlight an Excel row based on another cell’s value.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
'------highlight min row------ For j = 2 to 92 If Excel.Cells(j,4) = Excel.Cells(2,11) Then Excel.Cells(j,4).EntireRow.Interior.ColorIndex = 39 Else End if Next |

8. Finally, a few clean up and Excel formatting items, like bolding the header, centering the data in the columns, and auto-fitting the column widths.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
'---Excel Formatting and Clean Up------------- Excel.Rows("1:1").Select Excel.Selection.Font.Bold = True Excel.Range("A1:K92").EntireColumn.Autofit Excel.Range("A1:K92").HorizontalAlignment = xlCenter Excel.Range("A1:K92").VerticalAlignment = xlCenter Excel.Range("I2:I92").Select Excel.Selection.NumberFormat = "0.00%" Excel.Rows("1:1").Select Excel.Visible = True End Sub |

The final result should look like this:

Changing the addendum sizes will potentially yield different results. Again, here’s the end result you should see:

Now there’s no more guessing or trial in error in knowing what the best blank layout is. You’ll know exactly what the best orientation is in order to have the highest material yield. I hope you found this tutorial fun and useful!

To see other tutorials I have to offer, check out my page here.