VBA Range

[magix]

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]

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").Range("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]

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]

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

 

[Jon Peltier]

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

- Jon