Display excel formula result in Msgbox

priddyboy

Board Regular
Joined
Feb 10, 2006
Messages
60
Is it possible to display the results of an excel formula in a message box without having the result stored anywhere? For example, I have several workbooks with multiple worksheets that I need to open and check the sum of 6 cells (these do not change between books or sheets). I am having to dig through someone elses workbooks that are not organized very well and everything is hardcoded. I tried something simple like this, but it doesn't work, obviously.

Dim str as string
Msgbox "str"
str = Sum(c49,c50,c52,d53,f9,g67)

Any help with the correct syntax would be appreciated.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
Good evening

Just a one liner will do :

Code:
MsgBox Application.WorksheetFunction.Sum(Range("C49,C50,C52,D53,F9,G67"))

Obviously, if you are using more than one sheet in your workbook, you may need to specify this within the range.

HTH

DominicB
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Also, this may not work for every Excel formula you have, as not every Excel function exists in Application.WorksheetFunctions. An alternative would be to use Evaluate:

Code:
MsgBox Evaluate("Sum(A1:A30)")

The argument to evaluate is a string, so you can build it up as required before passing it to the Evaluate method.

Best regards

Richard
 

priddyboy

Board Regular
Joined
Feb 10, 2006
Messages
60

ADVERTISEMENT

I think I've used something simliar in access. That's helpful as I wanted to round the value to zero decimals. Thanks again :LOL:
 

ronanbaker1

Board Regular
Joined
Nov 15, 2012
Messages
75
Hi Guys,

I'm looking to do something similar, Instead of Sum function just one cell - another.

The difference is I want Text to come first and then my answer.


So

MsgBox "Cells do not tie by..... Value .... "
 

titania

New Member
Joined
Jun 1, 2014
Messages
2
Also, this may not work for every Excel formula you have, as not every Excel function exists in Application.WorksheetFunctions. An alternative would be to use Evaluate:

Code:
MsgBox Evaluate("Sum(A1:A30)")

The argument to evaluate is a string, so you can build it up as required before passing it to the Evaluate method.

Best regards

Richard

I can't quite seem to adjust this to work for my needs i have a cell (w5) that displays a choose formula after pressing a form button. Ideally I would liketo then double click on that cell and have a different cell (w9) display data the results of a different formula (if)

Since that seems neigh impossible id like to double click on the cell and have a message box appear displaying the results of the if formula.

Example: if cell w5 displays "bunny" (after pressing form button) id like either:
1)double click w5 and have an =if(.... Formula display in w9
Or 2) double click w5 and have a msgbox display an =choose or =if formula

Any help would be so awesome!!
 

Forum statistics

Threads
1,136,430
Messages
5,675,794
Members
419,586
Latest member
RoteichA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top