This may seem strange, but I code in VBA but never created complex
functions on a worksheet itself. Now it's driving me crazy trying to
embed functions in functions.

Is there some sort of trick there? Outside in? Inside out?

Or some kind of editing tool?

Thanks.

SteveM

Perhaps you could help us understand your question.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"SteveM" <(E-Mail Removed)> wrote in message
news:87fab83d-510a-406f-8a56-(E-Mail Removed)...
> This may seem strange, but I code in VBA but never created complex
> functions on a worksheet itself. Now it's driving me crazy trying to
> embed functions in functions.
>
> Is there some sort of trick there? Outside in? Inside out?
>
> Or some kind of editing tool?
>
> Thanks.
>
> SteveM

Calling a function from a worksheet is simple. On the worksheet
=Myfunction(A1,B2,C3,D4:E7)

VBA
Function Myfunction(Num as Integer, Data as String, NewDate as Date, Target
as Range)

for each cell in Target
'your code
next cell
end Function
"SteveM" wrote:

> This may seem strange, but I code in VBA but never created complex
> functions on a worksheet itself. Now it's driving me crazy trying to
> embed functions in functions.
>
> Is there some sort of trick there? Outside in? Inside out?
>
> Or some kind of editing tool?
>
> Thanks.
>
> SteveM
>

On Nov 20, 9:28 am, SteveM <sbm...@vzavenue.net> wrote:
> This may seem strange, but I code in VBA but never created complex
> functions on a worksheet itself. Now it's driving me crazy trying to
> embed functions in functions.
>
> Is there some sort of trick there? Outside in? Inside out?
>
> Or some kind of editing tool?
>
> Thanks.
>
> SteveM

