Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / November 2007

Tip: Looking for answers? Try searching our database.

Round function evaluates to zero if bookmark is hidden text

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Thomas McLain - 06 Nov 2007 19:45 GMT
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 - 06 Nov 2007 21:22 GMT
> 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

Signature

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.

Thomas McLain - 06 Nov 2007 21:46 GMT
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.

> > I have a document template that uses the round function, which is
> > used to round the value of another bookmark.  When that bookmark is
[quoted text clipped - 22 lines]
>     MsgBox myVal
> End Sub
Jay Freedman - 07 Nov 2007 16:51 GMT
Since you posted in a VBA newsgroup, I think my assumption was justified.
Fortunately, macropod has your answer.

> 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
[quoted text clipped - 33 lines]
>> Email cannot be acknowledged; please post all follow-ups to the
>> newsgroup so all may benefit.
Thomas McLain - 07 Nov 2007 17:14 GMT
Yes, your assumption was justified.  I admit that I unknowingly admitted that
crucial information.  Thank you for your willingness to help.

> Since you posted in a VBA newsgroup, I think my assumption was justified.
> Fortunately, macropod has your answer.
[quoted text clipped - 36 lines]
> >> Email cannot be acknowledged; please post all follow-ups to the
> >> newsgroup so all may benefit.
macropod - 07 Nov 2007 07:24 GMT
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
Signature

macropod
[MVP - Microsoft Word]
-------------------------

>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?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.