Calulated form field when result is null and zero

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rockFish - 15 Feb 2005 01:10 GMT

My protected document contains three form fields with the bookmarked
names HIGH, LOW, and RANGE. Both the HIGH and LOW fields are manually
entered with numbers or are left blank (empty). The RANGE field is
calculated using the difference between HIGH and LOW.

The problem I'm having is trying to make the form field RANGE show a
"0" when the two bookmarked fields (HIGH and LOW) are calculated
*and* remain blank when the bookmarked fields are left blank.

Using the expression =HIGH-LOW \# "#;-#; " in the RANGE field options
only solves part of my problem. When HIGH and LOW are blank, RANGE is
blank. But if HIGH-LOW=0, RANGE still remains blank. I guess that's a
limitation of the numeric picture field switch as it interprets a
"blank" field as a zero. Is there anything using VBS that would
work correctly in this situation?

Signature

rich

Reply to this Message

Doug Robbins - 15 Feb 2005 01:34 GMT

Dim High As String, Low As String, result As String
If High = "" And Low = "" Then
result = ""
Else
result = High - Low
End If
MsgBox result

Signature

Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP

> My protected document contains three form fields with the bookmarked
> names HIGH, LOW, and RANGE. Both the HIGH and LOW fields are manually
[quoted text clipped - 11 lines]
> "blank" field as a zero. Is there anything using VBS that would
> work correctly in this situation?

Reply to this Message

Jezebel - 15 Feb 2005 01:56 GMT

The performance purists insist that you should test for empty strings using
"Len(str) = 0" -- hard to imagine a VBA app where it would make any
measurable difference, but strictly speaking, they're right.

> Dim High As String, Low As String, result As String
> If High = "" And Low = "" Then
[quoted text clipped - 19 lines]
>> "blank" field as a zero. Is there anything using VBS that would
>> work correctly in this situation?

Reply to this Message

Alex Ivanov - 15 Feb 2005 04:22 GMT

I heard this opinion many times, but always ignored it and never had time to
actually prove or disprove it myself.
Finally, I wrote very sophisticated test routine and found that performance
purists are right.
In one hundred million iteration loop len(str)=0 outperformed str="" by 34%.
That's whole 3 seconds difference!
Whether str has a value below or is an empty string does not seem to affect
performance in any case

Dim i As Long
Dim tm As Double
Dim str As String
str = "111111111111111111111111111"
tm = Timer
For i = 1 To 100000000
If Len(str) = 0 Then 'runs about 5.8 sec on my PC
'If str = "" Then ' runs 8.8 sec
'do nothing
End If
Next
Debug.Print "Execution Time: " & Timer - tm

Signature

Please reply to NG only. This email is not monitored.
Alex.

> The performance purists insist that you should test for empty strings
> using "Len(str) = 0" -- hard to imagine a VBA app where it would make any
[quoted text clipped - 23 lines]
>>> "blank" field as a zero. Is there anything using VBS that would
>>> work correctly in this situation?

Reply to this Message

Jezebel - 15 Feb 2005 04:59 GMT

As I said, I can't imagine an app where it would make a difference; although
bear in mind that the adage was originally uttered when 30Mhz was considered
a fast PC. Your current PC is probably 100 times faster than that ...

The reason it works whether or not the string is empty is that strings are
stored in two parts: pointer plus length, and content. Thus the length can
be checked without having to retrieve the string itself.

>I heard this opinion many times, but always ignored it and never had time
>to actually prove or disprove it myself.
[quoted text clipped - 46 lines]
>>>> "blank" field as a zero. Is there anything using VBS that would
>>>> work correctly in this situation?

Reply to this Message

rockFish - 15 Feb 2005 04:08 GMT

Thanks Doug. Seeing your code gave me an idea. This is what I used.

Sub GetResult()
If ActiveDocument.FormFields("High").result = "" Then
ActiveDocument.FormFields("Range").result = ""
Else: ActiveDocument.FormFields("Range").result = _
ActiveDocument.FormFields("High").result - _
ActiveDocument.FormFields("Low").result
End If
End Sub

--
rich

> Dim High As String, Low As String, result As String
> If High = "" And Low = "" Then
[quoted text clipped - 26 lines]
> > "blank" field as a zero. Is there anything using VBS that would
> > work correctly in this situation?

Reply to this Message

Jezebel - 15 Feb 2005 05:01 GMT

You might want to do the calculation with variables and a modicum of
error-checking, in case the user enters non-numeric values, or enters "high"
but not "low".

> Thanks Doug. Seeing your code gave me an idea. This is what I used.
>
[quoted text clipped - 47 lines]
>> > "blank" field as a zero. Is there anything using VBS that would
>> > work correctly in this situation?

Reply to this Message