Consulting

Results 1 to 20 of 68

Thread: Advice needed to speed this code.

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #33
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,097
    Location
    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.
    Attached Files Attached Files
    Last edited by SamT; 05-27-2016 at 09:52 AM.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •