ExcelBanter - VBA Cell Addressing

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - VBA Cell Addressing (https://www.excelbanter.com/excel-discussion-misc-queries/17961-vba-cell-addressing.html)

VBA Cell Addressing

I've got what must be a simple addressing problem but it's driving me
crazy. I can create a simple macro that just looks like:

Sub TestMacro
ActiveCell(1,2) = "Done"
End Sub

And it does exactly what I'd expect. It plugs a value into the cell to
the right of the cursor. Now if I try to do the similar thing with a
Function instead of a Macro it just refuses to co-operate. For example:

Function TestFunct(CellRef)
ActiveCell(1,2) = "Done"
End Function

This will croak with a #VALUE! error. Likewise:

Function TestFunct(CellRef)
CellRef(1,2) = "Done"
End Function

Also refuses to co-operate with the same error. Yet both of these
approaches work fine for me in a Macro.

How do I write to an arbitrary cell location from a Function?

Thanks.

Bill

"Bill Martin -- (Remove NOSPAM from address)"
wrote in message ...
I've got what must be a simple addressing problem but it's driving me
crazy. I can create a simple macro that just looks like:

Sub TestMacro
ActiveCell(1,2) = "Done"
End Sub

And it does exactly what I'd expect. It plugs a value into the cell to
the right of the cursor. Now if I try to do the similar thing with a
Function instead of a Macro it just refuses to co-operate. For example:

Function TestFunct(CellRef)
ActiveCell(1,2) = "Done"
End Function

This will croak with a #VALUE! error. Likewise:

Function TestFunct(CellRef)
CellRef(1,2) = "Done"
End Function

Also refuses to co-operate with the same error. Yet both of these
approaches work fine for me in a Macro.

How do I write to an arbitrary cell location from a Function?

Thanks.

Bill

You can't write to an arbitraty cell from a function. It has to be a Sub.

/Fredrik

Presumably, you are trying to do this from a worksheet? You can't.

A worksheet function can only return a value, it cannot change any of the
cell or worksheet attributes.

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Bill Martin -- (Remove NOSPAM from address)"
wrote in message ...
I've got what must be a simple addressing problem but it's driving me
crazy. I can create a simple macro that just looks like:

Sub TestMacro
ActiveCell(1,2) = "Done"
End Sub

And it does exactly what I'd expect. It plugs a value into the cell to
the right of the cursor. Now if I try to do the similar thing with a
Function instead of a Macro it just refuses to co-operate. For example:

Function TestFunct(CellRef)
ActiveCell(1,2) = "Done"
End Function

This will croak with a #VALUE! error. Likewise:

Function TestFunct(CellRef)
CellRef(1,2) = "Done"
End Function

Also refuses to co-operate with the same error. Yet both of these
approaches work fine for me in a Macro.

How do I write to an arbitrary cell location from a Function?

Thanks.

Bill

Fredrik Wahlgren wrote:

You can't write to an arbitraty cell from a function. It has to be a Sub.

/Fredrik

Well, that explains that. Alternatively can I call a Sub from a
Function, and have that Sub write to an arbitrary cell?

Thanks...

Bill

No, see my reply. The best you can do is use the Worksheet_Change event to
monitor a cell, and if it changes, then trigger your changes.

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Bill Martin -- (Remove NOSPAM from address)"
wrote in message ...
Fredrik Wahlgren wrote:

You can't write to an arbitraty cell from a function. It has to be a Sub.

/Fredrik

Well, that explains that. Alternatively can I call a Sub from a
Function, and have that Sub write to an arbitrary cell?

Thanks...

Bill

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
<html
<head
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type"
</head
<body bgcolor="#ffffff" text="#000000"
Bob Phillips wrote:
<blockquote " type="cite"
<pre wrap=""No, see my reply. The best you can do is use the Worksheet_Change event to
monitor a cell, and if it changes, then trigger your changes.

</pre
</blockquote
<font face="Arial"Thanks Bob.  I'll have to muddle along some other
how.<br
<br
Bill<br
</font
</body
</html