udf to calculate date difference value error

[littlepete]

hello

an udf to compose all parts of a name (title, surname, first names, last name, transcription if other script) that is working
made me very happy !
i'm trying now to use (datumverschil in dutch: ) datedifference. it has three parts: first date; second date; way of measuring...

this is why i'm trying... probably i'm forgetting something?
i try to use it this way : " dv(q6;am6;"d") "

Function dv(eerste As Range, tweede As Range, str As String)
dv = DateDiff(eerste, tweede, str)
End Function

thank you for the correction or help !!!

 

[Joe4]

I do not understand the use of a User Defined Function (UDF).
Why create a UDF that does exactly what the existing DateDiff function does?
Why not just use DateDiff directly?

That seems like unnecessarily "recreating the wheel".

 

[littlepete]

I do not understand the use of a User Defined Function (UDF).
Why create a UDF that does exactly what the existing DateDiff function does?
Why not just use DateDiff directly?

That seems like unnecessarily "recreating the wheel".

the answer on your question is very simpel : my formulae are flirting with the 8.192 maximum frontier so i'm replacing
all long-letter functions with two letter short versions, like: ucase (in dutch hoofdletters) => HL that way i'm reducing
the number of characters in my formulae... that's the only reason, apart from learning more in vba !!!

 

[Joe4]

You should be aware that replacing native Excel and VBA functions with User Defined Functions has its drawbacks.
User Defined Functions are not volatile, by default, unless your designate them to be.
That means that they may not update automatically as the values that use change.

my formulae are flirting with the 8.192 maximum frontier

I am not quite sure what that means. If you are hitting any sort of Excel limits, that is usually a good indication that you may have too much (of whatever it is) in a workbook, and your performance may lag. That may also be a red flag that you are not using the best tool for the job.

But back to your question, note that the DateDiff function is looking for Dates, as the first two arguments, not Ranges.

DateDiff function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

 

[littlepete]

hello

that limit is due to the long words always coming back in my formulae...
i'm using all these possibilities just to learn vba... i started from nothing a year ago,
now i use long formulae; macros; udf, userforms and all kinds of variables and others...

i tried with an adjusted range to date as two variables, but still get the "error in value"...

can you tell me what dv = DateDiff(eerste, tweede, str) should be?

i'm guessing the third str can be written "y" or is that not true either?

 

[Joe4]

Are you trying to use this function in VBA or on your spreadsheet?
Can you show me an example of how you are trying to use it (so I can see what you are feeding into it for your parameters)?

 

[littlepete]

Are you trying to use this function in VBA or on your spreadsheet?
Can you show me an example of how you are trying to use it (so I can see what you are feeding into it for your parameters)?

i am in my formula replacing the function datumverschil (english: datediff) with my "dv"
i may hope you know how to use datediff, so dv will do exactly the same in the same way,
and i did explain the raison already...

=ALS(BY6="";"";

ALS(EN(DATUMVERSCHIL(BY6;NU();"y")<>0;
DATUMVERSCHIL(BY6;NU();"ym")<>0);
DATUMVERSCHIL(BY6;NU();"y")&" jaar en "&DATUMVERSCHIL(BY6;NU();"ym")&" maand ";"")

&ALS(EN(DATUMVERSCHIL(BY6;NU();"y")<>0;
DATUMVERSCHIL(BY6;NU();"ym")=0);
DATUMVERSCHIL(BY6;NU();"y")&" jaar ";"")

the formula looks at the date in cel by6 then tells me how long/how long ago it happened...
the formula is as i said much longer.

 

[Joe4]

Hmm... I don't think you quite answered my question.

When you create your own Functions in VBA, you can use those functions in both VBA and directly on the worksheet (as you would any other native Excel function).
I am just trying to understand where you are trying to use it.
The formulas you posted are not really valid formulas in either, as you have shown them.

Any formula directly on the worksheet would start with an "=".
And in using it in VBA, it would need to be set equal to a variable or a range value (or returned in a Message Box).
So I am not quite clear on exactly how you are trying to use this.
Please show me EXACTLY how and where you are trying to use it.

Also, in this part right here:

DATUMVERSCHIL(BY6;NU();"ym")=0)

what is "NU()"?

 

[littlepete]

Hmm... I don't think you quite answered my question.

When you create your own Functions in VBA, you can use those functions in both VBA and directly on the worksheet (as you would any other native Excel function).
I am just trying to understand where you are trying to use it.
The formulas you posted are not really valid formulas in either, as you have shown them.

Any formula directly on the worksheet would start with an "=".
And in using it in VBA, it would need to be set equal to a variable or a range value (or returned in a Message Box).
So I am not quite clear on exactly how you are trying to use this.
Please show me EXACTLY how and where you are trying to use it.

Also, in this part right here:

DATUMVERSCHIL(BY6;NU();"ym")=0)

what is "NU()"?

hello.

let me recommend to you google translate it will tell you : nu = now.
i also told you i'm using my udf in formula, you know what that is?
i also told you that the reason i'm making udf is because my formula is at its limits (8192 characters)
so you also know what i presented as code is only a very small part of it.
and because i 'm sure you know how a datediff function works,

i'm asking myself why you are asking ten million questions, while you perfectly well
just could tell me how to write an udf to replace the datediff function.

thank you so far for only taking my time and making me angry.
i will not ask you anything else. just read again all the decent answers i already gave.

 

[Joe4]

let me recommend to you google translate it will tell you : nu = now.

Don't make it hard for people to help you!
If you post it in the normal "Excel Questions" forum, then you should use the translate when posting the details. We shouldn't have to try to translate it to figure out what you are doing.
If you want to post it in your native language, you are welcome to do so, but then you should post it in the "Questions in Other Languages" forum.

I did not have very much time to investigate deeply into your question, it was the end of the day. I made an error in assuming that you were structuring the DATEDIFF function properly, but I went back and checked into that and it appears that you are not. The interval comes first, not last. See: MS Excel: How to use the DATEDIFF Function (VBA)

So I think your function should look like this:

Function dv(eerste As Date, tweede As Date, str As String)
    dv = DateDiff(str, eerste, tweede)
End Function

thank you so far for only taking my time and making me angry.
i will not ask you anything else. just read again all the decent answers i already gave.

I am sorry you feel that way. Since you do not seem to appreciate my help, I will steer clear of answering any of your questions going forward, and leave them for others.