Selection.Calculate does not work (last try) 2

[Toman]

Hi,
I'm sorry to repeat my question from March 2004 (thread707-811375). Nobody has answered it. I hope that new generation of skilled programmers will help me.

I was using following macro with Microsoft Word 8 on WIN 98 for a long time.

Sub MakeSum()

' mimics very useful behavior of F2 key on very old MSWord3 (for DOS)
' calculates arithmetic operations included in selection (maybe row or column selection)
' and puts the result into the clipboard

If Len(Selection.Text) > 1 Then
        ' MsgBox Selection.Text
        result = Selection.Calculate
        ' MsgBox "result: " + Str(result) + " .. put into the clipboard"
        StatusBar = "result: " + Str(result) + " .. put into the clipboard"
        Set MyData = New DataObject
        MyData.SetText result
        MyData.PutInClipboard
Else
        StatusBar = "Select the expression to count first !!"
    End If

End Sub

After switching to Word 2002 from XP Office collection macro stopped working.
Does someone know why?
Thank you very much
Toman

 

[TonyJollans]

My knowledge of Word doesn't go that far back but the command I think you are talking about still exists.

Select Tools > Customize
Click on "Keyboard..."
Under Categories (on the left) select "All Commands"
Scroll down the Commands list on the right and find "ToolsCalculate"
Assign the key of your choice.

I don't know why the code shouldn't work but it will need a reference to MS Forms for the DataObject type to be available.


Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[fumei]

Sub SelectionNumbers()
Dim oData As DataObject
Set oData = New DataObject
oData.SetText Selection.Calculate
oData.PutInClipboard
Application.StatusBar = "The Selection calculation = " & oData.GetText
End Sub

works for me. Although to simply display in the statusbar you certainly do not need to make the DataObject. I am assuming it is to be used elsewhere.

Just as a note to anyone not familiar with this, Selection.Calculate is handy for doing calculations in a table. Normally when Word does calculation in a table, if it finds a blank cell, it stops. So that:

row 1 34
row 2 blank
row 3 8
row 4 =SUM(ABOVE)

SUM returns 8.

Selection.Calculate keeps on going and ignores blank cells. Selecting the cellss containing 34, blank, and 8 will return the correct answer...being 42 of course.

.Calculate defaults to SUM. Add expressions (as text!) to change this. Selecting horizontally....

row 1 12 * 4 10 returns 58

row 2 5 23 / 10 returns 7.3

The second example shows the hierarchy of mathematical expressions. Even though the selection looks like 5 + 23 / 10, it is really (23 / 10) + 5.

There is NO error if text is found within the Selection.

row 2 5 bob / 10 returns 0.5

You can of course, also use a Range object, and do Range.Calculate.

Gerry

 

[Toman]

Hi Tony,
You are perfectly true, command ToolsCalculate still exist and does the same what I was always used to. Very pleasant news to me. Please, read perfect Gerry's explanation about Selection.Calculate, which stays behind the ToolsCalculate function.
Thank you and star for you.

Hi Gerry,
As you've noticed very well, I'm not very familiar with VBA programming. In fact - after Tony's recommendation - I don't need mentioned macro any more. Nevertheless I'd like to find out the correct coding in this situation. Your code returns me error message User define type not defined on line

Dim oData As DataObject

Any suggestions?
Thank you and star for brilliant Calculate function explanation for those not knowing it yet.
Tom.

 

[Toman]

Link for giving stars seems to be out of order. I'll try it tomorrow.
T.

 

[PHV]

Toman, when in VBE menu Tools -> References ...
tick the Microsoft Forms 2.0 Object Library

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Toman]

Yes, yes, Microsoft Forms 2.0 Object Library was not activated on my comp (even not among available references). Library found (C:\WINDOWS\system32\FM20.DLL), registred and macro works like Swiss watch now.

Stars delivered OK from MSIE. Voting not possible from Firefox 1.5.0.1

Thank you, gentlemen.
Toman

 

[fumei]

Actually, unless you need to, I am sure why you wanted the DataObject. You call the Selection.Calculate method and it returns a value. This can be used dynalically within your code, or saved to variable. I only included it as you had it your original post.

There are valid reasons for moving data through the Clipboard, but I am not sure if you have any here. In which case...ignore the DataObject. Forget about it.

Do remember though that the data type returned by Selection.Calculate is Single. If you have need for more accurate values, you had best think of something else. Like - not doing this in Word!

Gerry

 

[Toman]

Gerry,

I understand that Selection.Calculate method returns a value. But typically I need to place this value somewhere else in the document. How can I do it without clipboard and temporally data object, which is required by PutInClipboard method?

Tom.

 

[PHV]

You already have the value of Selection.Calculate stored in a variable named result, so where is the problem ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Toman]

Sorry, how do I put variable result into clipboard, Tom.

 

[PHV]

I need to place this value somewhere else in the document
Selection.TypeText Text:=result

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TonyJollans]

Hi Tom,

You have a variable called "result".

Assuming you have a Range variable (called "YourRange", say) set to the other place in your document all you need to do is something like:

