Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with active cell
I have written a function in VBA, Excel 2003.
In the spreadsheet I have placed it in the cell via the formula bar text e.g. "=dostuff()" The function works and is called whenever the cell changes or when the spreadsheet is opened. This is great. But if I want the function to change the contents of another cell, i.e. not the cell that called the function then this results in an Application defined error or object defined error. I believe the problem may be that I can not change the active cell from the cell that has called this function. In summary, I have placed the function "=dostuff() in cell "A1" I want the function to return an integer (no problem it does this) I also want it to enter an integer value into cell "D2" Is there a way of doing this? Thank you for your help. john |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with active cell
Hi John.
Your post is not much clear, however try to insert in your UDF: Range("D2").Value = your integer value Regards Eliano "none" <""john"@(none)" wrote: I have written a function in VBA, Excel 2003. In the spreadsheet I have placed it in the cell via the formula bar text e.g. "=dostuff()" The function works and is called whenever the cell changes or when the spreadsheet is opened. This is great. But if I want the function to change the contents of another cell, i.e. not the cell that called the function then this results in an Application defined error or object defined error. I believe the problem may be that I can not change the active cell from the cell that has called this function. In summary, I have placed the function "=dostuff() in cell "A1" I want the function to return an integer (no problem it does this) I also want it to enter an integer value into cell "D2" Is there a way of doing this? Thank you for your help. john |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with active cell
UDF's placed in a cell can return values to that same cell.
They can't change the value of other cells. none wrote: I have written a function in VBA, Excel 2003. In the spreadsheet I have placed it in the cell via the formula bar text e.g. "=dostuff()" The function works and is called whenever the cell changes or when the spreadsheet is opened. This is great. But if I want the function to change the contents of another cell, i.e. not the cell that called the function then this results in an Application defined error or object defined error. I believe the problem may be that I can not change the active cell from the cell that has called this function. In summary, I have placed the function "=dostuff() in cell "A1" I want the function to return an integer (no problem it does this) I also want it to enter an integer value into cell "D2" Is there a way of doing this? Thank you for your help. john -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with active cell
no
A function can only change the value of the cell into which it is entered. -- Gary''s Student - gsnu200748 "none" <""john"@(none)" wrote: I have written a function in VBA, Excel 2003. In the spreadsheet I have placed it in the cell via the formula bar text e.g. "=dostuff()" The function works and is called whenever the cell changes or when the spreadsheet is opened. This is great. But if I want the function to change the contents of another cell, i.e. not the cell that called the function then this results in an Application defined error or object defined error. I believe the problem may be that I can not change the active cell from the cell that has called this function. In summary, I have placed the function "=dostuff() in cell "A1" I want the function to return an integer (no problem it does this) I also want it to enter an integer value into cell "D2" Is there a way of doing this? Thank you for your help. john |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with active cell
you might want to put the udf in any "hidden" cell, adjust the vba code
so that both integer values are returned as a combined string (insert a separator character between the two values), insert in the cells A1 and D2 formulas to return the first/second part of the combined string and finally convert them back to integers. none wrote: I have written a function in VBA, Excel 2003. In the spreadsheet I have placed it in the cell via the formula bar text e.g. "=dostuff()" The function works and is called whenever the cell changes or when the spreadsheet is opened. This is great. But if I want the function to change the contents of another cell, i.e. not the cell that called the function then this results in an Application defined error or object defined error. I believe the problem may be that I can not change the active cell from the cell that has called this function. In summary, I have placed the function "=dostuff() in cell "A1" I want the function to return an integer (no problem it does this) I also want it to enter an integer value into cell "D2" Is there a way of doing this? Thank you for your help. john |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Row select mode to highlight active row of active cell | Excel Discussion (Misc queries) | |||
I need to sort an active sheet using the col of the active cell | Excel Programming | |||
Can't get CF to work properly, Active Cell problem? | Excel Programming | |||
Excel VBA-use variable in active cell formula problem | Excel Programming | |||
Problem with Active Cell Refence | Excel Programming |