Macro works, identical Function doesn't....why?
 

I have the following simplified code that represents my problem. When I run
the code from the VBA editor, or run the individual macros it works
perfectly. But when I call Test() from a worksheet function the WriteIt
macro generates a #Value error and the EraseIt macro does nothing at all.
What am I doing wrong? Im useing Excel 2003.

Function Test() As Boolean

MsgBox ("Click OK to Write")
Call WriteIt
MsgBox ("Click OK to Erase")
Call EraseIt

End Function

Sub WriteIt()

Range("A1").Value = "Hello"

End Sub

Sub EraseIt()

Range("A1").ClearContents

End Sub

Macro works, identical Function doesn't....why?
 

Hi Paul,

A worksheet function returns a value to the cell contining the function. It
cannot change another cell or alter its environment.

---
Regards,
Norman



"Paul S" wrote in message
...
I have the following simplified code that represents my problem. When I
run
the code from the VBA editor, or run the individual macros it works
perfectly. But when I call Test() from a worksheet function the WriteIt
macro generates a #Value error and the EraseIt macro does nothing at all.
What am I doing wrong? Im useing Excel 2003.

Function Test() As Boolean

MsgBox ("Click OK to Write")
Call WriteIt
MsgBox ("Click OK to Erase")
Call EraseIt

End Function

Sub WriteIt()

Range("A1").Value = "Hello"

End Sub

Sub EraseIt()

Range("A1").ClearContents

End Sub

Macro works, identical Function doesn't....why?
 

Thanks Norman,

The books I have never mentioned that a function couldn't change other
cells. But it seems you must be right, because that is how it acts. Is
there any way to get around this limitation? My example was trying to call
the macros from the function. Can I instead have the macros called in some
other automated way? Im thinking something like when a change is sensed in a
cell. My book refers to Auto_open but that wouldn't suit my purposes.

Paul


"Norman Jones" wrote:

Hi Paul,

A worksheet function returns a value to the cell contining the function. It
cannot change another cell or alter its environment.

---
Regards,
Norman



"Paul S" wrote in message
...
I have the following simplified code that represents my problem. When I
run
the code from the VBA editor, or run the individual macros it works
perfectly. But when I call Test() from a worksheet function the WriteIt
macro generates a #Value error and the EraseIt macro does nothing at all.
What am I doing wrong? Im useing Excel 2003.

Function Test() As Boolean

MsgBox ("Click OK to Write")
Call WriteIt
MsgBox ("Click OK to Erase")
Call EraseIt

End Function

Sub WriteIt()

Range("A1").Value = "Hello"

End Sub

Sub EraseIt()

Range("A1").ClearContents

End Sub

Macro works, identical Function doesn't....why?
 

Hi Paul,

Look at the Worksheet_Change event.

For information on event procedures, see Chip Pearson's notes at:

http://www.cpearson.com/excel/events.htm


---
Regards,
Norman



"Paul S" wrote in message
...
Thanks Norman,

The books I have never mentioned that a function couldn't change other
cells. But it seems you must be right, because that is how it acts. Is
there any way to get around this limitation? My example was trying to
call
the macros from the function. Can I instead have the macros called in
some
other automated way? Im thinking something like when a change is sensed
in a
cell. My book refers to Auto_open but that wouldn't suit my purposes.

Paul


"Norman Jones" wrote:

Hi Paul,

A worksheet function returns a value to the cell contining the function.
It
cannot change another cell or alter its environment.

---
Regards,
Norman



"Paul S" wrote in message
...
I have the following simplified code that represents my problem. When I
run
the code from the VBA editor, or run the individual macros it works
perfectly. But when I call Test() from a worksheet function the
WriteIt
macro generates a #Value error and the EraseIt macro does nothing at
all.
What am I doing wrong? Im useing Excel 2003.

Function Test() As Boolean

MsgBox ("Click OK to Write")
Call WriteIt
MsgBox ("Click OK to Erase")
Call EraseIt

End Function

Sub WriteIt()

Range("A1").Value = "Hello"

End Sub

Sub EraseIt()

Range("A1").ClearContents

End Sub

Macro works, identical Function doesn't....why?
 

Thanks again Norman,

That was exactly what I was looking for. I'll get the hang of this yet...

Paul


"Norman Jones" wrote:

Hi Paul,

Look at the Worksheet_Change event.

For information on event procedures, see Chip Pearson's notes at:

http://www.cpearson.com/excel/events.htm


---
Regards,
Norman



"Paul S" wrote in message
...
Thanks Norman,

The books I have never mentioned that a function couldn't change other
cells. But it seems you must be right, because that is how it acts. Is
there any way to get around this limitation? My example was trying to
call
the macros from the function. Can I instead have the macros called in
some
other automated way? Im thinking something like when a change is sensed
in a
cell. My book refers to Auto_open but that wouldn't suit my purposes.

Paul


"Norman Jones" wrote:

Hi Paul,

A worksheet function returns a value to the cell contining the function.
It
cannot change another cell or alter its environment.

---
Regards,
Norman



"Paul S" wrote in message
...
I have the following simplified code that represents my problem. When I
run
the code from the VBA editor, or run the individual macros it works
perfectly. But when I call Test() from a worksheet function the
WriteIt
macro generates a #Value error and the EraseIt macro does nothing at
all.
What am I doing wrong? Im useing Excel 2003.

Function Test() As Boolean

MsgBox ("Click OK to Write")
Call WriteIt
MsgBox ("Click OK to Erase")
Call EraseIt

End Function

Sub WriteIt()

Range("A1").Value = "Hello"

End Sub

Sub EraseIt()

Range("A1").ClearContents

End Sub