Calulated form field when result is null and zero

[rockFish]

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?

 

[Doug Robbins]

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


--
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

 

[Jezebel]

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
result = ""
Else
result = High - Low
End If
MsgBox result


--
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

 

[rockFish]

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

 

[Alex Ivanov]

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


--
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
measurable difference, but strictly speaking, they're right.

 

[Jezebel]

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.

 

[Jezebel]

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".