Macro to Skip Calcuation if Field is Blank?!

[Elan05]

Hello,
I have the following macro in my document to calculate a payment amount, based on the times per year the contract is invoiced. My macro is repeated for Year3 - Year5... I wanted to know if there is something that I can add to this so that if any years beyond the Year1 are not filled in (form field left blank) the macro won't try to calculate. Right now, if some of the form fields aren't filled in, I get an error message when I exit the fields that are empty.

Sub CalcPayable() 
  '
  ' CalcPayable Macro
 If ActiveDocument.FormFields("Cycle").Result = "Annually" Then 
     ActiveDocument.FormFields("Payable1").Result = ActiveDocument.FormFields("Year1").Result 
 ElseIf ActiveDocument.FormFields("Cycle").Result = "Semiannually" Then 
     ActiveDocument.FormFields("Payable1").Result = ActiveDocument.FormFields("Year1").Result / 2 
 ElseIf ActiveDocument.FormFields("Cycle").Result = "Quarterly" Then 
     ActiveDocument.FormFields("Payable1").Result = ActiveDocument.FormFields("Year1").Result / 4 
 ElseIf ActiveDocument.FormFields("Cycle").Result = "Bimonthly" Then 
     ActiveDocument.FormFields("Payable1").Result = ActiveDocument.FormFields("Year1").Result / 6 
 ElseIf ActiveDocument.FormFields("Cycle").Result = "Monthly" Then
     ActiveDocument.FormFields("Payable1").Result = ActiveDocument.FormFields("Year1").Result / 12
 ElseIf ActiveDocument.FormFields("Cycle").Result = "Triannually" Then 
     ActiveDocument.FormFields("Payable1").Result = ActiveDocument.FormFields("Year1").Result / 3 
 End If 
 
 If ActiveDocument.FormFields("Cycle").Result = "Annually" Then 
     ActiveDocument.FormFields("Payable2").Result = ActiveDocument.FormFields("Year2").Result 
 ElseIf ActiveDocument.FormFields("Cycle").Result = "Semiannually" Then 
     ActiveDocument.FormFields("Payable2").Result = ActiveDocument.FormFields("Year2").Result / 2 
 ElseIf ActiveDocument.FormFields("Cycle").Result = "Quarterly" Then 
     ActiveDocument.FormFields("Payable2").Result = ActiveDocument.FormFields("Year2").Result / 4 
 ElseIf ActiveDocument.FormFields("Cycle").Result = "Bimonthly" Then 
     ActiveDocument.FormFields("Payable2").Result = ActiveDocument.FormFields("Year2").Result / 6 
 ElseIf ActiveDocument.FormFields("Cycle").Result = "Monthly" Then 
     ActiveDocument.FormFields("Payable2").Result = ActiveDocument.FormFields("Year2").Result / 12 
 ElseIf ActiveDocument.FormFields("Cycle").Result = "Triannually" Then 
     ActiveDocument.FormFields("Payable2").Result = ActiveDocument.FormFields("Year2").Result / 3 
 End If 
End Sub

[fumei]

1. use formfield objects (docFF in code below)
2. use Select Case (for the cycle type) and then a test of Year (not = 0)

Option Explicit

 
Sub CalcPayable()
'
' CalcPayable Macro
Dim docFF As FormFields
Set docFF = docFF()
 
Select Case docFF("Cycle").Result
Case "Annually"
If docFF("Year1").Result <> 0 Then
docFF("Payable1").Result = docFF("Year1").Result
If docFF("Year2").Result <> 0 Then
docFF("Payable2").Result = docFF("Year2").Result
If docFF("Year3").Result <> 0 Then
docFF("Payable3").Result = docFF("Year3").Result
' same for year 4 and 5
Case "Semiannually"
If docFF("Year1").Result <> 0 Then
docFF("Payable1").Result = docFF("Year1").Result / 2
 
If docFF("Year2").Result <> 0 Then
docFF("Payable2").Result = docFF("Year2").Result / 2
 
If docFF("Year3").Result <> 0 Then
docFF("Payable3").Result = docFF("Year3").Result / 2
' same for year 4 and 5
Case "Quarterly"
If docFF("Year1").Result <> 0 Then
docFF("Payable1").Result = docFF("Year1").Result / 4
 
If docFF("Year2").Result <> 0 Then
docFF("Payable2").Result = docFF("Year2").Result / 4
 
If docFF("Year3").Result <> 0 Then
docFF("Payable3").Result = docFF("Year3").Result / 4
' same for year 4 and 5
Case "Bimonthly"
If docFF("Year1").Result <> 0 Then
docFF("Payable1").Result = docFF("Year1").Result / 6
 
