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

[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]

@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.

[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!!

[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 am glad that you like it!

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

[afghanNazY]

Hi, i need to write a VBA code for adding up intergers, is there function that i can use?

Thanks

[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!

[redballyou]

How come the name manager recognized the function evaluate while the spreadsheet itself doesn't?

[ExcelIsFun]

It is just programed that way. The whole Excel 4 Macro Functions is just a left over from a long time ago.

However, as I showed in the video, you can insert a Macro sheet and use the function that way.

[robzrob]

I'm only just getting into VBA. Get into it, it's great - you haven't even BEGUN to appreciate Excel without it.

[ExcelIsFun]

You are 100% correct about VBA.

If you know VBA, anything is possible and the anything is usually easy to do.

If you only know non-VBA methods, most anything is possible, and the anything can sometimes be dramatically harder to do than the VBA comparable solution.

Mr Excel once said about Aladin (REALLY smart formula guy), "why does he need to use VBA, when he can do almost anything with a formula?"

But Mr Excel also said in this video, Yes, Learn VBA!

[planiolro]

I like your solution since VBA is unknown for me. This Excel 4 seems quite interesting since this is the second time (after counting cells with custom number formatting) when you use it. Are there any other useful forgotten functions in Excel 4? Making a video on this topic sounds reasonable.

[ExcelIsFun]

I only have one other video about Excel 4 Macro functions:

Excel Magic Trick 431: Excel 4 GET.CELL Macro Function

(as you mentioned)

I will make videos that use Excel 4 Macro functions as I learn more about them!

If you find a good source for these old functions, let me know.

[rfmajo]

Hi ExcelIsFun,

Where can I learn about those Excel 4 functions? Can you make a video about this topic?

Thanks.

[ExcelIsFun]

I only have one other video about Excel 4 Macro functions:

Excel Magic Trick 431: Excel 4 GET.CELL Macro Function

I do not know a good source for all the old Excel 4 Macro functions.

I will make videos that use Excel 4 Macro functions as I learn more about them!