Calculation from results of form fields in VBA

[David Sampson]

I have stumbled (literally) on a solution for calculating the result of a formfield using the results of 2 others:

If aDocForm("SupInfBox").Result = "inf" Then
aDocForm("IsoSuggest").Result = ((aDocForm("LLWireSetup").Result - aDocForm("ULWireSetup").Result) / 2) + aDocForm("ULWireSetup").Result
End If

and it works.

However, when I change simply the + to a -

If aDocForm("SupInfBox").Result = "sup" Then
aDocForm("IsoSuggest").Result = ((aDocForm("LLWireSetup").Result + aDocForm("ULWireSetup").Result) / 2) - aDocForm("ULWireSetup").Result
End If

I get the error

"Type Mismatch" related to the second expression.

Can anyone tell me why this doesn't work?

Thanks
David Sampson


Submitted via EggHeadCafe - Software Developer Portal of Choice
ADO SHAPE Command - Producing Hierarchical Recordsets
http://www.eggheadcafe.com/tutorial...36-f10d970a8758/ado-shape-command--produ.aspx

 

[Jay Freedman]

I'm not certain of this, but I think you may find you get that error
for some combinations of data in the fields and not for others. The
underlying reason is that you're relying on VBA to do implicit type
conversions, which is an inherently risky practice.

The .Result property of a formfield object is defined as being of
String data type. When you use the operators +, -, and / to do
arithmetic, you're asking VBA to convert the String values to a
numeric data type (probably Single). Then the = sign asks VBA to
convert the numeric value back to a String so it can be stored in the
..Result of another formfield.

A lot can go wrong in those implicit conversions -- one or more of the
inputs may contain nonnumeric characters, values you expect to be
positive are actually negative, and so on.

It's much safer to do all the data type conversions explicitly, and
also to check for possible error-makers (such as nonnumeric input).
Use the Val function to convert from String to Single, and use the
Format function to convert from Single to String. If you use variables
to hold intermediate results, use Dim statements to declare them
(otherwise VBA uses Variant data type, which can hide bugs in your
logic).

So I suggest something like the following:

Dim aDocForm As FormFields
Dim LLW As Single, ULW As Single
Dim temp As Single

Set aDocForm = ActiveDocument.FormFields

If aDocForm("SupInfBox").Result = "inf" Then
LLW = Val(aDocForm("LLWireSetup").Result)
ULW = Val(aDocForm("ULWireSetup").Result)

' Val() returns 0 if input is nonnumeric;
' you may need to check for this

temp = ((LLW + ULW) / 2) - ULW
aDocForm("IsoSuggest").Result = Format(temp, "0.00")
End If

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

 

[Peter Jamieson]

Just to back up your message, "+" is also a string concatenation
operator, e.g. try

Sub plus()
Dim s As String
Dim t As String
s = "123"
t = "456"
Debug.Print t + s
Debug.Print t - s
End Sub

Peter Jamieson

http://tips.pjmsn.me.uk

 

[Fumei2 via OfficeKB.com]

And thus Jay's warning that it is better to use explicit terms. It is ALWAYS
better to be explicit.

Debug.Print t + s 456123 appended as a STRING
Debug.Print t - s 333 calculated as numbers converted from...and back
to...a string

Just to back up your message, "+" is also a string concatenation
operator, e.g. try

Sub plus()
Dim s As String
Dim t As String
s = "123"
t = "456"
Debug.Print t + s
Debug.Print t - s
End Sub

Peter Jamieson

http://tips.pjmsn.me.uk

I'm not certain of this, but I think you may find you get that error
for some combinations of data in the fields and not for others. The

[quoted text clipped - 72 lines]

 

[Peter Jamieson]

BTW, if want to promote the notion of "explicit", may I suggest that you
put a really good reference to a definition of "explicit" as it applies
to programming in your messages/sig etc. I suspect many of the people
who read your messages
a. don't really know what you mean and
b. might a good definition, and perhaps explanation of the issues
involved, very useful.

Best regards,

Peter Jamieson

http://tips.pjmsn.me.uk

And thus Jay's warning that it is better to use explicit terms. It is ALWAYS
better to be explicit.

Debug.Print t + s 456123 appended as a STRING
Debug.Print t - s 333 calculated as numbers converted from...and back
to...a string

Just to back up your message, "+" is also a string concatenation
operator, e.g. try

Sub plus()
Dim s As String
Dim t As String
s = "123"
t = "456"
Debug.Print t + s
Debug.Print t - s
End Sub

Peter Jamieson

http://tips.pjmsn.me.uk

I'm not certain of this, but I think you may find you get that error
for some combinations of data in the fields and not for others. The

[quoted text clipped - 72 lines]

ADO SHAPE Command - Producing Hierarchical Recordsets
http://www.eggheadcafe.com/tutorial...36-f10d970a8758/ado-shape-command--produ.aspx

 

[Peter Jamieson]

b. might a good definition, and perhaps explanation of the issues

involved, very useful.

i.e. might find a good definition... etc.

Peter Jamieson

http://tips.pjmsn.me.uk

BTW, if want to promote the notion of "explicit", may I suggest that you
put a really good reference to a definition of "explicit" as it applies
to programming in your messages/sig etc. I suspect many of the people
who read your messages
a. don't really know what you mean and
b. might a good definition, and perhaps explanation of the issues
involved, very useful.

Best regards,

Peter Jamieson

http://tips.pjmsn.me.uk

And thus Jay's warning that it is better to use explicit terms. It is
ALWAYS
better to be explicit.

Debug.Print t + s 456123 appended as a STRING
Debug.Print t - s 333 calculated as numbers converted from...and back
to...a string

Just to back up your message, "+" is also a string concatenation
operator, e.g. try

Sub plus()
Dim s As String
Dim t As String
s = "123"
t = "456"
Debug.Print t + s
Debug.Print t - s
End Sub

Peter Jamieson

http://tips.pjmsn.me.uk

I'm not certain of this, but I think you may find you get that error
for some combinations of data in the fields and not for others. The
[quoted text clipped - 72 lines]
ADO SHAPE Command - Producing Hierarchical Recordsets
http://www.eggheadcafe.com/tutorial...36-f10d970a8758/ado-shape-command--produ.aspx