If docFF("Year2").Result <> 0 Then
docFF("Payable2").Result = docFF("Year2").Result / 6
 
If docFF("Year3").Result <> 0 Then
docFF("Payable3").Result = docFF("Year3").Result / 6
' same for year 4 and 5
Case "Monthly"
If docFF("Year1").Result <> 0 Then
docFF("Payable1").Result = docFF("Year1").Result / 12
 
If docFF("Year2").Result <> 0 Then
docFF("Payable2").Result = docFF("Year2").Result / 12
 
If docFF("Year3").Result <> 0 Then
docFF("Payable3").Result = docFF("Year3").Result / 12
' same for year 4 and 5
 
Case "Triannually"
If docFF("Year1").Result <> 0 Then
docFF("Payable1").Result = docFF("Year1").Result / 3
 
If docFF("Year2").Result <> 0 Then
docFF("Payable2").Result = docFF("Year2").Result / 3
 
If docFF("Year3").Result <> 0 Then
docFF("Payable3").Result = docFF("Year3").Result / 3
' same for year 4 and 5
 
End Select
End Sub

[macropod]

Personally, I'd forego the macro and use a series of formula fields.

[Elan05]

Fumei - Thank you for the detailed response. The "docFF" is confusing me though. What am I referencing there?

Macropod - Are you suggesting using an IF formula in those fields, similar to the idea of my original macro?

[macropod]

Originally Posted by Elan05

Macropod - Are you suggesting using an IF formula in those fields, similar to the idea of my original macro?

yes, that kind of thing is possible, but it makes little difference (the main one being whether macros are allowed to run) if your document already has other macros in it.

[Elan05]

My document does have other macros in it. Does that mean I can't use the calculation feature in the fields? I created the following calculation (made sure it worked in Excel before transferring over to Word).

=IF(Cycle="Annually", "Year1"/1, IF(Cycle ="Semiannually", "Year1"/2, IF(Cycle ="Quarterly", "Year1"/4, IF(Cycle ="Monthly", "Year1"/12, IF(Cycle ="Bimonthly", "Year1"/6, IF(Cycle ="Triannually", "Year1"/3,0))))))
When I protected the document and tabbed through the fields, it skipped over the field with the formula and didn't calculate anything. Also, the default of the form field with the calculation was showing "$t:00" Which is another issue... I can't have anything showing as a default because if there isn't anything entered in the fields, I want the printed document to look like those fields don't exist.

Going back to the macro by Fumei - does "docFF" need to be changed to ActiveDocument.FormFields? since that is what I am referencing?

[macropod]

Originally Posted by Elan05

My document does have other macros in it. Does that mean I can't use the calculation feature in the fields?

Not at all. It's just that, once you've established a requirement to use macros for one part of the exercise, you may as well use them wherever else they're suitable, rather than presenting whoever has to maintain the document later on with a mix of macros and field calculations.

I created the following calculation (made sure it worked in Excel before transferring over to Word).

=IF(Cycle="Annually", "Year1"/1, IF(Cycle ="Semiannually", "Year1"/2, IF(Cycle ="Quarterly", "Year1"/4, IF(Cycle ="Monthly", "Year1"/12, IF(Cycle ="Bimonthly", "Year1"/6, IF(Cycle ="Triannually", "Year1"/3,0))))))
When I protected the document and tabbed through the fields, it skipped over the field with the formula and didn't calculate anything.

The field formula syntax in Word is not the same as in Excel. To see how to do a wide range of calculations in Word, check out my Microsoft Word Field Maths Tutorial, at:
http://windowssecrets.com/forums/sho...l=1#post320143
or
http://www.gmayor.com/downloads.htm#Third_party

[fumei]

Ooops! My bad. Yes docFF should refers to all the formfields in the document.

Dim docFF as FormFields ' declares docFF as the formfield collection
Set docFF = ActiveDocument.Formfields() ' sets the object

Sorry about that. I am surprised Paul did not call me on that. I am even more surprised I missed it! Doh.

If you do go the field calculation route you are strongly advised to to listen to Paul and check out his Word Field Maths Tutorial. It is a few years old, but is still relevant and is still the very best learning source out there.

[macropod]

Originally Posted by fumei

Sorry about that. I am surprised Paul did not call me on that. I am even more surprised I missed it! Doh.

I didn't call you on that because I haven't been monitoring this thread closely. Given your level of competence, I'm not inclined to check your work. Of course, as you know from our discussion at VBAX, I too make mistakes sometimes ...

[fumei]

That is OK. My tongue was firmly planted in my cheek. Ummmm, hopefully that expression works well (as intended) in Oz.