+ Reply to Thread
Results 1 to 17 of 17

The UDF is completely dead when the range has 50,000 rows for calculation

  1. #1
    Forum Contributor
    Join Date
    01-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003, 2007, 2010, 2019
    Posts
    436

    The UDF is completely dead when the range has 50,000 rows for calculation

    I created a UDF below which is expected to work nicely. The idea is to calculated weighted average for a variable (cases need to meet the criteria). But when the range contains 50,000 rows (e.g. A1:A50000), this macro is just dead. Excel just keeps running for hours after hours without responding. I thought array in VBA is quick enough to handle 50,000 rows. I am wondering if there is a better way to do calculation when there are so many rows used.

    Please Login or Register  to view this content.

  2. #2
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: The UDF is completely dead when the range has 50,000 rows for calculation

    I think your problem may stem from your use of Application.Transpose. Although I thought the limit for this function was around 65,500+, it may be that your 5 consecutive calls using it has pushed the limit in some other way. Anyway, in looking at your code, I do not see you using any functions (such as Join or Filter) that would require the array to be one-dimensional (that is what Transpose creates from a single column of contiguous cell values). So I would try changing your code not to use Application.Transpose (you'll have to add a second dimension index of 1 to each o f those five array when you call them) and see how that works. If I didn't miss any modifications to the arrays, this should do it for you...
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    01-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003, 2007, 2010, 2019
    Posts
    436

    Re: The UDF is completely dead when the range has 50,000 rows for calculation

    Just tried without Transpose, but the problem is still there. Excel keeps running without responding and in the end crashed out

  4. #4
    Forum Contributor
    Join Date
    01-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003, 2007, 2010, 2019
    Posts
    436

    Re: The UDF is completely dead when the range has 50,000 rows for calculation

    Please help me have a look at the error handling statement. There must be something wrong with it. If I change the error handling to On Error Resume Next, then it works (Excel can respond now).

    Please Login or Register  to view this content.

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: The UDF is completely dead when the range has 50,000 rows for calculation

    I suspect that the problem is here
    Please Login or Register  to view this content.
    If an error occurs anywhere in the blue code, it will take you the line in red. So you end up going round in circles.

  6. #6
    Forum Contributor
    Join Date
    01-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003, 2007, 2010, 2019
    Posts
    436

    Re: The UDF is completely dead when the range has 50,000 rows for calculation

    Thank you, sir. The error handling really drives me mad. So how can I fix it with a better error handing statements? For my case, it is just to run the next loop whenever an error occurs

  7. #7
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: The UDF is completely dead when the range has 50,000 rows for calculation

    Does the code I highlighted in blue need to run, after everything else has finished?

  8. #8
    Forum Contributor
    Join Date
    01-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003, 2007, 2010, 2019
    Posts
    436

    Re: The UDF is completely dead when the range has 50,000 rows for calculation

    Yes, sir. Basically the loop is trying to get data into the array. After that, the blue code runs to find the weighted average. So the blue code always needs to run.

  9. #9
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: The UDF is completely dead when the range has 50,000 rows for calculation

    Maybe:
    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  10. #10
    Forum Contributor
    Join Date
    01-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003, 2007, 2010, 2019
    Posts
    436

    Re: The UDF is completely dead when the range has 50,000 rows for calculation

    Thank you, Sir. I need to have data sent to arrays a() and b() first, and after that I run the code from surpL = worksheet... to calculate weighted average.

  11. #11
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: The UDF is completely dead when the range has 50,000 rows for calculation

    What do you want to happen if the part in blue returns an error, which it probably is?

  12. #12
    Forum Contributor
    Join Date
    01-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003, 2007, 2010, 2019
    Posts
    436
    Quote Originally Posted by Fluff13 View Post
    What do you want to happen if the part in blue returns an error, which it probably is?
    I can add a line to test if the divisor is zero. So the blue part should be safe.

    So I just need to sort out the loop statement in terms of error handling. If I add On Error Goto 0 below that to reset, do you think it will work?

    Thank you, Sir.

  13. #13
    Forum Contributor
    Join Date
    01-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003, 2007, 2010, 2019
    Posts
    436

    Re: The UDF is completely dead when the range has 50,000 rows for calculation

    I have now changed the error handling to below. Do you think this will work? To avoid endless loop, I reset error handling to On Error GoTo 0.

    Please Login or Register  to view this content.

  14. #14
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: The UDF is completely dead when the range has 50,000 rows for calculation

    That should be ok, another option
    Please Login or Register  to view this content.

  15. #15
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: The UDF is completely dead when the range has 50,000 rows for calculation

    You should never jump out of the middle of a loop. ( Your "on Error..." does.)
    You should never jump into the middle of a loop. (Your "Resume Next..." does.)
    You never described the conditions that you expect might raise an error; those divisions (i.e., IsNumeric(1 / weight(j)), etc. might?)

    You haven't indicated if your named ranges, e.g. weight = .Transpose(Range(code).Value) are fixed?, entire columns?, or dynamic. If they are fixed, are all rows always used?

    If a zero indicates the last used row, then you should just exit the loop.
    Please Login or Register  to view this content.
    Last edited by protonLeah; 09-21-2019 at 09:14 PM.
    Ben Van Johnson

  16. #16
    Forum Contributor
    Join Date
    01-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003, 2007, 2010, 2019
    Posts
    436

    Re: The UDF is completely dead when the range has 50,000 rows for calculation

    Hi protonLeah,

    For my case, the statement IsNumeric(1 / weight(j, 1)) will definitely result in 1/0 error in some cases. That is why I just want the loop to simply continue to the next one when an error like this occurs. So if a case of 1/0 causes the loop to terminate, then it is not something I wanted to see

  17. #17
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    995

    Re: The UDF is completely dead when the range has 50,000 rows for calculation

    To handle the divide by zero error:
    try replacing this part:

    Please Login or Register  to view this content.
    with this:

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA- how to check within a row within a range- completely stuck! :(
    By ykhan16 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-16-2019, 07:45 AM
  2. [SOLVED] The Dead Horse: Alternating Color rows REGARDLESS of hidden, autofiltered, etc Please help
    By chriscovino in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 08-15-2014, 12:27 PM
  3. [SOLVED] Delete Rows - as in remove them from the sheet completely?
    By MBlaster in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-12-2012, 05:08 PM
  4. Deleting completely blank rows ?
    By hg34 in forum Excel General
    Replies: 4
    Last Post: 08-16-2010, 08:39 PM
  5. completely clear all rows after date
    By Directlinq in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-04-2009, 06:24 AM
  6. Completely Removeing Columns and Rows from A Sheet
    By cgi_pro in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-13-2006, 05:08 AM
  7. [SOLVED] [SOLVED] Finding completely blank rows
    By J Streger in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-15-2005, 06:30 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1