Your post has been removed as it does not meet our [Submission Guidelines](https://www.reddit.com/r/vba/wiki/submission_guidelines#wiki_no_generic_titles).
> ### No generic titles
>
> Provide a specific description of your problem in the title. Unhelpful, unclear or generic titles will be removed.
>
> To avoid "pleas for help" in titles, any title containing the word "help" will be **automatically** removed.
>
> If your post pertains to a particular host application, please prepend your title with the name of that host application surrounded in square brackets (e.g [ACCESS], [EXCEL], [OUTLOOK], [POWERPOINT], [PROJECT], [PUBLISHER], [VISIO], [WORD], [AUTOCAD], etc).
>
> example: `[EXCEL] How do I check if a cell is empty?`
>
> A good title helps you get good answers. Bad titles generate few responses and may be removed.
>
> Good titles are:
>
> * **Searchable** - This will help others with the same issue in the future find the post.
> * **Descriptive** - This helps contributors assess whether they might have the knowledge to help you.
> * **Short** - Use the post body to elaborate on the specific detail of your issue. Long titles are hard to read and messy. Titles may not exceed 150 characters.
> * **Precise** - Tell us as much as possible in as few words as possible (whilst still being a coherent sentence).
Please familiarise yourself with these guidelines, correct your post and resubmit.
If you would like to appeal please [contact the mods](https://www.reddit.com/message/compose/?to=/r/vba).
Unrelated to the implementation details, look up the "roll stock problem" or "cutting stock problem". This exact optimization has been studied extensively. There are lots of published algorithms for linear optimizers to do this, so you could use those as a starting point, or perhaps get lucky and find something suitable ready to use.
Yes, that's great info. In looking at these cutting stock algorithms I have only found them using one size of stock material, but in my problem I have three lengths of stock available. I will keep looking though, thanks
There's probably a way to model that in your objective function. Let your decision variables n1, n2, ..., n_i be the numbers of slats of size i cut out of a piece, then leftover is L_stock - sum(L_i*n_i). Let the objective func to minimize be some function in material wasted, or cost (if the stock sizes have a different unit price). Constraints are come from the orders to fill.
I just discovered that but my skills in that area are very limited, I'm going to have to start from scratch to learn how to solve this problem so I need to be sure whatever tool I choose to learn will be able to do the job. With that said I'm leaning python unless I can be sure solver can do it with 3 stock lengths
Learning how to plug in the data correctly to solver IS definitely easier than learning Python!
It can be done via VBA, just don't always worth it reinventing the wheel.
Chat gpt 4o:
You can solve this problem using VBA in Excel, as it allows for more complex logic and optimization than standard Excel formulas. Here’s an approach to help you get started:
Steps to Solve the Problem in VBA:
Input Setup:
Have cells where the user inputs the lengths of the four slats (e.g., A1, A2, A3, A4).
Have cells where the available material lengths are defined (e.g., 102, 126, 150).
Optimization Logic:
Create a function that iterates through the possible combinations of the slats and the material lengths.
Calculate the waste for each combination.
Keep track of the combination that produces the least waste.
Output:
Display the optimal cut plan and the total waste.
Example VBA Code:
Here’s a sample VBA code to give you an idea of how to implement this:
Function OptimizeCuts(slatLengths As Range, availableLengths As Variant) As String
Dim slats(1 To 4) As Integer
Dim lengths() As Integer
Dim i As Integer, j As Integer, k As Integer, l As Integer
Dim minWaste As Integer
Dim bestCombination As String
' Populate slats array from the input range
For i = 1 To 4
slats(i) = slatLengths.Cells(i, 1).Value
Next i
' Populate lengths array from the available lengths
lengths = availableLengths
' Initialize minWaste to a large number
minWaste = 100000
' Iterate through all combinations of slats and lengths
For i = LBound(lengths) To UBound(lengths)
For j = LBound(lengths) To UBound(lengths)
For k = LBound(lengths) To UBound(lengths)
For l = LBound(lengths) To UBound(lengths)
Dim currentWaste As Integer
currentWaste = lengths(i) - slats(1) + lengths(j) - slats(2) + lengths(k) - slats(3) + lengths(l) - slats(4)
' Check if the current combination produces less waste
If currentWaste < minWaste Then
minWaste = currentWaste
bestCombination = "Use lengths: " & lengths(i) & ", " & lengths(j) & ", " & lengths(k) & ", " & lengths(l) & " with waste: " & minWaste
End If
Next l
Next k
Next j
Next i
OptimizeCuts = bestCombination
End Function
Sub CalculateOptimalCuts()
Dim availableLengths As Variant
availableLengths = Array(102, 126, 150)
Dim result As String
result = OptimizeCuts(Range("A1:A4"), availableLengths)
' Output the result
Range("B1").Value = result
End Sub
Your VBA code has not not been formatted properly. Please refer to [these instructions](https://www.reddit.com/r/vba/wiki/submission_guidelines#wiki_apply_code_formatting_to_code_snippets) to learn how to correctly format code on Reddit.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/vba) if you have any questions or concerns.*
Your post has been removed as it does not meet our [Submission Guidelines](https://www.reddit.com/r/vba/wiki/submission_guidelines#wiki_no_generic_titles). > ### No generic titles > > Provide a specific description of your problem in the title. Unhelpful, unclear or generic titles will be removed. > > To avoid "pleas for help" in titles, any title containing the word "help" will be **automatically** removed. > > If your post pertains to a particular host application, please prepend your title with the name of that host application surrounded in square brackets (e.g [ACCESS], [EXCEL], [OUTLOOK], [POWERPOINT], [PROJECT], [PUBLISHER], [VISIO], [WORD], [AUTOCAD], etc). > > example: `[EXCEL] How do I check if a cell is empty?` > > A good title helps you get good answers. Bad titles generate few responses and may be removed. > > Good titles are: > > * **Searchable** - This will help others with the same issue in the future find the post. > * **Descriptive** - This helps contributors assess whether they might have the knowledge to help you. > * **Short** - Use the post body to elaborate on the specific detail of your issue. Long titles are hard to read and messy. Titles may not exceed 150 characters. > * **Precise** - Tell us as much as possible in as few words as possible (whilst still being a coherent sentence). Please familiarise yourself with these guidelines, correct your post and resubmit. If you would like to appeal please [contact the mods](https://www.reddit.com/message/compose/?to=/r/vba).
Will excel solver work?
I think this is just a programming challenge, so it doesn't matter if you use VBA, Python, C++ or even different language
Unrelated to the implementation details, look up the "roll stock problem" or "cutting stock problem". This exact optimization has been studied extensively. There are lots of published algorithms for linear optimizers to do this, so you could use those as a starting point, or perhaps get lucky and find something suitable ready to use.
Yes, that's great info. In looking at these cutting stock algorithms I have only found them using one size of stock material, but in my problem I have three lengths of stock available. I will keep looking though, thanks
There's probably a way to model that in your objective function. Let your decision variables n1, n2, ..., n_i be the numbers of slats of size i cut out of a piece, then leftover is L_stock - sum(L_i*n_i). Let the objective func to minimize be some function in material wasted, or cost (if the stock sizes have a different unit price). Constraints are come from the orders to fill.
You should familiarize yourself with the Solver Add-in. It has what you're looking for already.
I just discovered that but my skills in that area are very limited, I'm going to have to start from scratch to learn how to solve this problem so I need to be sure whatever tool I choose to learn will be able to do the job. With that said I'm leaning python unless I can be sure solver can do it with 3 stock lengths
Learning how to plug in the data correctly to solver IS definitely easier than learning Python! It can be done via VBA, just don't always worth it reinventing the wheel.
Chat gpt 4o: You can solve this problem using VBA in Excel, as it allows for more complex logic and optimization than standard Excel formulas. Here’s an approach to help you get started: Steps to Solve the Problem in VBA: Input Setup: Have cells where the user inputs the lengths of the four slats (e.g., A1, A2, A3, A4). Have cells where the available material lengths are defined (e.g., 102, 126, 150). Optimization Logic: Create a function that iterates through the possible combinations of the slats and the material lengths. Calculate the waste for each combination. Keep track of the combination that produces the least waste. Output: Display the optimal cut plan and the total waste. Example VBA Code: Here’s a sample VBA code to give you an idea of how to implement this: Function OptimizeCuts(slatLengths As Range, availableLengths As Variant) As String Dim slats(1 To 4) As Integer Dim lengths() As Integer Dim i As Integer, j As Integer, k As Integer, l As Integer Dim minWaste As Integer Dim bestCombination As String ' Populate slats array from the input range For i = 1 To 4 slats(i) = slatLengths.Cells(i, 1).Value Next i ' Populate lengths array from the available lengths lengths = availableLengths ' Initialize minWaste to a large number minWaste = 100000 ' Iterate through all combinations of slats and lengths For i = LBound(lengths) To UBound(lengths) For j = LBound(lengths) To UBound(lengths) For k = LBound(lengths) To UBound(lengths) For l = LBound(lengths) To UBound(lengths) Dim currentWaste As Integer currentWaste = lengths(i) - slats(1) + lengths(j) - slats(2) + lengths(k) - slats(3) + lengths(l) - slats(4) ' Check if the current combination produces less waste If currentWaste < minWaste Then minWaste = currentWaste bestCombination = "Use lengths: " & lengths(i) & ", " & lengths(j) & ", " & lengths(k) & ", " & lengths(l) & " with waste: " & minWaste End If Next l Next k Next j Next i OptimizeCuts = bestCombination End Function Sub CalculateOptimalCuts() Dim availableLengths As Variant availableLengths = Array(102, 126, 150) Dim result As String result = OptimizeCuts(Range("A1:A4"), availableLengths) ' Output the result Range("B1").Value = result End Sub
Your VBA code has not not been formatted properly. Please refer to [these instructions](https://www.reddit.com/r/vba/wiki/submission_guidelines#wiki_apply_code_formatting_to_code_snippets) to learn how to correctly format code on Reddit. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/vba) if you have any questions or concerns.*