Can a macro utilise a formula and apply it to ComboBox value in Word?

[DavG63]

Hi everyone

This is something that I was thinking about trying but before I did I wanted to just check with the experts to see whether it was actually possible.

If I have the following ComboBoxes:-

ComboBox1 = Start Date
ComboBox2 = End Date
ComboBox3 = Value
ComboBox4 = Interest Rate

Can I create a macro that would calculate the number of days between the values in ComboBox1 and ComboBox2, a la DATEDIF in Excel ("Result X")? Can the same macro then multiply the value in ComboBox3 by ComboBox4 and divide that result by 365, i.e. to get the daily rate of interest, finally then multiplying that figure by Result X to calculate the total applicable interest over the period?

Sorry if I'm not being particularly clear. As I say I haven't yet even attempted to make this work so it's all just a jumble of ideas in my head at the moment.

Thanks very much

Dav

[gmayor]

There is a DateDiff function in Word VBA if that's what you mean, and calculation of numeric values is also relatively simple. What type of combobox is involved?

[DavG63]

Hi Graham

Sorry if I'm not being particularly clear, I've used DATEDIF in Excel before but have never considered using it in Word before. This also may sound really stupid, but I wasn't aware that there was more than one type of ComboBox?!

Here's an example that I hope answers your question:-

ComboBox1 = "01/01/2015" (the format of the date should be (dd/mm/yyyy))
ComboBox2 = "01/02/2015" (the format of the date should be (dd/mm/yyyy))
ComboBox3 = 100.00
ComboBox4 = 8.0%

Based on that I would expect the DateDif to be 31 days.

ComboBox3 x ComboBox4 would = 8.00

8.00 would then be divided by 365, and multiplied by DateDif to give the result of 0.68.

I hope this clarifies but I suspect not!

By trial and error I've managed to piece together the following which displays the DateDif in a DocVariable (so I could check it worked, I wouldn't actually intend to display it anywhere on the finished document).



Dim StartDate As StringDim EndDate As String
Dim iNumDays As String


StartDate = Me.ComboBox1.Value
EndDate = Me.ComboBox2.Value


iNumDays = DateDiff("d", StartDate, EndDate)



Thanks

Dav

[gmayor]

There are userform combo-boxes, activeX combo-boxes and content control combo-boxes.:)

The following should point the way. I have added message boxes at each stage. Frankly I wouldn't use combo boxes for dates unless the dates are fixed in code as there is too much scope for error. Use a VBA date picker instead.


Dim iDiff As Single, iCalc As Single
Dim DateStart As Date, DateEnd As Date
Dim iVal As Single, iPercent As Single
DateStart = CDate(Me.ComboBox1.Value)
DateEnd = CDate(Me.ComboBox2.Value)
iVal = Me.ComboBox3.Value
'Remove % from the percentage
iPercent = Replace(Me.ComboBox4.Value, "%", "")
MsgBox iPercent

'Convert percentage to decimal
iPercent = 1 * iPercent / 100
MsgBox iPercent

'Calculate date difference
iDiff = DateDiff("d", DateStart, DateEnd)
MsgBox iDiff

'get final value and format to 2 decimal places
iCalc = Format(((iVal * iPercent) / 365) * iDiff, "0.00")
MsgBox iCalc

[DavG63]

Thanks Graham

I had no idea that there were so many variations on a ComboBox! The one I would use as a personal preference is the Userform ComboBox.

I'll plug your code into my template and see how it reacts.

Thanks very much for your help and Merry Christmas when it comes.

Dav