Creating Complex Functions in the Worksheet Formula Bar - THIS ISNUTS!

[SteveM]

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

 

[Don Guillett]

Perhaps you could help us understand your question.

 

[Guest]

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]

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

 

[Guest]

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!

 

[JP]

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

 

[JP]

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

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


HTH,
JP

 

[Guest]

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.

 

[Guest]

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

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!

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

 

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

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

 

[JP]

In Excel there are often multiple ways to do the same thing, some
easier than others. But if you don't understand the way functions
work, how are you going to write a VBA equivalent?

--JP

 

[Mike Middleton]

SteveM -

I suggest "inside out" and using worksheet cells for the "editing tool."

For your example, I would put the MIN function in a cell, the MAX function
in another cell with a reference to the cell containing the MIN, the IF
function in another cell with appropriate references, and finally, the INDEX
function in a fourth cell.

This approach facilitates debugging.

After everything is working fine, I might combine everything into a single
cell.

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[ilia]

I start by building the formula separately, each intermediate result
(what I consider intermediate) stored in its own cell. Next, I use
find/replace in a text editor to make it into one big formula.

Recently I wrote a macro to do this automatically. It takes a cell,
searches for any references to other cells, and if those cells contain
formulas, it brings them into one, resulting in a megaformula. Its
functionality is limited, but it works for most formulae that have
references on a single sheet and do not contain arrays.

Unless I know exactly what I'm doing, I rarely do everything in one
cell, through the formula bar.

 

[JP]

Can you post that macro?


Thx,
JP

 

[ilia]

It's a little long, so download it here:
http://www.44lbs.net/ilia/mdlMakeMegaFormula.bas

Import the module into your personal macros workbook (File/Import
File).

Please read the comments at the top. Note that you must have a
reference to Microsoft VBScript Regular Expressions 1.0 in your VB
project (Tools/References). Because it's used often, I opted for
early binding. Modify isAddress() function if you wish to use late
binding - such as CreateObject("vbscript.RegExp").

I would appreciate any feedback as to the functionality, and/or
suggestions for modification. My contact information is also in the
comments at the top.

 

[Tim Williams]

Might only help a little, but remember you *can* embed newlines (Alt+Enter)
in your formula to break it up into workable chunks...

Tim