HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Produce a Linear Cut List for Woodworking

Submitted by: @import:stackexchange-codereview··
0
Viewed 0 times
codereviewstackoverflowexcelvba
linearproducewoodworkingcutforlist

Problem

I was inspired to write this by a recent review of cutting pipes.

The only thing I'm really iffy about is using the WriteCuts method. I couldn't figure out a way to Let listOfCuts() out of Class TwoByFour.
Example

If you want to build a table (out of 2x4s), you'll need

  • 4 legs of x length



  • 2 long sides of y length and



  • 2 short sides of z length



This could be represented in a table like so -

Length  Qty
48      4
30      2
15      2


Assuming length is in imperial inches and I can only buy 96" boards at the store, I want to know how many boards I need to buy to get the lengths and quantities I want. In this (theoretical) case I need to buy 3 boards -

Board   Cuts        Remainder
1       48, 48         0
2       48, 30, 15     3
3       48, 30, 15     3


Algorithm

So with this simplified explanation, I can figure out my cuts by using the board's off-cut piece for each length until I need a new board. This is the algorithm I'm using.

Pseudocode -

  • Put length/qty table in array



  • Create list of pieces (descending in length)



  • Use a board until you can't make another piece



  • Get another board



  • Repeat until all pieces are cut



Class TwoByFour

```
Option Explicit
Const BOARD_LENGTH As Long = 96
Private index As Long
Private remainder As Double
Private listOfCuts() As Double

Private Sub Class_Initialize()
ReDim listOfCuts(1 To 1)
listOfCuts(1) = BOARD_LENGTH
End Sub

Public Property Get NumberOfCuts() As Long
NumberOfCuts = UBound(listOfCuts)
End Property

Public Property Get Offcut() As Double
Offcut = listOfCuts(UBound(listOfCuts))
End Property

Public Sub MakeCut(length As Double)
index = UBound(listOfCuts)
remainder = listOfCuts(index) - length
listOfCuts(index) = length
ReDim Preserve listOfCuts(1 To index + 1)
listOfCuts(index + 1) = remainder
End Sub

Public Function WriteCuts() As

Solution

I approached the problem with some differences in the methods assigned to the TwoByFour class. One of the things I added is to add the blade kerf - the width of the material removed by the saw blade, because this reduces the amount of remaining material.

For the TwoByFour class, I believed the object could know about its original length, the amount of unused length remaining, and the number of cuts already made. Because I always want to know which component is getting cut from which board, I chose to create an ID for each component piece in order to keep track of them.

Class TwoByFour:

Option Explicit

Private Type BoardInfo
    boardLength As Double
    kerfWidth As Double
    unusedLength As Double
    cutPieces As Scripting.Dictionary
End Type
Private this As BoardInfo

Private Sub Class_Initialize()
    this.boardLength = 0
    this.kerfWidth = 0
    this.unusedLength = this.boardLength
    Set this.cutPieces = New Scripting.Dictionary 'stored as 
End Sub

Public Property Let StockLength(newLength As Double)
    '--- new length also resets the number of cuts
    this.boardLength = newLength
    this.unusedLength = this.boardLength
    Set this.cutPieces = New Scripting.Dictionary 'stored as 
End Property
Public Property Get StockLength() As Double
    StockLength = this.boardLength
End Property

Public Property Let BladeKerf(newKerf As Double)
    this.kerfWidth = newKerf
End Property
Public Property Get BladeKerf() As Double
    BladeKerf = this.kerfWidth
End Property

Public Property Get NumberOfCutPieces() As Long
    NumberOfCutPieces = this.cutPieces.Count
End Property

Public Property Get LeftoverLength() As Double
    LeftoverLength = this.unusedLength
End Property

Public Function MakeCut(cutLength As Double, id As String) As Double
    '--- returns the remaining board length or zero if the cut can't be made
    If (cutLength  0) And (index  0) And (index <= NumberOfCutPieces) Then
        GetPieceID = this.cutPieces.Keys(index - 1)
    Else
        GetPieceID = "n/a"
    End If
End Function


I tend to organize data in Dictionaries or Arrays. In the main Sub I really used both. I agreed with @Raystafarian about sorting the components by size, but since I used a Dictionary, I wanted to use sorting functions "out of the box".


The code below relies on the SortDictionary routine from Craig Pearson, as noted in the code. (Get it here.) In turn, the SortDictionary routine relies on the QSort module. (Get it here.) Both are required to make the sorting work properly.

The main routine DimensionalLumberCutList is based on values on the worksheet, arranged as:

So the main work is done in these methods:

```
Option Explicit

