31 Replies Latest reply on May 7, 2018 2:34 AM by Rob Edwards

# How many lengths of material to order? Here's a macro to answer!

You've drawn a weldment and are wondering how many lengths of steel to order?

Solidworks gives you the total length - but that doesn't really help most of the time.

I've written this macro which you run against a part or assembly.  You enter the amount of material you are likely to waste on each cut (a few mm) and the length your supplier offers that section - 6m for example - for each size.

It starts with that length and subtracts lengths from the cut list until there is not enough left to fit any of the bits from the cut list in and starts a new length - and so on until all the items in your cut list have been 'cut'

It pops up a message box showing the number of lengths of each section required plus the amount of wastage.  This is useful to decide if it's better to order say 6m or 16m lengths, based on the wastage.

Lastly, it writes a text file showing the following:

Bill of Materials:

100x50x3.5 RHS C350 -     4650mm     Angle 1: 45°     Angle 2: 45°     Qty: 2

100x50x3.5 RHS C350 -     625mm     Angle 1: 45°     Angle 2: 45°     Qty: 2

100x50x3.5 RHS C350 -     250mm     Angle 1: 45°     Angle 2: 0°     Qty: 4

......

Optimised Cutting List:

RHS200x200x10

Length: 1 (16000mm) - 3650, 3650, 2300.49, 2300.49, 2280.13, 771.13, 771.13,

Length: 2 (16000mm) - 2280.13, 2064, 2064, 313.22, 313.22, 308.57, 308.57, 308.57, 308.57,

RHS80x80x5

Length: 1 (16000mm) - 3039.77, 3039.77, 3033.49, 3033.49, 1514, 1514, 717.98,

Length: 2 (16000mm) - 1485.74, 1485.74, 1485.74, 1485.74, 1482.76, 1482.76, 1482.76, 1482.76, 1000, 1000, 891.85, 849.97,

Length: 3 (16000mm) - 827.86, 717.98, 717.98, 717.98, 653.44, 653.44, 635, 635, 627.18, 627.18, 627.18, 627.18, 510.52, 510.52, 469.85, 469.85, 469.85, 469.85, 431.28, 389.39, 389.39, 389.39, 291.13, 291.13, 291.13, 291.13,

........

Summary:

RHS200x200x10 - 2 x 16000mm Lengths    With 7848mm Left over.

RHS80x80x5 - 3 x 16000mm Lengths   With 2310mm Left over.

....

I've also added a .bmp you can use as an icon (if you like) when you add the macro to a toolbar

Hope you find it useful!

Si

• ###### Re: How many lengths of material to order? Here's a macro to answer!

Don't use weldments much, but I downloaded your Macro and looks like you put a lot of work into it. Thank You! Wish there was a place on this forum where all macros were kept for future reference.

• ###### Re: How many lengths of material to order? Here's a macro to answer!

Brian Brazeau wrote:

Don't use weldments much, but I downloaded your Macro and looks like you put a lot of work into it. Thank You! Wish there was a place on this forum where all macros were kept for future reference.

I've been thinking the same thing myself.

Do you think we could create a new post in the api section that is an index to useful macros

each macro could have it's own post (home) and people can improve, offer variations over time etc...

It does seem like there are many requests for stuff over and over.  It would require discipline from contributors to keep it tidy

I'm not too sure of the best format.  Glenn's FAQ posts were so good they got taken up officially

• ###### Re: How many lengths of material to order? Here's a macro to answer!

Rob Edwards wrote:

Brian Brazeau wrote:

Don't use weldments much, but I downloaded your Macro and looks like you put a lot of work into it. Thank You! Wish there was a place on this forum where all macros were kept for future reference.

I've been thinking the same thing myself.

Do you think we could create a new post in the api section that is an index to useful macros

each macro could have it's own post (home) and people can improve, offer variations over time etc...

It does seem like there are many requests for stuff over and over. It would require discipline from contributors to keep it tidy

