Creating Functions From Formula

[Yongle]

During thread http://www.vbaexpress.com/forum/show...abet-in-a-cell
it suddenly struck me that of the myriad ways in VBA of achieving the same end result, one particularly helpful one is creating functions that can be used repeatedly across workbooks, thus avoiding using any macros whatsoever in many workbooks.

The first solution offerered in the above thread was to avoid VBA and use standard Excel functionality (albeit creating a rather terrifying formula in the eyes of a typical Excel user) :
=MID(A1,MIN(INDEX(SEARCH(CHAR(64+ROW($1:$26)),A1&"abcdefghijklmnopqrstuvwxy z"),0)),LEN(A1))
If faced with the same (or similar) problem again later, many users start from scratch re-writing the formula (and debugging their way through nested brackets etc!) - they probably deleted or cannot find that original spreadsheet with the formula in it.

My question:
Is there a foolproof way of quickly creating a function from any Excel formula, no matter how complicated?

[Bob Phillips]

No. A formula can consist of so many elements, each one would have to be accounted in the VBA, and each one might generate multiple VBA statements, or many might generate a single VBA statement. And a function in one formula might be handled in a certain way in VBA, but the same function in another formula could be handled a different way in VBA, context is everything.

[Yongle]

thanks for the full explanation @XLD

[pike]

Basic rule use excel functions first only use Vba if you can not solve with spread sheet functions

[Paul_Hossler]

The first solution offerered in the above thread was to avoid VBA and use standard Excel functionality (albeit creating a rather terrifying formula in the eyes of a typical Excel user) :
=MID(A1,MIN(INDEX(SEARCH(CHAR(64+ROW($1:$26)),A1&"abcdefghijklmnopqrstuvwxy z"),0)),LEN(A1))

Personal opinions ---

1. Using intrinsic Excel worksheet functions is faster in terms of performance - they're compiled and not interpreted

2. If there is potential for re-use I like to create a generalized user defined function and put it in Personal.xlsm or an add-in to distribute

3. I know I'm not very good with long, complicated, multi-level WS functions like that one, so many times I find it easier and faster to just create a VBA function instead of trying to test and debug a complicated WS formula. Six months later, I'm more likely to understand a commented logically laid out VBA function than a WS formula like that

4. Typical modern computers execute fast enough to normally make time differences imperceptible. I follow the rule of thumb that says something has to improve by 100% before it 'feels faster'

5. For me, most times rethinking the algorithm or using intrinsic Excel capabilities generates the greatest improvement

[snb]

To illustrate in this case how simple it is:

Sub M_snb()
    MsgBox [MID(A1,MIN(INDEX(SEARCH(CHAR(64+ROW($1:$26)),A1&"abcdefghijklmnopqrstuvwxy z"),0)),LEN(A1))]
End Sub

or

Sub M_snb()
    for j=1 to len([A1])
        if instr("abcdefghijklmnopqrstuvwxyz" ,mid(lcase([A1]),j,1)) then exit for
    next
    msgbox mid([A1],j)
End Sub

Do not believe what they tell you about speed: most of the time they quote others, who quote others, who quote others, without having tested the assertion in real life themselves. It's more about rumours than about Excel and/or VBA.

Crucial is the analysis of what you want to achieve, the structuring of the data, the reduction of redundancy, etc. The calculation in VBA can be much faster most of the time because it doesn't recalculate over and over again if no essential data have been changed.

[Yongle]

@snb - the speed of the various possibilities is of no concern - I am just thankful when my code runs without a hitch.
I have had a go (without any success) at transforming sub m_snb() into functions that could be used in any cell. How do I take that next step?
thanks

Update - I was not seeing very well or not looking properly - had left one set of square brackets in place. I get it now.
thanks

[Yongle]

3. I know I'm not very good with long, complicated, multi-level WS functions like that one, so many times I find it easier and faster to just create a VBA function instead of trying to test and debug a complicated WS formula. Six months later, I'm more likely to understand a commented logically laid out VBA function than a WS formula like that

@Paul_Hossler - 100% agreement from me - even a few days later I struggle to amend a complicated nested formula which seemed so clear and logical at the time.