Convert Excel Functions to VBA Macro Function

[Simon Corner]

I regularly write long nested Excel functions, is there an easy way to
convert a function that I have made work in Excel into a custom
function without having to rewrite the whole thing into VBA?

Here is an example function:

=IF(LEFT($A6)=TEXT(O$3,0),REPLACE(REPLACE(IF(LEFT($A6)=TEXT(O$3,0),REPLACE($A6,1,1,""),""),1,FIND("
- ",IF(LEFT($A6)=TEXT(O$3,0),REPLACE($A6,1,1,""),""))+2,""),FIND(" -
",REPLACE(IF(LEFT($A6)=TEXT(O$3,0),REPLACE($A6,1,1,""),""),1,FIND(" -
",IF(LEFT($A6)=TEXT(O$3,0),REPLACE($A6,1,1,""),""))+2,"")),1000,""),"")

Any help would be greatly appreciated.

Simon Corner

 

[Frank Kabel]

Hi Simon
there's no easy way. You have to rewrite this in VBA depending on your
requirements. You may post what you want to achieve.

 

[Simon Corner]

Thanks Frank,

The company I work for outputs specifications from a database, I'd like
to break out chunks of text from the string. e.g.

1......STPTLA-0091 - Strap Toggle Assembly with Locking Clevis Pin -
0091 (MANUFACTURED)1(1)

All I need is the part number STPTLA-0091 in one cell and the
description, Strap Toggle Assembly with Locking Clevis Pin, in another.
I can do this useing a nested funtion in Excel but it's very cumbersome.

Regards,

Simon Corner

 

[Bob Phillips]

Simon,

It can be done, basically by breaking it down into its component parts, but
if you don't want to 'rewrite the whole thing into VBA', why bother? What is
the problem with the formula that makes you think you need to do something?

The obvious thing 'wrong' is the complexity. One way to reduce this is to
put parts into another cell. For instance, if the formula is in H1, you
could break it down so
H2: =REPLACE($A6,1,1,"")
H3: =IF(LEFT($A6)=TEXT(O$3,0),H2,"")

H1 can then be simplified to

=IF(LEFT($A6)=TEXT(O$3,0),REPLACE(REPLACE(H3,1,FIND("-
",H3)+2,""),FIND(" -",REPLACE(H3,1,FIND(" -",H3)+2,"")),1000,""),"")

You could even put this in workbook names, making it off-worksheet, and
given meanigful names.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)