VBA Range
 

Hi,

If I have VBA Function, what is the correct statement to insert a value into
a particular cell, let say cell A1 ?

Function ABC ((S1 As String) As String


' how can I set the value at particular cell ?
Range("A1").value = S1
...
...
End Function

is this Range("A1").value = S1 correct ? or Application.Range("A1").value =
S1 ? or... ?

VBA Range
 

I would try to be as specific as possible:

activesheet.Range("A1").value = S1
or
worksheets("sheet999").Range("A1").value = S1
or
activeworkbook.worksheets("sheet999").Range("A1"). value = S1
or
someworkbookvariablehere.worksheets("sheet999").Ra nge("A1").value = S1

Just a word of warning.

If you're thinking about using this function in a worksheet cell, then it won't
work. A function called from a cell on a worksheet can't change the value in
another cell. Essentially, it can only return a value to the cell with the
formula.


magix wrote:

Hi,

If I have VBA Function, what is the correct statement to insert a value into
a particular cell, let say cell A1 ?

Function ABC ((S1 As String) As String

' how can I set the value at particular cell ?
Range("A1").value = S1
...
...
End Function

is this Range("A1").value = S1 correct ? or Application.Range("A1").value =
S1 ? or... ?

--

Dave Peterson

VBA Range
 

Hi Dave,

I think my scenario fall under your warning statement.
It doens't work with the suggestion.
I just want to set a value in another cell. You said ="A function called
from a cell on a worksheet can't change the value in
another cell."

Then how can I change the value in another cell after this function has been
executed in a worksheet cell ?

Thanks.

Regards,
Magix



"Dave Peterson" wrote in message
...
I would try to be as specific as possible:

activesheet.Range("A1").value = S1
or
worksheets("sheet999").Range("A1").value = S1
or
activeworkbook.worksheets("sheet999").Range("A1"). value = S1
or
someworkbookvariablehere.worksheets("sheet999").Ra nge("A1").value = S1

Just a word of warning.

If you're thinking about using this function in a worksheet cell, then it
won't
work. A function called from a cell on a worksheet can't change the value
in
another cell. Essentially, it can only return a value to the cell with
the
formula.


magix wrote:

Hi,

If I have VBA Function, what is the correct statement to insert a value
into
a particular cell, let say cell A1 ?

Function ABC ((S1 As String) As String

' how can I set the value at particular cell ?
Range("A1").value = S1
...
...
End Function

is this Range("A1").value = S1 correct ? or Application.Range("A1").value
=
S1 ? or... ?

--

Dave Peterson

VBA Range
 

You can't use a formula to change the value in another cell.

You could use a Sub (subroutine, not Function) that does the work. In fact,
excel has some builtin events that you may be able to tie into.

But it depends on what you want. You could tie into the worksheet_change event
if the user is typing something that causes the change. You could tie into the
worksheet_calculate if a formula reevaluates and causes the change.

Some references:

David McRitchie's intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

You can read more about events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

magix wrote:

Hi Dave,

I think my scenario fall under your warning statement.
It doens't work with the suggestion.
I just want to set a value in another cell. You said ="A function called
from a cell on a worksheet can't change the value in
another cell."

Then how can I change the value in another cell after this function has been
executed in a worksheet cell ?

Thanks.

Regards,
Magix

"Dave Peterson" wrote in message
...
I would try to be as specific as possible:

activesheet.Range("A1").value = S1
or
worksheets("sheet999").Range("A1").value = S1
or
activeworkbook.worksheets("sheet999").Range("A1"). value = S1
or
someworkbookvariablehere.worksheets("sheet999").Ra nge("A1").value = S1

Just a word of warning.

If you're thinking about using this function in a worksheet cell, then it
won't
work. A function called from a cell on a worksheet can't change the value
in
another cell. Essentially, it can only return a value to the cell with
the
formula.


magix wrote:

Hi,

If I have VBA Function, what is the correct statement to insert a value
into
a particular cell, let say cell A1 ?

Function ABC ((S1 As String) As String

' how can I set the value at particular cell ?
Range("A1").value = S1
...
...
End Function

is this Range("A1").value = S1 correct ? or Application.Range("A1").value
=
S1 ? or... ?

--

Dave Peterson

--

Dave Peterson

VBA Range
 

Or put a formula into the cell that's supposed to change based on the first
cell.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Dave Peterson" wrote in message
...
You can't use a formula to change the value in another cell.

You could use a Sub (subroutine, not Function) that does the work. In
fact,
excel has some builtin events that you may be able to tie into.

But it depends on what you want. You could tie into the worksheet_change
event
if the user is typing something that causes the change. You could tie
into the
worksheet_calculate if a formula reevaluates and causes the change.

Some references:

David McRitchie's intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

You can read more about events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

magix wrote:

Hi Dave,

I think my scenario fall under your warning statement.
It doens't work with the suggestion.
I just want to set a value in another cell. You said ="A function called
from a cell on a worksheet can't change the value in
another cell."

Then how can I change the value in another cell after this function has
been
executed in a worksheet cell ?

Thanks.

Regards,
Magix

"Dave Peterson" wrote in message
...
I would try to be as specific as possible:

activesheet.Range("A1").value = S1
or
worksheets("sheet999").Range("A1").value = S1
or
activeworkbook.worksheets("sheet999").Range("A1"). value = S1
or
someworkbookvariablehere.worksheets("sheet999").Ra nge("A1").value = S1

Just a word of warning.

If you're thinking about using this function in a worksheet cell, then
it
won't
work. A function called from a cell on a worksheet can't change the
value
in
another cell. Essentially, it can only return a value to the cell with
the
formula.


magix wrote:

Hi,

If I have VBA Function, what is the correct statement to insert a
value
into
a particular cell, let say cell A1 ?

Function ABC ((S1 As String) As String

' how can I set the value at particular cell ?
Range("A1").value = S1
...
...
End Function

is this Range("A1").value = S1 correct ? or
Application.Range("A1").value
=
S1 ? or... ?

--

Dave Peterson

--

Dave Peterson