I'm not too sure of the best format. Glenn's FAQ posts were so good they got taken up officially

This would be great , that way the macros can include instructions too. I have some that only work a certain way and no info telling me the specific instructions.

• ###### Re: How many lengths of material to order? Here's a macro to answer!

John Stoltzfus has his great spreadsheet search tool, but it would be nice to create some kind of agreed on structure to organise it directly from the forum

Off the bat I think we would need an FAQ

A master post listing categories with links to other sub posts

eg

File Properties

Cut Lists

Drawings

Configurations

Drawings

Import

...

Then at that level delve down into specific macros that do certain tasks

We could also have a requests page

Sorry for hijacking this post, I'm just putting this out there.

Be nice to have the input of a moderator and other forum users

• ###### Re: How many lengths of material to order? Here's a macro to answer!

We can use the already available 3d Content Central site for that.

• ###### Re: How many lengths of material to order? Here's a macro to answer!

Thanks Deepak that's good to know, I was always just on here

• ###### Re: How many lengths of material to order? Here's a macro to answer!

Simon, thanks for sharing the macro!!

• ###### Re: How many lengths of material to order? Here's a macro to answer!

Thanks Deepak, It probably owes more to you, the examples and advice you've posted!

I don't post much - but I do lurk in the background!

I've written another version which I'll post when I'm back at work that can be run against a part or assembly.  It only seems to recurse one level deep (won't read parts from sub-assemblies) but I'll figure that out soon.

Do any of you know if it's possible to 'insert' a new type of table into the tables menu?

The way I'd love this to work is you select insert a table into a drawing, it runs the macro and pre-populates the table - or something like that.

Then the 'optimised' cut list can be placed on a drawing in place of the regular cut list.  Best I've come up with is just to copy / paste the text file into a note on a drawing.  OK, but not as integrated.

The reaction from the guys in the workshop has been good.  Although everyone assumes the Movie industry has unlimited budgets, actually they are quite tight (in the UK at least) - something that saves a few lengths of steel goes down well.

It came about from the boss asking for an estimate of the steel required for a big frame.  It took half an hour to estimate.  I thought there has got to be a better way to calculate it!

When I ran this, I had over-estimated by 8 lengths from a total of 48.  Better to be over - but it still hurts budgets & cash-flow.

Si

• ###### Re: How many lengths of material to order? Here's a macro to answer!

Simon Rafferty wrote:

Thanks Deepak, It probably owes more to you, the examples and advice you've posted!

I don't post much - but I do lurk in the background!

Thanks for the kind words Simon but like you I've also been learning from the examples/knowledge shared by others and of course the API help files.

Do any of you know if it's possible to 'insert' a new type of table into the tables menu?

The way I'd love this to work is you select insert a table into a drawing, it runs the macro and pre-populates the table - or something like that.

Then the 'optimised' cut list can be placed on a drawing in place of the regular cut list.

The best would be to use a general table (again this can be a defined template having the column headers filled out) and have the cell populated withe desired values.

• ###### Re: How many lengths of material to order? Here's a macro to answer!

Thanks Deepak - you're a star!

• ###### Re: How many lengths of material to order? Here's a macro to answer!

Simon Rafferty wrote:

I've written another version which I'll post when I'm back at work that can be run against a part or assembly. It only seems to recurse one level deep (won't read parts from sub-assemblies) but I'll figure that out soon.

Try the codes from this macro to go any level in active assy https://forum.solidworks.com/message/849298#comment-849298

• ###### Re: How many lengths of material to order? Here's a macro to answer!

Awesome!   Simon Thank You cant wait to take a look

• ###### Re: How many lengths of material to order? Here's a macro to answer!

Superb convenience and exhalations upon you Simon! Excellent work.  I can't emphasize this enough.

I've been asking for this (in context of Routing) for over a year. If I understand it well, this is exactly what I'm after.

