excel autocalculate result in status bar

[J_B_#]

Is it possible to access the Autocalculate result (shown in the status bar),
in Excel or VBA, in order to drop it into the clipboard for [paste special
value] pasting?

 

[PaulD]

: Is it possible to access the Autocalculate result (shown in the status
bar),
: in Excel or VBA, in order to drop it into the clipboard for [paste special
: value] pasting?

Check out this previous post
http://tinyurl.com/8xeoc
Paul D

 

[David McRitchie]

Hi Paul,
Thanks for the reference concerning the fact that filtering affects
the reported values for SUM, etc on the Status Bar, I have
updated my page accordingly. The macro shows all of the
values in one message box; whereas, you have to choose which
value you are interested in when viewing in Excel.

Macro to simulate StatusBar Reporting (#simulation)
http://www.mvps.org/dmcritchie/excel/statusbar.htm#simulation

 

[J_B_#]

Thanks guys.

Paul's solution is near the mark, but lacks choice of function, so would
require several buttons/macros.

David's solution doesn't address the need to drop a single value into the
clipboard.

Obviously one could write a macro to interrogate a combo/list button (not
that I have the skill), but that seems inelegant whilst that value must be
around in the guts of excel somewhere.

John B

 

[David McRitchie]

Maybe you'll have to modify a solution to get what you want, but you've
been shown how to get each of the values that you have a choice of
(plus a count of text values). Going through the clipboard itself is a
rather "inelegant" solution in itself, because no one but you is going
to know where those values came from.

 

[PaulD]

: Thanks guys.
:
: Paul's solution is near the mark, but lacks choice of function, so would
: require several buttons/macros.
:
: David's solution doesn't address the need to drop a single value into the
: clipboard.
:
: Obviously one could write a macro to interrogate a combo/list button (not
: that I have the skill), but that seems inelegant whilst that value must be
: around in the guts of excel somewhere.
:
: John B

Well, I wouldn't say the value 'must' be around since it's possible to store
things in class properties that are not publicly exposed. However, I did
note that this setting is saved since everytime you open Excel, it remembers
what setting you last used. I found that the following registry key (on my
computer) stores these settings.

HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel\Options

here are the values for each setting
Average = 5
Count = a9
Count Nums = 0
Max = 7
Min = 6
Sum = 4
None = ffffffff

Unfortunately, it appears excel writes this data only when you exit, so if
the user changed the setting while in excel, I don't know of any way to find
the value. If you wanted to know what the original value was at time of
opening, just read the registry and compare to the list. Not sure if this
helps but that's all I could find.
Paul D

 

[J_B_#]

Thanks Paul - that's one step nearer to understanding what is/is not available.

Appreciated

John