Public Sub DimensionalLumberCutList()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet1")

Dim StockLength As Double
Dim kerfWidth As Double
StockLength = CDbl(ws.Range("StockLength"))
kerfWidth = CDbl(ws.Range("KerfWidth"))

Dim lastRow As Long
Dim listOfComponents As Dictionary
Set listOfComponents = New Dictionary
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

'--- requires all lengths to be unique
Dim length As Double
Dim qty As Long
Dim i As Long
Dim j As Long
For i = 2 To lastRow
length = ws.Cells(i, 1).Value
qty = ws.Cells(i, 2).Value
If qty > 0 Then
For j = 1 To qty
listOfComponents.Add (length & "-" & j), length
Next j
End If
Next i

'--- from http://www.cpearson.com/excel/CollectionsAndDictionaries.htm
' sorted for the longest pieces appear first
SortDictionary listOfComponents, False, True

Dim finalBoardCuts() As TwoByFour
finalBoardCuts = CutMyPieces(listOfComponents, StockLength, kerfWidth)

'--- output the results
On Error Resume Next
Dim numBoards As Long
Dim boardName As String
Dim thisBoard As TwoByFour
numBoards = UBound(finalBoardCuts)
If Err.Number = 0 Then
Debug.Print "Your project requires " & numBoards & " boards:"
Debug.Print "(calculated with a " & kerfWidth & " inch blade kerf)"
For i = 1 To numBoards
boardName = "Board " & i & ": "
Set thisBoard = finalBoardCuts(i)
For j = 1 To thisBoard.NumberOfCutPieces
Debug.Print boardName;
Debug.Print "component " & thisBoard.GetPieceID(j);
Debug.Print " at " & thisBoard.GetPieceLength(j) & " inches"
Next j
Next i
Else
Debug.Print "No boards to cut."
End If
End Sub

Private Function CutMyPieces(ByRef compList As Dictionary, _
Optional boardLength As Double = 96#, _
Optional bladeWidth As Double = 0.125) As TwoByFour()
'--- resulting dictionary returns list of
Dim boards() As TwoByFour
ReDim boards(1 To 1)
Set boards(UBound(boards)) =

Code Snippets

Option Explicit

Private Type BoardInfo
    boardLength As Double
    kerfWidth As Double
    unusedLength As Double
    cutPieces As Scripting.Dictionary
End Type
Private this As BoardInfo

Private Sub Class_Initialize()
    this.boardLength = 0
    this.kerfWidth = 0
    this.unusedLength = this.boardLength
    Set this.cutPieces = New Scripting.Dictionary 'stored as <ComponentID, length>
End Sub

Public Property Let StockLength(newLength As Double)
    '--- new length also resets the number of cuts
    this.boardLength = newLength
    this.unusedLength = this.boardLength
    Set this.cutPieces = New Scripting.Dictionary 'stored as <ComponentID, length>
End Property
Public Property Get StockLength() As Double
    StockLength = this.boardLength
End Property

Public Property Let BladeKerf(newKerf As Double)
    this.kerfWidth = newKerf
End Property
Public Property Get BladeKerf() As Double
    BladeKerf = this.kerfWidth
End Property

Public Property Get NumberOfCutPieces() As Long
    NumberOfCutPieces = this.cutPieces.Count
End Property

Public Property Get LeftoverLength() As Double
    LeftoverLength = this.unusedLength
End Property

Public Function MakeCut(cutLength As Double, id As String) As Double
    '--- returns the remaining board length or zero if the cut can't be made
    If (cutLength < (this.unusedLength + this.kerfWidth)) Or _
       (cutLength = this.unusedLength) Then
        this.cutPieces.Add id, cutLength
        this.unusedLength = this.unusedLength - cutLength - this.kerfWidth
    End If
