+ Reply to Thread
Results 1 to 5 of 5

UDF not working quite good

  1. #1
    Forum Contributor
    Join Date
    02-09-2014
    Location
    Kamnik, Slovenia
    MS-Off Ver
    Excel 2010
    Posts
    693

    UDF not working quite good

    Hi,

    I have an UDF function to calculate time. One of the SUM is not working, and I cannot fix It.

    Please help me fix this UDF !

    Take a look in the sample, SUM is not working in BQ3 cell (orange cell), It should SUM positive times in range, but It doesn't
    Attached Files Attached Files

  2. #2
    Forum Contributor lancer102rus's Avatar
    Join Date
    04-02-2014
    Location
    Ufa
    MS-Off Ver
    Excel 2010, 2013
    Posts
    252

    Re: UDF not working quite good

    I think you don`t need UDF. To sum only positive times use formula SUMIF. E.g.
    Please Login or Register  to view this content.
    I have a question about your formula in column A. What is it calculating? Why do you work with time like with strings?
    Last edited by lancer102rus; 08-11-2014 at 03:41 AM.

  3. #3
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: UDF not working quite good

    Quote Originally Posted by Lukael View Post
    I have an UDF function to calculate time. [....] Take a look in the sample, SUM is not working in BQ3 cell (orange cell)
    The primary problem is: BQ5 and BQ15 are strings ("21:00" and "24:00"), not numeric time. That causes errors in your UDF, which go undetected because you have On Error Resume Next.

    Should you fix BQ5 and BQ15 (et al) so they are numeric time? Or do you need help to ensure that your UDF tolerates "time strings"?

    Also note that BQ8, BQ13 and BQ18 are strings with one space (" "), not null strings (""). Those also cause errors. But they are are benign because of the On Error Resume Next.

    Nevertheless, if that is unexpected, I suggest that you clean up your data.

    [EDIT] PS.... It appears that you have "time strings" in column A so that you can represent negative time. It is unclear whether positive must also be "time strings". That is a design issue that only you can answer. Nevertheless, it appears that the UDF expects to work with the negative "time strings". So it should tolerate positive "time strings", too. So obviously, there is a design flaw in the UDF.

    With positive "time strings", the runtime error arises because Brr=1 and Drr(0) is the string "21:00", for example. Thus, Int(Drr(0)) causes a "type mismatch" error. I'm sorry, but I don't have time now to study the UDF implementation and offer a fix.
    Last edited by joeu2004; 08-11-2014 at 04:03 AM.

  4. #4
    Forum Contributor
    Join Date
    02-09-2014
    Location
    Kamnik, Slovenia
    MS-Off Ver
    Excel 2010
    Posts
    693

    Re: UDF not working quite good

    Quote Originally Posted by lancer102rus View Post
    I think you don`t need UDF. To sum only positive times use formula SUMIF. E.g.
    Please Login or Register  to view this content.
    Unfortunatelly that is not working, and tried that before posting here. Regular =SUM(BQ5:BQ20) doesn't work too.

    I have a question about your formula in column A. What is it calculating? Why do you work with time like with strings?
    Formulas in column A and formulas with UDF function do the calculations for positive and negative times, that why strings.

  5. #5
    Forum Contributor
    Join Date
    02-09-2014
    Location
    Kamnik, Slovenia
    MS-Off Ver
    Excel 2010
    Posts
    693

    Re: UDF not working quite good

    The primary problem is: BQ5 and BQ15 are strings ("21:00" and "24:00"), not numeric time. That causes errors in your UDF, which go undetected because you have On Error Resume Next.
    Maybe, but look in cell BR3 and all cells in range for SUM, you'll notice that UDF works fine with strings. Actually exact same formula in cells for SUM, just opposite one.

    Also note that BQ8, BQ13 and BQ18 are strings with one space (" "), not null strings (""). Those also cause errors. But they are are benign because of the On Error Resume Next.
    These are empty cells, you probably mean different ones ?

+ 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. Good day!
    By notinuse in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 03-12-2014, 09:06 PM
  2. Good Day, and thank you
    By muzashi1963 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 08-06-2013, 10:57 PM
  3. Good Day All
    By geoeol in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 05-24-2013, 09:48 AM
  4. Good Day
    By Crafttime in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 01-03-2013, 09:00 AM
  5. good job
    By reyryg tr rytytr reerter in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 05-21-2005, 11:32 AM

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