Error in VB calculate field

[Patricio Hidalgo]

Hi,

I have a formfield and need calculate another field with the value of two
fields. When did the routine, I have an error and when checked the value in
VB they are empty. What I am doing wrong?

Sub salife()

sa_life_e = Val(sa_life) / Val(tasa)

End Sub

Thanks for your help

 

[Jay Freedman]

Hi,

I have a formfield and need calculate another field with the value of
two fields. When did the routine, I have an error and when checked
the value in VB they are empty. What I am doing wrong?

Sub salife()

sa_life_e = Val(sa_life) / Val(tasa)

End Sub

Thanks for your help

If the names sa_life_e, sa_life, and tasa are the names of form fields in
the document, then you must use those names as indexes into the
ActiveDocument.FormFields collection to get the values of the fields. The
way you've written the macro, VBA assumes that they're names of undeclared
variables, so it initializes each of them to zero (and then the "divide by
zero" error occurs).

Instead, write something like this:

Sub salife()

Dim sa_life_e_str As String
Dim sa_life_str As String
Dim tasa_str As String

Dim sa_life_e_val As Single
Dim sa_life_val As Single
Dim tasa_val As Single

' get the strings from the input fields
sa_life_str = ActiveDocument.FormFields("sa_life").Result
tasa_str = ActiveDocument.FormFields("tasa").Result

' verify that you won't try to divide by zero
If Val(tasa_str) <> 0 Then
' calculate the result from the values of the strings
sa_life_e_val = Val(sa_life_str) / Val(tasa_str)

' show only two decimal places
sa_life_e_str = Format(sa_life_e_val, "0.00")

' store the result in the third field
ActiveDocument.FormFields("sa_life_e").Result = sa_life_e_str
Else
' you may omit the next statement if you
' don't want to use it
MsgBox "The tasa field evaluates to 0."
End If
End Sub

Notice that I declared the value variables as Single data type, meaning that
they can have decimal parts. If you declare them as Integer or Long, any
decimal parts will be ignored.

Also notice that if Val(tasa_str) evaluates to zero, which can happen if the
field content is not a valid number, this version won't try to fill in the
field. There are various ways to handle this, by a message box or by putting
a warning in the field, etc.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[Patricio Hidalgo]

Hi Jay.

Thanks a lot, your suggestion is great.

Patricio

 

[macropod]

Hi Patricio,

If you set the formfields' properties to 'calculate on exit', you can use a formula field to do the calculation without the need for
vba:
{IF{tasa}<> 0 {=sa_lifetasa}}
and add a numeric picture switch to control the formatting, for example:
{IF{tasa}<> 0 {=sa_lifetasa \# ",0.00"}}

Note: The field brace pairs (ie '{ }') for the above examples are created via Ctrl-F9 - you can't simply type them or copy & paste
them from this message.