Help Please, MsgBox with Calculate

Cendrinne · #1 09-12-2022, 04:22 PM

Hello Pros,
This might be challenging, but I don't know if it's fixable.

I found a video on how to calculate from text or in a table.

It works wonderfully, not the formula function but more like from this link, to add a command called:

Calculate

Calculate
How to Perform Simple Calculations in Microsoft Word

The thing about it, is the it doesn't last long to show the results in the status bar, but it's accurate.

I was so proud of myself to have found a way to include a msgbox, like that it stays.

If I use the command/icon methode such as in the link above, I get the results accurately and the numbers are fully formatted.

*******Problem.
When I use the MsgBox which I've also created myself a personalized Word Tab and created my own Icon to calculate with the MsgBox and add such as these numbers as an example which I've tried In and Out of a table:
5,000,000
10,000,000
15,000,000

Select these 3 numbers, I click on the my Word VBA Icon = I get 3E+07 in the MsgBox.

Code:

     
'use this line below, only if in tables.
 Selection.SelectCell
     
    MsgBox Selection.Calculate

Question:
How to get a MsgBox + Fully Formatted either with or without symbols $% + Without an 3E+07 message?

Unless it's not doable?

Any insights in the problem I get, I would be soooooooooooooooo grateful.

If it's not doable, please let me know so I will stop searching.

Guessed · #2 09-12-2022, 05:21 PM

3E+07 means 3 followed by 7 zeros and is spoken as "three times ten to the power of seven".

If your totals are always whole numbers you can specify the formatting of the answer like this

Code:

MsgBox Format(Selection.Calculate,"#,##0")

or show two decimal places like this

Code:

MsgBox Format(Selection.Calculate,"#,##0.00")

Hint: You can experiment with number formatting in Excel to work out number formatting strings to use in this VBA.

Cendrinne · #3 09-12-2022, 05:34 PM

Quote:

Originally Posted by Guessed

3E+07 means 3 followed by 7 zeros and is spoken as "three times ten to the power of seven".

If your totals are always whole numbers you can specify the formatting of the answer like this

Code:

MsgBox Format(Selection.Calculate,"#,##0")

or show two decimal places like this

Code:

MsgBox Format(Selection.Calculate,"#,##0.00")

Hint: You can experiment with number formatting in Excel to work out number formatting strings to use in this VBA.

Makes sense, you are right.
Thank you for guiding me. This will help me alot.

But how come the status bar, knows which format to use, it's actually automatic. Why we need to format the numbers in a MsgBox???

Just curiuos, if you know the answer. I was often told that Word is dum, not smart. I find it's actually pretty smart to know which format to use.

Thanks again,

Cendrinne · #4 09-12-2022, 05:58 PM

Look at No 4 at this site:

HTML Code:

http://wordfaqs.ssbarnhill.com/ToolsCalculate.htm

You can paste from Ctrl-V.

So maybe there is a way, so have that displayed in that MsgBox???

Guessed · #5 09-12-2022, 07:45 PM

It appears that Word and Excel start using the "10 to the power of" formatting if the numbers have 8 digits or more. When the numbers are smaller than that you don't need to worry about it too much.

You can make the code more complex to let Word choose its own format when the numbers are less than 10,000,000 by using an If/Else in your code.

I don't know why the StatusBar display method treats the result differently.

Maybe you would find this works better for your scale of numbers
MsgBox CDbl(Selection.Calculate)

Cendrinne · #6 09-12-2022, 08:00 PM

Quote:

Originally Posted by Guessed

It appears that Word and Excel start using the "10 to the power of" formatting if the numbers have 8 digits or more. When the numbers are smaller than that you don't need to worry about it too much.

You can make the code more complex to let Word choose its own format when the numbers are less than 10,000,000 by using an If/Else in your code.

I don't know why the StatusBar display method treats the result differently.

Maybe you would find this works better for your scale of numbers
MsgBox CDbl(Selection.Calculate)

Interesting. OK, I'll stop for tonight, or else I will be too tired tomorrow.
Thank you thank you thank for the lesson.

Interesting topic

If I do find the answer one day, I'll let you know

Night my forum smart friend

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Is there any way to get MsgBox columns to line up?	Jennifer Murphy	Word VBA	3	03-01-2020 12:13 AM
Word VBA - Ignore MsgBox	crazzyapple	Word VBA	2	11-08-2018 12:06 AM
Problem with Msgbox in VBA	Puzzled Programmer	Excel Programming	6	09-06-2018 04:34 PM
msgbox when deleting cells	Mikedk64	Excel Programming	11	07-13-2017 07:14 AM
Make MsgBox appear...	Jamtart	PowerPoint	3	09-01-2012 08:21 AM