Advice needed to speed this code.

[SamT]

One large problem with your approach is that everything is on one worksheet. Excel, and VBA work best if different types or groups of data are on different worksheets.

• Arrange the basic systemic data so it is very easy for Excel. (systemic = Products and factories.) Each System Unit gets its own sheet
• Design various User Friendly worksheets to present details and summaries to people. Each such Report sheet is reasonably specialized.
• Use VBA and Excel to fill out those Report sheets
• Create a VBA "Orders" UserForm for people to input Order details and a sheet to list those values in an Excel friendly format.
• Design Factory Staus sheets to list Factory Status in Excel friendly format.
• Use VBA and Excel to analyze Factory status and Order details.
• Create a VBA Userform "Assignment Orders" to present the result of the analysis to people so they can assign Orders to a select Factory.

At this time, with little knowledge of actual procedures, I am leaning towards a standardized Factory Class module and VBA code that uses Factory Class objects. Possibly a standardized Order Class Module.

See attached for Excel Friendly Systemic data sheets. The layout is critical, only the top two rows are formatted in any way, an empty column is required between different groups of data types. "LONDON" and "ENJOAR" are examples of possible data sheets for factories and Products.

Most Systemic data sheets can be hidden from the Users.

The Factory details are included on Product sheets because Product lines change much more often than Factory details.

The actual details on each example are limited to what I could find on your last attachment.

[VISHAL120]

Hi ,

Sorry for the late reply.

thanks for the proposal SamT. In fact the sheet that i have send is the sheet where the calculation is being done. The end presentation to the user is very much simpler and very user friendly . As before the calculation is ready we need to check each parameters date like the fabric, Ok prod, trims date, etc. and also to calculate the mins based on the efficiency and the minimum qty set per day and its just after all these calculations are done the all data are sent to this sheet for the main planning events to occur.

After that all these calculated data are sent back the end user sheets to show how each order and there dept has been planned and if all dates are on target and if not its highlight the dept so that the user can easily see the orders which are in danger of delivery.

All the other parts of the calculation takes almost 10 - 12 secs to occur and the only one that take a lot of time is the one that i have sent which takes almost 52 -65 secs.

So for every analysis that a user will do like changing the minimum qty per day or changing the efficiency of the specific dept he will have to wait 12+ 65 = 77 secs to see the analysis results which is very time consuming.

if you want i can send the complete file but due to the size and seeing where i am having the problem that is why i have send only the part that am really block.

[SamT]

I enjoy that kind of Project. Unfortunately my main computer is broken and this little laptop just is not good enough to work on a large project.

I would like to see the entire file, but I would only want to see the data and calculations for 3 orders and 3 factories.

So for every analysis that a user will do like changing the minimum qty per day or changing the efficiency of the specific dept he will have to wait 12+ 65 = 77 secs to see the analysis results which is very time consuming.

that is why the data should be organized for Excel and not for people. Reports and result should be organized for people.

Excel must look at the entire sheet and calculate everything on the sheet. If you have a sheet for each factory, only that sheet and the results Report sheet need to be calculated.

In programming your project is known as 'organic,' meaning that it started small and manageable, but over time more and more was added to it until now it is no longer manageable.

In my experienced opinion, it is time to step back and look at what you need, without considering what you have, then design a Project from the ground up to accomplish that in an efficient manner.

[VISHAL120]

Hi SamT,

thanks for your reply.

I will remove all the other orders from it, will place 3-4 orders with its dept and lines ( factory) and make the changes as you request and will post the file latest by tomorrow. As you say its really a big project and making a planning for a whole factory and lines at the same time for the each dept. its taken us almost a year to come what we are today and for sure it will be a great help for anyone who work in textile sector.

its not a problem for me after the completion to post it in the forum for free so that other of our friends can use it if it help them.

thanks again.

[VISHAL120]

Hi SamT,

Please find the link below for the complete project.

https://app.box.com/s/ryqk6taz3n1aa3lwd6hyy72dycysoeb7

be careful when opening and enabling as it try to add ins as a toolbar to run it. otherwise we can still deactivate the toolbar by going though the macro and run the clear toolbar .

We need to click on the plan Factory for it to run. And there are other fields where we can adjust the capacity.

if you need further information please do inform. will be glad to give and of course suggestion will be most welcome.

[VISHAL120]

Hi,

can anyone please give a recommendation and advise please.

thanking in advance.

[p45cal]

After examining the attachment in msg#58, whenever I've had the inclination to try and sort this problem out, it has been with some trepidation, since it is complex.
There is at least one effective circular reference in the sheet which needs more iterations than you'd expect for the values in the grid to become stable.
It does need a radical rethink in the approach to solving this - where you start with the raw data, make calculations on it, make calculations on those results and finally populate the grid. At the moment, values in the grid are used to populate cells outside the grid, which are then used to populate the grid again. Trying to do this in your workbook will be time-consuming. I'm not going to do it, especially after seeing that enormous workbook in msg# 65! I can't unravel the logic, and I don't know your business.

A pointer: if you were able to calculate one row at a time in that sheet, so that all the values in that row (in and out of the grid) were calcualated without circular references and remained set in concrete once calculated then you should be able to get quick (<5 seconds) results after each alteration of raw data. Try to re-arrange the columns on the sheet so that any cell's formula (or calculation through vba) refers only to cells on rows above and/or cells in columns to the left of that cell, no referring to cells to the right on the same row, and no referring to cells on rows below at all. This should guarantee the absence of circular references. Then it should be possible to convert to vba to reduce file size and cut calculation time.

[VISHAL120]

Hi P45CAL,

thanks for the advise i will try examining the code you give me as option and combine the proposal as you mentioned. One thing is that if we do not want to use the column outside the grid which is the Block start date which fetch the date of the start date of the previous dept from the grid and to add the constant date defined by user from another sheet which is ( Start_Next_Department_Days), can we make it fetch the date from the grid instead then it will more independent from the column outside the grid for the start of the next dept..

by the way for the time being i have had to decrease the number of columns from the grid which previous was 107 columns on the grid and now it have modified it to 75 columns but still its using alomost 55 secs for the whole calculation.

i will still try to find out the best way out by searching all the way possible. again thanks for helping.