how to change a cell content in a visual basic function?

  • Thread starter Thread starter serdar
  • Start date Start date
S

serdar

myfunction() is a function i called from, lets say, cell C3 as:

= myfunction()

in myfunction() i want to change a cells value:

......
Cells(8, 1) = 3 ( or Cells(8, 1).Value = 3 dunno which is
true:)
.....
myfunction= 123
.....


but i get nothing, cos this is a function but not a sub, i guess.

well, how to do this?
 
Indeed it is not possible to change worksheets from within a function. Use a
SUB instead.
And, before you ask, no, it is not possible to trick Excel and call a sub
from the function to change a cell or anything like that.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
thanks..yep i ve already tried to call a sub:)
let me ask u this way:

i want to alter various cells, just the user enters a value in a cell but
not using a macro cos this needs a second step. is it possible? (am i clear,
im not sure though:)
 
There are event macros that just wait looking for certain things to happen.

Each worksheet has a worksheet_change event that is always running waiting for
you to do something. You can tie into that event if you want.

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

Here's an example that will look for a typing change in A1 and modify B1 when A1
changes.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count > 1 Then Exit Sub 'one cell at a time
If Intersect(.Cells, Me.Range("a1")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End With

errHandler:
Application.EnableEvents = True

End Sub

Target is the range that is changed.

The program will look to see how many cells changed. If that count is larger
than 1, then it gets the heck out.

It looks to see if you changed A1, if it's some other cell, it gets out.

If there's an error, the code jumps to the error handler (near the bottom). It
enables events (so xl can continue monitoring for more changes).

The next line (application.enableevents =false) tells excel to stop looking for
changes. We don't want our update to B1 to cause the event to fire again (and
again and again...).

..offset(0,1) is from a starting point of Target (which is A1 in this example).
So one column to the right of A1 (same row) is B1.

It changes the value in B1 to the date/time (now) and formats the cell to show
the date/time.

Then it continues through the error handler and tells excel to start looking for
changes again.

If you want to test this, start a new workbook.
Rightclick on the worksheet tab that should have this behavior. Select View
Code and paste this into the code window.

Then back to excel and change a few cells -- then change A1!

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Back
Top