In the past, I've just totaled lengths, divide by purchase length, rounded up, and maybe added a stick. Thankfully, our leftover stock is easily used up in similar custom products. This will impress my shop manager because it's his responsibility to decide how much we buy. I can't wait to put it through its paces the next time we prepare for fabrication.

• ###### Re: How many lengths of material to order? Here's a macro to answer!

For me, it's more about accurately costing the steel required.  It will all get used regardless - but this seems to make the workshop manager happy!

Si

• ###### Re: How many lengths of material to order? Here's a macro to answer!

So maybe there should be a way for people to divert some of their maintenance fees to these third party people that are doing the development work that SWX isn't?  This seems like a feature that should be part of the core program.  Anyone that's had to do this by hand would agree.

• ###### Re: How many lengths of material to order? Here's a macro to answer!

I wasn't jumping to that conclusion, but rather just being thankful for a wish granted - regardless of how or by whom. Nothing we discuss will change methodology of business practices, particularly where money is involved.

• ###### Re: How many lengths of material to order? Here's a macro to answer!

I enjoy writing them.

There is so much open source software out there that I've used and used for inspiration - I feel it's important to give back, to pay the favour forward.

• ###### Re: How many lengths of material to order? Here's a macro to answer!

Do you mind if I convert this to add-in? This looks pretty useful and I want to distribute as a free goodie.

• ###### Re: How many lengths of material to order? Here's a macro to answer!

That would be cool!  I'd like to learn how.

Might be worth waiting until later this week - I need to add Deepak's code to recurse through sub-assemblies and maybe insert the results into a proper table that you can insert into a drawing.

If you have a link to an idiot's guide to compiling an add-in - I'd be very interested!  I have a few odd macros I've written that would be great encapsulated as an add-in

Si

• ###### Re: How many lengths of material to order? Here's a macro to answer!

This looks great Simon, Thankyou

but do we need to add an info anywhere such as were to find weldments?

something missing here?

when i run i get this

then

then get to chose where to put it

• ###### Re: How many lengths of material to order? Here's a macro to answer!

Ran from assembly file and it works

• ###### Re: How many lengths of material to order? Here's a macro to answer!

I need to upload another version - I'd forgotten to include the call to a function in the code that detects a part rather than an assembly!

I'm going to add Deepak's code to recurse through sub-assemblies and I'll update the attachment (Hopefully) later today!

Si

• ###### Re: How many lengths of material to order? Here's a macro to answer!

I've written a slightly better version, inspired by Deepak's method of traversing sub-assemblies, except I've written it recursively.

What does recursive mean?  It's where a function calls itself.

In this case it looks to see if there is a sub-feature.  If there is, it calls itself (to see if the sub feature has a sub feature) - and so on until it has reached the top of the tree of sub-assemblies.

When it reaches the top, it looks for weldments in the 'branches' then falls back down one level until it gets back to the roots.

This makes the code much smaller but means it will traverse as many levels of sub-assemblies as there are.  Deepak's code traversed 3 levels IIRC, but on one of my assemblies it didn't go far enough.

• ###### Re: How many lengths of material to order? Here's a macro to answer!

Not sure if this has been covered already.  This seems to use a "greedy" algorithm.  Greedy algorithms don't work "perfectly" for this type of problem.  They work ok at best.  Let me explain.

I can purchase full lengths at 10 feet.  I have two 4' and four 3' pieces.  Also, I have zero waste for the sake of calculations.  Now you and I know that I need to purchase 2 lengths right?  [(3 + 3 + 4 = 10), (3 + 3+ 4 = 10)].   However, a greedy algorithm would calculate [(4 + 4 = 8), (3 + 3+ 3 = 9), (3 = 3)].

The answer to the problem is much more difficult to solve and is actually NP Hard.  This means that it cannot be solved in polynomial time.  In other words, it could take 2 seconds or trillions of years.  Often times, it takes somewhere in the middle.  Even then, there is often waste tolerance built into the algorithm.  But even then, these algorithms are not guaranteed to find the optimal solution.  Just a solution.

On the bright side, your application will be good enough to be very very useful for many users.  Bravo!

• ###### Re: How many lengths of material to order? Here's a macro to answer!

You are the first to make this observation!

I pondered the problem for a while, appreciating it is a non-trivial problem to come up with the optimal solution.  Similar in some ways to the classic 'travelling salesman' problem.

After looking at the wastage suffered in most cases by not providing any guidance, I decided that a simple (quick) solution would give me the bulk of the savings immediately - and a mechanism to provide near instantaneous quotations with a favourable degree of accuracy.

• ###### Re: How many lengths of material to order? Here's a macro to answer!

Yup, that's all you can do.

But that does answer the question as to why SolidWorks doesn't already do this seemingly trivial task.

• ###### Re: How many lengths of material to order? Here's a macro to answer!

I concur with Jesse. In my personal opinion, the only way to conquer these NP-hard problems is with a quantum computer!

• ###### Re: How many lengths of material to order? Here's a macro to answer!

That said, the solution space for this problem is fairly small (relative to some) - it might be viable just to run a sledgehammer type approach (trying every permutation), then if a solution has not been found within a few seconds, fall back to a less optimal algorithm.

In the 90's I used GA's to find near optimal solutions to a similar NP problem.  With them, a sledgehammer approach took about 24h to complete.  The GA achieved 80% of the optimal solution in a couple of minutes.  The eventual system delivered allowed you to wait as long or as short a time as you liked, understanding that the longer you wait, the better the solution - but that operationally you didn't always have that long.

That version might have to wait until I retire though!

Si

• ###### Re: How many lengths of material to order? Here's a macro to answer!

Hi Simon

I's been a beautiful sunny day on this holiday weekend, and what did I do? Have a bonfire and play with vba.

I'm used to manually getting out cutlists, it was my job for about 10 years.  So I thought how would I do it?

..and can I program that way.

My basic strategy is to just keep cutting the longest item.  Now I know this is inefficient but as you say, the benefits of a quick and ready estimator are good enough already.  For me having too little stock costs more in lost time as every cut is crucial, having no room for mistakes really puts you on the edge.   For us the real cost benefit is in terms of saving time.  That is our most precious resource, the one thing we can't buy.

The advantage for us would be not optimising the material usage but optimising the cut order.

Anyway hopefully this is just the start of my project, I don't know when I will have time to look again.

Programming-wise I implemented a Priority Queue (Heap)

here it's nearly finished at item 122/127.  You can see the three heaps.

The hardest part was trying to get the heap to format nicely, its still not right but it works ok for 3 digit numbers

I just generated random numbers for testing

But the results are promising, generally about 1% waste with maybe a couple% usable off-cuts.

I'm guessing that this kind of approach will favour a random distribution, so it would be great if someone could give me a typical use data set.

I have a few ideas for optimisation:.

• Looking down a few levels
• Multiple stock bins
• Job numbering / Item Identification
• Cut instructions
• Combining identical lengths

Anyway I’m all out of time.

It would be really nice to collaborate with others on something like this.

I attach the log file and the code (when I’ve cleaned it up a bit)

• ###### Re: How many lengths of material to order? Here's a macro to answer!

Hi Irvana,

Nice tool....I would add two small but significant items to make it usable in the real world.

1) Add PartNo (identifier for cut list)

Yours solves quite fast. We use 1D Stock Cutter by 1D Stock Cutter - Linear material cutting optimization / length nesting software

But I have used Excel solver base solutions as well. Either way, you will need to identify your cut pieces in the cut list to make it applicable.

Otherwise well done.

I find Simon's effort a great teaching moment, But would have to say that this functionality belongs outside SW.

Good effort & Kudos to all contributors

Elmar