Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Help! Newbie here. Macro functions but extremely slow.

  1. #1

    Help! Newbie here. Macro functions but extremely slow.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello All!


    I have had so much help with online forums and thank everyone for their help and advice. I was wondering if someone could help me with my macro. I am a civil engineer with no programming knowledge. Most of the vba macro I created is by using codes from this forum or by recording in excel and extending it a little further. It has worked so far however it takes hours and hours to process all the files. I now have a folder with over 600 files that I have to process and I have let it run overnight and it has successfully however I cannot present it to the users since it is time consuming. Couple of specific issues that I as a non-programmer can mention is:


    1. Module 37 is the master list of all the macro I use --- I dont think this has any issues
    2. Call one is pretty straight forward - it imports all the csv like files into the current workbook -- Don't think this has any issues
    3. Call two (module 6) - calculates average speed and uniformity metrics. --- This is the one I think has major issues. Since the data is variable, I did not know how to mention "check for rows with values and calculate average". Instead, I calculate average of full columns or sometimes specific cell numbers which in this case I set it as 3600 rows. Then all the calculations are done cell by cell since I recorded macro and generalized. This macro takes a ton of time.


    4. Module 29 also is recorded and is time consuming.


    5. Module 33 is also recorded and takes time to copy and paste formula cell by cell.




    I was wondering if anyone out here could help/ guide? I have attached the macro for your reference. Thanks in advance!


    Sarah
    Attached Files Attached Files

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    718
    Articles
    0
    Excel Version
    Excel 2010 64bit
    WOW.... you didn't allow much time for an answer from here before posting the same question at MrExcel did ya.

    rules on both forums will refer you to this http://www.excelguru.ca/content.php?184

  3. #3

    Sorry!

    I'm sorry. I had not read the rules. I will take this post down now. Thanks for pointing out!

  4. #4
    I cannot figure out how to delete my post. Please report my post to the moderator so they can delete it. Thanks!

  5. #5
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    718
    Articles
    0
    Excel Version
    Excel 2010 64bit
    You don't need to take it down or delete it.
    In fact your Excel questions and problems can help others, that's the idea of these forums.
    If you or anyone feels it is necessary to cross post there is a proper way and that is to include links to the question in the other forums where the question has been posted.

    You don't have enough posts on this forum to post links so this is the cross posts that I know of

    http://www.mrexcel.com/forum/excel-q...ent-macro.html
    http://www.excelforum.com/excel-prog...mely-slow.html
    http://www.ozgrid.com/forum/showthread.php?t=191169

    if there are more please add them. You will need to omit http://www. part for the forum software to accept them prior to having 5 or 6 posts.

    Thanks
    Last edited by NoS; 2014-09-29 at 06:51 PM.

  6. #6
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    USING RC notation is exceedingly difficult to edit and find out what you are wanting to do. Suggest dump a heap of comments everywhere

    1: Set Autocalculate off at the start and on again when you have finished
    Application.Calculation = xlCalculationManual and
    Application.Calculation = xlCalculationAutomatic

    2:The main reason for the slowness is that you are activating each sheet and then setting a ton of formula cell by cell.
    Take Module6 Sub Two_Avg_Speed_Loop() as an example

    a: don't activate the sheet
    b: don't select the cell(s)
    Code:
    For Each ws In ActiveWorkbook.Worksheets
    
    ws.Activate
    
    If ws.Name Like "*.CSV" Then
    
    Set range1 = Range("D:D")
    Set range2 = Range("O:O")
    Range("N1") = WorksheetFunction.Average(range1)
    Range("O1").Value = "=IF(RC[-11]="""","""",ABS(RC[-11]-R1C14))"
        Range("O1").Select
        Selection.AutoFill Destination:=Range("O1:O3600")
    I would suggest

    Code:
    For Each ws In ActiveWorkbook.Worksheets
    with ws
    
    If .Name Like "*.CSV" Then
    
    Set range1 = .Range("D:D")
    Set range2 = .Range("O:O")
    .Range("N1") = WorksheetFunction.Average(range1)
    .Range("O1").Value = "=IF(RC[-11]="""","""",ABS(RC[-11]-R1C14))"
        .Range("O1").AutoFill Destination:=Range("O1:O3600")
    
    
    end with
    3: Also make sure that you release memory (in case it's a memory problem)
    in the above need set range1 = nothing at the end etc

  7. #7
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    374
    Articles
    0
    Excel Version
    2020
    Filling whole columns - containing 2^20 cells- with formulae is madness.

  8. #8
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Total agree snb and it's not just one sheet.
    Was merely pointing out if you absolutely had to progress down that path where the biggest bottle necks are

  9. #9
    I agree that the macro is the most inefficient one anyone would come across. It just stated with couple of scenario and now that it works, I need to make 600 files run. That is why all the cells have selection and formula and paste etc. I just recorded macro in excel and applied to all worksheets in a workbook. Now it need to find ways to make it efficient and set an approximate timeline. Do you think putting comments and the letting you all know what I am trying to accomplish at every step of the macro code would help you all give me suggestions? As always, Thank you all for your time and help!

    --Sarah

  10. #10
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Sarah, it's more the size of the project. A simple read thru and make some suggestions is typically about ten minutes for a question. To go thru 1000+ lines of code, understand what is going on is about 4+ hours. I've also got tons of legacy code written by others and often there isn't the time budget to properly fix things so it's a case of what minor modifications can help the most or does it need to be dumped and rewritten.

    Some other suggestions for you are
    1: Dump it into pivot tables add file, year, month, day, hour, minute (as required) columns
    2: Create a CSV sheet, a calculation sheet and a summary sheet so

    erase summary
    for each CSV
    a) erase CSV sheet
    b) Read CSV and memory dump array into CSV sheet

    something like
    lrow = ws.Range("a1").End(xlDown).row
    lcolumn = ws.Range("A1").SpecialCells(XlCellType.xlCellTypeLastCell).Column
    oData = ws.Range("a1").Resize(lrow, lcolumn).Value2 'dump sheet to array and

    'dump back as
    Sheets(sSheet).Cells(iPacked, 1).Resize(UBound(oNewData, 1), UBound(oNewData, 2)).Value2 = oNewData


    c) Copy Results into summary (again memory dump as above)

    d) also use memory not cells, again my example above
    read oData
    redim oNewData(same rows but more columns if required)
    for each row populate all oNewData columns

Page 1 of 2 1 2 LastLast

Posting Permissions

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