Mr Excel & excelisfun Trick 37: Evalualte Text Math VBA, Excel 4 Macro Function, MoreFunc add-in?

See Mr Excel and excelisfun take math formulas stored as text and evaluate then using three methods: VBA, Excel 4 Macro Function or the MoreFunc add-in. See how to create a global universal relative Cell Reference in a Defined Name Formula.

Category:

Science & Technology

Tags:

• Excel
• Evalualte
• Text
• Math
• formulas
• VBA
• MoreFunc
• add-in
• Macro
• Function
• excelisfun
• Bill
• Mr
• Jelen
• Mike
• Gel
• Girvin
• Highline
• Community
• College
• global
• universal
• relative
• Cell
• Reference
• in
• Defined
• Name
• Formula

[rssd78]

Guys what can i say beautiful and so creative thinking. Thanks

[ExcelIsFun]

@rssd78 , cool, I am glad that you liked it!!

[DrSchille]

BTW

The Evaluate function is accessible through VBA. So if you need the EVAL function as mentioned in the video, without downloading the 66-function add-in, you can simply write a code such as this:

Function EVAL(myVal as string)

EVAL = Application.Evaluate(myVal)

End Function

Cheers

[ExcelIsFun]

@DrSchille , Cool! Thanks!

[DrSchille]

Hello!

Great VBA trick and functions.

Here's another way to do this:

In B1 write ="="&a1 and copy-down. Select all and paste-special and choose values.

Select all and use find & replace tool to replace "=" with "=" unless U do the replacing, excel won't calculate (at least in excel 2007). This isn't as dynamic as the eval-function and not as quick to repeat as a macro, but can at least do it without any extras or knowledge of VBA.

I would prefer to know VBA though.

[ExcelIsFun]

Nice alternative!!

[DrSchille]

@DrSchille I just figured out that the code doesn't work properly if you don't have an english version. It does a fair job, but you will get problems if the 'info' you are evaluating are functions, such as 'exp' or 'sqrt, which are diffent in other languages.

You'll need some extra lines in the VBA code, to translate them from 'FormulaLocal' to a 'english standard VBA-readable Formula'.

Right now I cannot think of how the code will look to get a 'function' that handles the issue.

[ExcelIsFun]

I am glad that you like it!

Excelishell? I disagree! I think that Excel is FUN!!!!!

[kelch12]

WOW,

EVALUATE is like the hidden function in excel. And that relative reference name is just brilliant! Love it. Like always, you give me wonder.

btw, there is a channel called "ExelIsHell" no relation there. An accidental find.

[ExcelIsFun]

I do not know how to do the VBA, but the formula is quite easy. If you have the numbers 2.2, 3.1, 5.9 in the range A1:A3, this formula will do it:

=SUM(INT(A1:A3)).

But you must enter the formula with the keystrokes:

Ctrl + Shift + Enter

because it is an array formula.

If you do not use Ctrl + Shift + Enter you will get the error: #VALUE!