Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't Update Cells from a Function?
I have a function for which I'd like to update some cells
programmatically. For the purposes of simplicity, I've boiled the problem down to: Function CellUpdt(InR as Range, OutC as Range) As Integer ' Do some processing with InR ... ' Update some spreadsheet cells Cells(OutCell.Row, OutCell.Column) = "Value1" ' Return CellUpdt = 0 End Function The function is driven from the spreadsheet itself, ie., =CellUpdt(M24:P50,R7:R10). I can't seem to get this work. In the debugger, when I reach the 1st assignment statement, the program just ends - no errors, no messages. Anybody know what's wrong? Thanks, Tommy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't Update Cells from a Function?
Hi Tommy
a function (UDF) can't do this. You are not able to change cells or formats or other things. You can only return values Frank Tommy wrote: I have a function for which I'd like to update some cells programmatically. For the purposes of simplicity, I've boiled the problem down to: Function CellUpdt(InR as Range, OutC as Range) As Integer ' Do some processing with InR ... ' Update some spreadsheet cells Cells(OutCell.Row, OutCell.Column) = "Value1" ' Return CellUpdt = 0 End Function The function is driven from the spreadsheet itself, ie., =CellUpdt(M24:P50,R7:R10). I can't seem to get this work. In the debugger, when I reach the 1st assignment statement, the program just ends - no errors, no messages. Anybody know what's wrong? Thanks, Tommy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't Update Cells from a Function?
Tommy,
The design is unsound - you can't do what you want to do. A worksheet function can return a value to the calling cell, it can change other cells as you are trying to do. For this, you need to use the Worksheet_Change event code. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Tommy" wrote in message ... I have a function for which I'd like to update some cells programmatically. For the purposes of simplicity, I've boiled the problem down to: Function CellUpdt(InR as Range, OutC as Range) As Integer ' Do some processing with InR ... ' Update some spreadsheet cells Cells(OutCell.Row, OutCell.Column) = "Value1" ' Return CellUpdt = 0 End Function The function is driven from the spreadsheet itself, ie., =CellUpdt(M24:P50,R7:R10). I can't seem to get this work. In the debugger, when I reach the 1st assignment statement, the program just ends - no errors, no messages. Anybody know what's wrong? Thanks, Tommy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't Update Cells from a Function?
Thanks to all. Looks like I'll have to change my design.
-----Original Message----- I have a function for which I'd like to update some cells programmatically. For the purposes of simplicity, I've boiled the problem down to: Function CellUpdt(InR as Range, OutC as Range) As Integer ' Do some processing with InR ... ' Update some spreadsheet cells Cells(OutCell.Row, OutCell.Column) = "Value1" ' Return CellUpdt = 0 End Function The function is driven from the spreadsheet itself, ie., =CellUpdt(M24:P50,R7:R10). I can't seem to get this work. In the debugger, when I reach the 1st assignment statement, the program just ends - no errors, no messages. Anybody know what's wrong? Thanks, Tommy . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update tab function | Excel Worksheet Functions | |||
Don't Update Function | Excel Discussion (Misc queries) | |||
Cells w/ user defined function do not auto update | Excel Worksheet Functions | |||
Function Update | Excel Worksheet Functions | |||
How do I link a row of cells in wks 1 to update diff cells wks 2 | Excel Worksheet Functions |