Sure, I mean trying to stuff multiple functions inside the formula
bar. e.g, INDEX(IF(MAX(MIN(BLAH(BLAH(BLAH

I know what the functions mean, it's the mechanics of building the
formulas that are frustrating.

SteveM

Interesting way round to come at Excel but it happens...

Inside out really. Here's an example of a nested If function (tends to
shock and amaze programmers!). Stick it in C2 and experiment with colours (a
really useful tool is Tools, Formula Auditing, Evaluate Formula):

=IF(AND(A2="Red",B2="Yellow"),"Orange",IF(OR(A2="Scarlet",B2="Crimson"),"Red",IF(AND(OR(A2="Blue",A2="Navy"),B2="Yellow"),"Green","Some other colour")))

The Ands and Ors are read outwards before the Ifs are evaluated [syntax of IF:
IF(condition, if true, if false)]

Hope that helps in some way!

"SteveM" wrote:

> This may seem strange, but I code in VBA but never created complex
> functions on a worksheet itself. Now it's driving me crazy trying to
> embed functions in functions.
>
> Is there some sort of trick there? Outside in? Inside out?
>
> Or some kind of editing tool?
>
> Thanks.
>
> SteveM
>

The way I do it is by creating smaller formulas using multiple cells,
then replacing the references in previous cells with the later
functions. But beware of the megaformula, it is very hard to debug.

HTH,
JP


On Nov 20, 9:44 am, SteveM <sbm...@vzavenue.net> wrote:
> On Nov 20, 9:28 am, SteveM <sbm...@vzavenue.net> wrote:
>
> > This may seem strange, but I code in VBA but never created complex
> > functions on a worksheet itself. Now it's driving me crazy trying to
> > embed functions in functions.
>
> > Is there some sort of trick there? Outside in? Inside out?
>
> > Or some kind of editing tool?
>
> > Thanks.
>
> > SteveM
>
> Sure, I mean trying to stuff multiple functions inside the formula
> bar. e.g, INDEX(IF(MAX(MIN(BLAH(BLAH(BLAH
>
> I know what the functions mean, it's the mechanics of building the
> formulas that are frustrating.
>
> SteveM

Hey check out this page, it has a tutorial on building megaformulas.

http://j-walk.com/ss/excel/usertips/tip079.htm


HTH,
JP

On Nov 20, 9:44 am, SteveM <sbm...@vzavenue.net> wrote:
> On Nov 20, 9:28 am, SteveM <sbm...@vzavenue.net> wrote:
>
> > This may seem strange, but I code in VBA but never created complex
> > functions on a worksheet itself. Now it's driving me crazy trying to
> > embed functions in functions.
>
> > Is there some sort of trick there? Outside in? Inside out?
>
> > Or some kind of editing tool?
>
> > Thanks.
>
> > SteveM
>
> Sure, I mean trying to stuff multiple functions inside the formula
> bar. e.g, INDEX(IF(MAX(MIN(BLAH(BLAH(BLAH
>
> I know what the functions mean, it's the mechanics of building the
> formulas that are frustrating.
>
> SteveM

As soon as a worksheet function become complicated, I resort to VBA. I find
it very frustrating to debug worksheet functions that have multiple IF
stement.

It is much easy to document and debug a VBA function then a worksheet
function.

When I have to create a complex worksheet function, I do it in small pieces
(sometimes using multiple cells). Then when i get the peice working I
combine them into a larger function. I also use the worksheet menu Tools -
Formula Auditing - Evaluate Formula to help debug the formulas.

"SteveM" wrote:

> On Nov 20, 9:28 am, SteveM <sbm...@vzavenue.net> wrote:
> > This may seem strange, but I code in VBA but never created complex
> > functions on a worksheet itself. Now it's driving me crazy trying to
> > embed functions in functions.
> >
> > Is there some sort of trick there? Outside in? Inside out?
> >
> > Or some kind of editing tool?
> >
> > Thanks.
> >
> > SteveM
>
> Sure, I mean trying to stuff multiple functions inside the formula
> bar. e.g, INDEX(IF(MAX(MIN(BLAH(BLAH(BLAH
>
> I know what the functions mean, it's the mechanics of building the
> formulas that are frustrating.
>
> SteveM
>

Isn't the code below easier to understand and debug and comment???

Function (Cell1 as Range, Cell2 as Range)
IF (Cell ="Red" and Cell2 ="Yellow") then
Mycolor = "Orange"
else
IF (cell1 ="Scarlet" or Cell2="Crimson") then
Mycolor = "Red"
else
IF (cell1="Blue" or cell1 ="Navy") and (cell2 ="Yellow") then
Mycolor = "Green"
else
Mycolor = "Some other color" 'I'm from the USA, don't spell colour
with a U
end if
end if
end if

end function


"Smallweed" wrote:

> Interesting way round to come at Excel but it happens...
>
> Inside out really. Here's an example of a nested If function (tends to
> shock and amaze programmers!). Stick it in C2 and experiment with colours (a
> really useful tool is Tools, Formula Auditing, Evaluate Formula):
>
> =IF(AND(A2="Red",B2="Yellow"),"Orange",IF(OR(A2="Scarlet",B2="Crimson"),"Red",IF(AND(OR(A2="Blue",A2="Navy"),B2="Yellow"),"Green","Some other colour")))
>
> The Ands and Ors are read outwards before the Ifs are evaluated [syntax of IF:
> IF(condition, if true, if false)]
>
> Hope that helps in some way!
>
> "SteveM" wrote:
>
> > This may seem strange, but I code in VBA but never created complex
> > functions on a worksheet itself. Now it's driving me crazy trying to
> > embed functions in functions.
> >
> > Is there some sort of trick there? Outside in? Inside out?
> >
> > Or some kind of editing tool?
> >
> > Thanks.
> >
> > SteveM
> >

On Nov 20, 9:53 am, Joel <J...@discussions.microsoft.com> wrote:
> As soon as a worksheet function become complicated, I resort to VBA. I find
> it very frustrating to debug worksheet functions that have multiple IF
> stement.
>
> It is much easy to document and debug a VBA function then a worksheet
> function.
>
> When I have to create a complex worksheet function, I do it in small pieces
> (sometimes using multiple cells). Then when i get the peice working I
> combine them into a larger function. I also use the worksheet menu Tools -
> Formula Auditing - Evaluate Formula to help debug the formulas.
>
> "SteveM" wrote:
> > On Nov 20, 9:28 am, SteveM <sbm...@vzavenue.net> wrote:
> > > This may seem strange, but I code in VBA but never created complex
> > > functions on a worksheet itself. Now it's driving me crazy trying to
> > > embed functions in functions.
>
> > > Is there some sort of trick there? Outside in? Inside out?
>
> > > Or some kind of editing tool?
>
> > > Thanks.
>
> > > SteveM
>
> > Sure, I mean trying to stuff multiple functions inside the formula
> > bar. e.g, INDEX(IF(MAX(MIN(BLAH(BLAH(BLAH
>
> > I know what the functions mean, it's the mechanics of building the
> > formulas that are frustrating.
>
> > SteveM

Thanks to you all for the advice.

I'll probably rely on the VB code approach.

I'm running 2003, so don't know what's in the current version. But
I'm surprised they have not built in a formula editor like the Query
editor in Access. But then again it's MS, so I'm not surprised.

SteveM