Using DATEDIFF in a userform

[Greg]

Hi all,

My datediff problem persists. If I haven't explained myself very well in the past I'll try again.

I have a document in which a basic interest calculation is performed. The document contains an amount (the principal), a rate of interest, a start date, an end date, a daily rate and total interest. All of thes must be displayed in the document after the calculation is performed.

The dates must be in the format dd/mm/yyyy.

The principal, the rate and the start and end dates must all be entered via the userform and the document must be re-usable so that subsequent adjustments can be made as and when required (i.e. the bookmarks must not dissapear after the calculation has been performed).

As I am still a novice, I need assistance with just about everything.

Can anyone help me?

[Killian]

Hi Greg,

Since we're taclking "just about everything", can I suggest doing it one step at a time..?
First, I think, get the userform working independantly
Then set up the document structure/bookmarks and get the form working with the document content
I've attached a document with a simple userform that has start & end date textboxes, a button to fire a calculation and a label to show the result.
The calculation routine first checks the dates are valid then shows the difference between the dates.
So I guess we need to add an interest rate and add some more output variables to calculate...

[Greg]

Hi Killian and thank you very much. That works well.

In the next step I want the two dates to appear on my document after entering them through the Userform. I also need the result (in days) to appear on the document.

Finally, I need an entry for the interest rate which also has to appear on the document.

Gerry kindly provided the basis of a Userform for the entry of names, etc to autopopulate my document. I hope that Userform can be easily adapted to accept your datediff code. I'll try it and let you know.

In the meantime I would be grateful if you were to consider the other issues referred to above.

[Killian]

OK, here's a basic example showing reading and writing values between a userform and document bookmarks. It's simple enough... bookmarks get deleted (well, overwritten), so the trick is to set a range variable, update the range then re-insert the bookmark.

There is a more consise way of doing this which I'll add later

[Killian]

Rather than explicitly coding for each bookmark name and textbox, one option would be to associate each textbox with a bookmark name by entering the bookmark name as the textbox's "Tag" property at design time.
Then for each command, you can loop through the controls and update them and their associated bookmarks. Example attached...

[Greg]

Thanks Killian,

In my existing document I have inserted bookmarks for various names, etc and referred to those bookmarks in the Userform. For example, the bookmark "Amount" is referred to in the Userform label as txtAmount.

Can I assume that datStart and datEnd on your datediff form can also be labelled txtdatStart and txtdatEnd and that bookmarks of the same name can be inserted on my document to show those entries? If so, how do I get the number of days between the dates to appear on my document instead of in a messagebox?

Also, what do you suggest for copying your code into my existing form. Should it be in a separate module?

Once again, thanks for your help.

[fumei]

Greg, here is a later version. As has been mentioned, you have to break this into workable chunks. I gave you the previous version that clearly put the dates into the bookmarks. This one does as well. It also put the difference in dates into bookmarks as well. My calculation algorithm is likely way off, but I asked what the calculation was exactly. You never answered.

So take your chunks one at a time. Use either Killian's demo, or mine.
Also, what do you suggest for copying your code into my existing form. Should it be in a separate module?Copying code rarely works perfectly. You have to look at what you can use. Look at HOW things work. Then make your own to fit your requirements. Build it. If you have problems post it back here.

But move in workable chunks. As far as I can see - getting data from the userform into bookmarks is done. This has been demonstrated to you. The attached demos getting the days difference.

Now, how you get the dates into the userform can be a format issue, but if you put in the dates on my userform as month / day /year (06/23/2006 for example), then it works.

Again, as far as I can see the issue of getting information from the userform into bookmarks is resolved. There is a working method on the attached to get your days difference. I did a rough calculation of the daily rate - but again...I asked for details, and you did not answer.

[Greg]

Hi Gerry and thanks for your help once more. You offer a wonderful service. I hope I can contribute one day. In the meantime, I'll follow your advice and see what happens.

Greg.

[Greg]

Gerry, I have integrated your code into my document.

My document now displays (via bookmarks) the Amount, the Rate, the Start Date and the End Date. It doesn't display the daily rate (in dollars and cents) and I probably forgot to mention that I also need to show the total interest payable for the period in question.

Also, I need to show the dates in the format dd/mm/yyyy and I only need to show the daily interest to 2 decimal places.

Killian's code and yours are now both present in the macro but only yours seems to be active. I'm not sure what consequences (if any) will result from that. Certainly, Killian's method seems quite different.

Finally, what's the significance of the following?

' HUH????
'=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045

I recognise it as a formula I provided some time ago but I got the impression it was of no use in a proper datediff function.

Greg.

[fumei]

Sigh.....


I recognise it as a formula I provided some time ago but I got the impression it was of no use in a proper datediff function.As far as I can see it has nothing AT ALL to do with the datediff. What has it to do with DateDiff???? It looks like a calculation of the daily rate.
But then, you still have not answered this.

Sigh.... again.

To get the daily interest as two digit (ie. currency) use FormatCurrency.Call FillABookmark("DailyRate", (CLng(txtLoanAmount) * _
CLng(txtRate) / 100) / 365)
Since you did NOT tell me how to calculate the daily interest rate (even though I asked), I just did a straightforward divide by 365 - as you can see. As it is a Long integer, yes it goes on for a few digits. You can use FormatCurrency directly in the instruction lineCall FillABookmark("DailyRate", _
FormatCurrency((CLng(txtLoanAmount) * _
CLng(txtRate) / 100) / 365))OR, you can make the calculated amount a formatted variable, THEN pass it.Dim lngDaily As Long
lngDaily = FormatCurrency((CLng(txtLoanAmount) * _
CLng(txtRate) / 100) / 365))
Call FillABookmark("DailyRate", lngDaily)Note that FormatCurrency will use the regional settings for currency. If it is set for $#.##, then:

FormatCurrency(2.39560923) will come out $2.39 on my machine. It will add the "$" to the value. So if there is a $ in the text - which I think there is - take it out. You may also consider using Round - depending of course if you WANT to round things.
I probably forgot to mention that I also need to show the total interest payable for the period in question.That's nice. So? So what? Big deal. Calculate it and dump it into another bookmark. If you have all the values needed to calculate it - which you do - then...calculate it, and put it into a bookmark. You do not need a textbox on the userform, as it is a calculation. Use the numbers you have, and calculate it...and dump it into a bookmark.
I need to show the dates in the format dd/mm/yyyyWhat are your regional settings for dates? Normally, they will take over. Look up Format in Help.

[Greg]

Hi Gerry, despite all my attempts to include the necessary bookmarks, I cannot get the NumDays or DailyRate to appear on my document.

Greg.

[fumei]

Shrug...I give up.

1. They DO appear in the demo I attached. Take a look at them. My demo puts NumDays into the document. My demo puts DailyRate into the document. Take a look at them. Does it work in my demo...or not? if it does...figure it out and put it into yours. if it does not work...post the darn code! I am supposed to extrapolate what may be the problem from the vagueness of your post?

2. They are NOT any different than any other use of the bookmark filling Sub. If you got one use of the Sub to work...then it works doesn't it?

NumDays is just a number. It is calculated on the userform. If you HAVE a bookmark named NumDays - and I believe my demo does - then doing a call to the FillABookmark with the appropriate parameters - just like the others, and just like I posted in the last post, will work.

Call FillABookmark("NumDays", X) - where X is the number calculated for the number of days;

OR

Call FillABookmark("NumDays", formula) - where formular is the actual formula to do the calculation. Either way will work.
despite all my attempts to include the necessary bookmarkswhat the heck does that mean? You either HAVE put the bookmarks in...or not. There is no "attempt" really. You either put in the bookmarks, or you have not put in the bookmarks. And using FillABookmarks works if there is a bookmark.

You state things like "attempt" - but you give ZERO ZERO ZERO description of what is happening. "Attempt"?????

What? You got an error? You were allowed to put in a bookmark? You DID get the bookmark in, but got an error when you "attempted" to fill it?

You tell me nothing. You post is useless. It does not give even a hint of what DID happen. I can not read minds! If you do not SAY something I have no idea of what that something may be.

Does anything appear in the document? Part of something? Nothing at all? Errors messages? Just a dull emptiness?

Sorry, but you are on your own now. Maybe someone else can help you. I have tried. I really have. I ask for both specific questions, and also try to explain how to give better posts. Maybe someone else can read your mind, but I can not.

[Greg]

Well Gerry, I suppose I asked for that. I agree that your document does exactly what it should. My problems arose after I copied your code (I know I shouldn't) into another document. I am determined to make it work so I'll persist alone. Anyway, thanks very much for your help to date. I really appreciate it.

Greg.

[fumei]

That is crap Greg. Pure crap. You do NOT have to do this alone. I was more than willing to assist. I will repeat some things - and perhaps it may help with other posts you do.

1. If someone asks a specific question...answer it. Period. No ifs or buts. Answer it. Read their post, and answer their question. It is very very frustrating to ask (repeatedly) and have the question ignored. If you do not know the answer - that is perfectly fine, but SAY SO.

2. Do not post vagueness. "It doesn't work". "I can not get" are useless phrases. Tell us what IS happening. All error messages must be detailed. All steps you have taken must be detailed.

This stuff is not hard. It really is not. Sure, you can stumble around and get things to work. But the best way is to walk it in knowable chunks. Step-by-step.

Copying code, if it is generic and simple, can work. Often though it can't. It definitely can not unless the conditions in the other document are not precisely the same. Especially for code that uses named things like bookmarks.

Sigh. Post your document and I will take a look.

[Greg]

How do I attach a document to this thing?

[fumei]

Click the "Go Advanced" button. There is a Manage Attachment area. You can browse to your file and "open" it. It does not really open it. It "opens" it to the upload manager here. Then click the upload button.

There is a file size limit. It is better to ZIP your files.

[Greg]

Here it is (I think).

I have managed to get things working nicely but would like to incorporate more messages in the event of incorrect entries. My efforts at this were not very successful.

Greg.

[fumei]

"incorrect entries" - yes, you are finding out what is the REAL work behind coding applications. Coding itself is - most of the time - the LEAST of the work. Good error trapping is the most. Done correctly you will find that coding for errors will likely be up to 10 times the amount of code for everything else.

I can't really help you too much here. YOU have to determine the logic. I put one of the basics in my first demo. Using IsNumeric to check if an input is in fact numeric. If you think about it, it can't stop there. It may be numeric - but what if it is an unacceptable number?

Write out - precisely, and I really really mean precisely - everything that could be a bad input. Then step-by-step figure out the logic to try and catch that error.
My efforts at this were not very successful.Greg.....I am telling you. These kinds of statements are useless, and annoying. Why bother telling us this? It tells us nothing. NOTHING.

I see no indication of attempts to do anything like this. So, if you did...what DID you do, and what DID (or did not) happen? What does "unsucessful" mean? Other than being a vaguely NON event.

Other comment - it is always polite to have the Tab movement on a userform move the cursor from field to field in a progession that makes sense. You use the TabIndex to do this. The first location is TabIndex 0, then 1, then 2 etc. That way the cursor will move logically through the form. Yours does not. However, a minor point and easily fixed.

So. What do you want me to do here?