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

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

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

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

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