YourRange.Text = result

Exactly what depends on how you are finding the other place in your document - is it a bookmark, perhaps?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[fumei]

What they are saying is you do NOT need to use the clipboard at all.

Selection.Calculate is an instruction that returns a value. So:

ActiveDocument.Bookmarks("Here").Range.Text = Selection.Calculate

would put the value into the bookmark "Here".

ActiveDocument.Formfields("myTextbox").Result = Selection.Calculate

would put the value into the formfield "myTextbox"

ActiveDocument.Tables(5).Cell(2,3).Range.Text = Selection.Calculate

would put the value into the cell (row2, column3) of the fifth table in the document.

etc etc etc.

The other point is that if Selection.Calculate is stored in a variable (eg. result) then it can be used now, or later. Selection.Calculate is of course an immediate kind of thing. It is the value of the CURRENT Selection.

Gerry

 

[Toman]

Hi Garry, thank you for your detailed explanation about placing Selection.Calculate somewhere in document. But I'm afraid there is some small misunderstanding.

they are saying you do NOT need to use the clipboard at all

But I say

I NEED and WANT to use the clipboard here

.

Macro I have written is to be used absolutely occasionally, not in certain projects or templates. It means that the place where Result is to be placed is unknown beforehand and has no bookmark or other kind of reference.
A small example:
[tt]
Dear parents,
I've bought
[COLOR=white blue]5[/color] apples and
[COLOR=white blue]7[/color] pears.
What about Aunt Mary? Now I have 12 (the number 12 was inserted here from clipboard after column selection of numbers 5,7 end executing macro which executes Selection.calculate and places result into the clipboard) pieces of fruit.
Your loving son Tom[/tt]

Thanks to you and all willing to help me. I am absolutely satisfied now. Macro works.
Your loving son Tom

 

[fumei]

Sorry...but you are wrong. You do NOT need the clipboard.

I've bought
5 apples and
7 pears.
What about Aunt Mary? Now I have 12 (the number 12 was inserted here from clipboard after column selection of numbers 5,7 end executing macro which executes Selection.calculate and places result into the clipboard) pieces of fruit.

Then PUT a bookmark at that location. It is much, much, much better to do that. How were you going to use the bookmark to insert into that location? However, you were going to do it, using a bookmark is better IMHO.

I will reiterate that you do not need to use the clipboard, at all. Go ahead, shoot me a scenario...but I really doubt you will come up with something that requires putting the value into the clipboard, then extracting it out of the clipboard.

Go ahead and use the clipboard if you want. But I do NOT believe that you NEED to use the clipboard.

Gerry

 

[TonyJollans]

Of course it is true that you do not *need* to use the clipboard - but the original question was about a macro which duplicated the action of Tools > Calculate.

Tools > Calculate, used in the UI, displays the result of the calculation in the status bar _and_ puts the result in the clipboard so that it can be pasted elsewhere in the document on an ad-hoc basis.

Given that the command exists and does what Tom wants, the macro is not needed and this discussion seems rather pointless.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[Toman]

Hi Garry, it's clear now that I like the clipboard and you don't so much. I have more reasons to use clipboard here, but to keep this thread readable to the others I'll describe the only one.

It took me five years or so to teach my wife to remember and use Ctrl+C and Ctrl+V.
Now instead of Ctrl+V I should teach her:
[ul]
[li]set cursor to place where the Result should be positioned and remember to do it before running a macro (else error message "Run-time error '5941')[/li]
[li]click on Insert[/li]
[li]click on more..[/li]
[li]click on Bookmark[/li]
[li]write (a proper) Bookmark name (else error message "Run-time error '5941'). Possible candidates are "here", "PlaceWhereToPutResult", "myBookmark", "result", "PutItHere". At this point my wife will finish, believe me. So do I, but I will help myself by opening source for macro and reading hardcoded name of bookmark on line

ActiveDocument.Bookmarks("Place").Range.Text = Result

[/li]
[li]click on Add[/li]
[li]select numbers[/li]
[li]fire a macro[/li]
[/ul]
Do you know why I must use a clipboard in my Macro with Selection.Calculate?
Bye, Tom.

 

[fumei]

Sheeesh....

Of course it is true that you do not *need* to use the clipboard - but the original question was about a macro which duplicated the action of Tools > Calculate.

Which is why my original response included the DataObject.

Although I do agree that the discussion is rather moot....

Toman - it is not at all that I dislike the clipboard. As I actually DID write in my post...it is fine to do so when there is a need to do so.

However, what I want to point out was, as described, taking a calculation of cells and placing that sum at a specific location does not require the clipboard. In fact, I will say it again, it is better if it is going to a specific location that there be no user action at all.

Your listing of what you would have to teach your wife is an red herring. You would not have to teach her to do that list, and I suspect you know that. For example she would never have to write in the bookmark.

In any case, I am glad we could help, and you can either use Tools > Calculate, or a macro that works for you.

Though....what does you wife use to fire the macro? Just kidding. It doesn't matter.

Gerry