Calculation from results of form fields in VBA
in [Word VBA]
|
From: David on 17 Jan 2010 18:57 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/tutorials/aspnet/2e5bf843-ae3d-4ec7-a036-f10d970a8758/ado-shape-command--produ.aspx
From: Jay Freedman on 17 Jan 2010 22:19 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 FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. On Sun, 17 Jan 2010 15:57:33 -0800, David Sampson wrote: >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/tutorials/aspnet/2e5bf843-ae3d-4ec7-a036-f10d970a8758/ado-shape-command--produ.aspx
From: Peter Jamieson on 18 Jan 2010 03:05 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 On 18/01/2010 03:19, Jay Freedman wrote: > 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 FAQ: http://word.mvps.org > Email cannot be acknowledged; please post all follow-ups to the > newsgroup so all may benefit. > > > On Sun, 17 Jan 2010 15:57:33 -0800, David Sampson wrote: > >> 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/tutorials/aspnet/2e5bf843-ae3d-4ec7-a036-f10d970a8758/ado-shape-command--produ.aspx
From: Fumei2 via OfficeKB.com on 18 Jan 2010 16:07 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 Peter Jamieson wrote: >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/tutorials/aspnet/2e5bf843-ae3d-4ec7-a036-f10d970a8758/ado-shape-command--produ.aspx -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/word-programming/201001/1
From: Peter Jamieson on 21 Jan 2010 13:04 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 On 18/01/2010 21:07, Fumei2 via OfficeKB.com wrote: > 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 > > > Peter Jamieson wrote: >> 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/tutorials/aspnet/2e5bf843-ae3d-4ec7-a036-f10d970a8758/ado-shape-command--produ.aspx >
|
Next
|
Last
Pages: 1 2 Prev: AutoOpen() doesn't run! Next: Macro Error Invalid Argument every other run |