Round function evaluates to zero if bookmark is hidden text

[Thomas McLain]

I have a document template that uses the round function, which is used to
round the value of another bookmark. When that bookmark is hidden text, the
round function evaluates to zero, which is incorrect. When the bookmark is
displayed, the round function evaluates properly. I need the bookmark to
remain hidden text, but I need the round function to work properly. How do I
do so?

 

[Jay Freedman]

I have a document template that uses the round function, which is
used to round the value of another bookmark. When that bookmark is
hidden text, the round function evaluates to zero, which is
incorrect. When the bookmark is displayed, the round function
evaluates properly. I need the bookmark to remain hidden text, but I
need the round function to work properly. How do I do so?

It isn't the Round function that's not working properly. If you're using the
..Range.Text of the bookmark and the text is hidden, the value of that
property is returned as the empty string, "". Passing that to the Val
function, explicitly or implicitly, returns the number 0. That's what is
being passed to the Round function.

The solution is to set the TextRetrievalMode.IncludeHiddenText property of
the range to True, so it sees the text regardless of whether it's hidden.
Here's a sample:

Sub demo()
Dim myVal As Single
Dim oRg As Range

Set oRg = ActiveDocument.Bookmarks("bk1").Range
oRg.TextRetrievalMode.IncludeHiddenText = True
myVal = Round(Val(oRg.Text), 2)
MsgBox myVal
End Sub

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

 

[Thomas McLain]

I should clarify. I'm using the round function as a formula in a field. i.e
{=round(Bookmark,-3) \# ,0;-,0}., not as part of a procedure.

 

[macropod]

Hi Thomas,

To reference the hidden bookmark's value, you need to code your field like:
{=ROUND({REF Bookmark \* Charformat}*1/6,-3) \# ,0;-,0}
By embedding a REF field in your formula with the '\* Charformat' switch, you bypass the bookmarked text's hidden attribute - just
make sure the 'R' in 'REF' isn't formatted as hidden.

Cheers

 

[Jay Freedman]

Since you posted in a VBA newsgroup, I think my assumption was justified.
Fortunately, macropod has your answer.

 

[Thomas McLain]

Yes, your assumption was justified. I admit that I unknowingly admitted that
crucial information. Thank you for your willingness to help.