Capabilities of a User-Defined Function
 

I know this is a strange question, but do UDFs have the ability to
change the spreadsheet's properties? For example, I have a function
which computes a value, but I needed it to merge the cell with the one
below if that value met certain conditions. It doesn't seem to want to
do the merge, but it seems to me that VBA instructions in the UDF are
just that; instructions. It shouldn't matter whether they affect cell
properties or not.

Am I correct, or have the programmers of Excel given it enough
sophistication to know that a UDF is purely for producing a value?

Thanks for any input...

Capabilities of a User-Defined Function
 

Hi Dennis,

A UDF returns a value to the calling cell; it cannot alter its environment.


---
Regards,
Norman



"Dennis Snelgrove" wrote in message
ups.com...
I know this is a strange question, but do UDFs have the ability to
change the spreadsheet's properties? For example, I have a function
which computes a value, but I needed it to merge the cell with the one
below if that value met certain conditions. It doesn't seem to want to
do the merge, but it seems to me that VBA instructions in the UDF are
just that; instructions. It shouldn't matter whether they affect cell
properties or not.

Am I correct, or have the programmers of Excel given it enough
sophistication to know that a UDF is purely for producing a value?

Thanks for any input...

Capabilities of a User-Defined Function
 

Hi Dennis,

I know this is a strange question, but do UDFs have the ability to
change the spreadsheet's properties?

No they have not. They do what they are designed for: return results.

BTW: I advise against merging cells, too much trouble with sorting and
copying.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

Capabilities of a User-Defined Function
 

It turns out that functions can do some strange things.

John Walkenbach has a few things documented:
http://j-walk.com/ss/excel/odd/odd06.htm

They can insert comments into other cells.

And they can merge cells (even other cells that don't hold that formula).

You can try something like:

Option Explicit
Function testme01()

Dim a As Long
Dim b As Long
a = 5
b = 10

If a < b Then
Application.Caller.Offset(3, 3).Resize(4, 4).Merge
End If
End Function

in your function.

But I'm not sure if this is a bug in excel (that may be fixed in newer
versions). You may want to do things like this with both eyes wide open.

(And I agree with Jan Karel--merged cells are a pain to work with. I try to
avoid them at all costs.)



Dennis Snelgrove wrote:

I know this is a strange question, but do UDFs have the ability to
change the spreadsheet's properties? For example, I have a function
which computes a value, but I needed it to merge the cell with the one
below if that value met certain conditions. It doesn't seem to want to
do the merge, but it seems to me that VBA instructions in the UDF are
just that; instructions. It shouldn't matter whether they affect cell
properties or not.

Am I correct, or have the programmers of Excel given it enough
sophistication to know that a UDF is purely for producing a value?

Thanks for any input...

--

Dave Peterson