End Function

Public Function GetPieceLength(index As Long) As Double
    '--- returns the length of the indicated piece
    If (index > 0) And (index <= NumberOfCutPieces) Then
        GetPieceLength = this.cutPieces.Items(index - 1)
    Else
        GetPieceLength = 0
    End If
End Function

Public Function GetPieceID(index As Long) As String
    '--- returns the length of the indicated piece
    If (index > 0) And (index <= NumberOfCutPieces) Then
        GetPieceID = this.cutPieces.Keys(index - 1)
    Else
        GetPieceID = "n/a"
    End If
End Function
Option Explicit

Public Sub DimensionalLumberCutList()
    Dim wb As Workbook
    Dim ws As Worksheet
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Sheet1")

    Dim StockLength As Double
    Dim kerfWidth As Double
    StockLength = CDbl(ws.Range("StockLength"))
    kerfWidth = CDbl(ws.Range("KerfWidth"))

    Dim lastRow As Long
    Dim listOfComponents As Dictionary
    Set listOfComponents = New Dictionary
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    '--- requires all lengths to be unique
    Dim length As Double
    Dim qty As Long
    Dim i As Long
    Dim j As Long
    For i = 2 To lastRow
        length = ws.Cells(i, 1).Value
        qty = ws.Cells(i, 2).Value
        If qty > 0 Then
            For j = 1 To qty
                listOfComponents.Add (length & "-" & j), length
            Next j
        End If
    Next i

    '--- from http://www.cpearson.com/excel/CollectionsAndDictionaries.htm
    ' sorted for the longest pieces appear first
    SortDictionary listOfComponents, False, True

    Dim finalBoardCuts() As TwoByFour
    finalBoardCuts = CutMyPieces(listOfComponents, StockLength, kerfWidth)

    '--- output the results
    On Error Resume Next
    Dim numBoards As Long
    Dim boardName As String
    Dim thisBoard As TwoByFour
    numBoards = UBound(finalBoardCuts)
    If Err.Number = 0 Then
        Debug.Print "Your project requires " & numBoards & " boards:"
        Debug.Print "(calculated with a " & kerfWidth & " inch blade kerf)"
        For i = 1 To numBoards
            boardName = "Board " & i & ": "
            Set thisBoard = finalBoardCuts(i)
            For j = 1 To thisBoard.NumberOfCutPieces
                Debug.Print boardName;
                Debug.Print "component " & thisBoard.GetPieceID(j);
                Debug.Print " at " & thisBoard.GetPieceLength(j) & " inches"
            Next j
        Next i
    Else
        Debug.Print "No boards to cut."
    End If
End Sub

Private Function CutMyPieces(ByRef compList As Dictionary, _
                             Optional boardLength As Double = 96#, _
                             Optional bladeWidth As Double = 0.125) As TwoByFour()
    '--- resulting dictionary returns list of <ComponentId, board number>
    Dim boards() As TwoByFour
    ReDim boards(1 To 1)
    Set boards(UBound(boards)) = New TwoByFour
    boards(UBound(boards)).StockLength = boardLength
    boards(UBound(boards)).BladeKerf = bladeWidth

    Dim componentLength As Double
    Dim componentId As Variant
    For Each componentId In compList.Keys
        componentLength = compList(componentId)
        If componentLength > boards(UBound(boards)).StockLength Then
            '--- too big for us, so skip it
            Debug.Print "Component " & CStr(componentId) & " is larger than the stock available."
        Else
            If componentLength <= boards(UBound(boards)).LeftoverLength Then
                boards(UBound(boards)).MakeCut componentLength, CStr(componentId)
           
Your project requires 5 boards:
(calculated with a 0.125 inch blade kerf)
Board 1: component 48-3 at 48 inches
Board 2: component 48-4 at 48 inches
Board 3: component 48-1 at 48 inches
Board 4: component 48-2 at 48 inches
Board 4: component 30-2 at 30 inches
Board 5: component 30-1 at 30 inches
Board 5: component 15-2 at 15 inches
Board 5: component 15-1 at 15 inches

Context

StackExchange Code Review Q#154618, answer score: 2

Revisions (0)

No revisions yet.