How to change an UDF argument?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In Visual Basic I wrote a function which should copy the value of the argument to calling cell and reset the argument cell to the value 1

Public Function MyFunction(x
MyFunction =
x =
End Functio

So in a worksheet =MyFunction(A1) should result in A1 getting the value 1. But this does not work. How can I get this going

Regards, Gauss
 
A worksheeet function cannot make any changes to the worksheet, other than
returning a value to the calling cell.

--

Vasant

Gauss said:
In Visual Basic I wrote a function which should copy the value of the
Click to expand...
argument to calling cell and reset the argument cell to the value 1.
Public Function MyFunction(x)
MyFunction = x
x = 1
End Function

So in a worksheet =MyFunction(A1) should result in A1 getting the value 1.
Click to expand...
But this does not work. How can I get this going?
 
Gauss,

UDF's cannot change the values of other cells. Even if it could you'd have a new
problem.

Your formula (for example) in cell B1
=MyFunction(A1)

Results in
B1 = A1 Value
A1 = 1

Next time you calculate
B1 = 1
A1 = 1

Dan E
 
I tried to explain my problem by a simple example that did not make sence because of circular reference
What about the next example

Public Function MyFunction(ByRef x, ByRef y
MyFunction =
y =
End Functio

Adding 'ByRef' to arguments still doesn't get me the pointers to x and y. Is there another way to get the pointers

Regards, Gauss
 
As was said in earlier reply: a UDF cannot change anything other than the
value of the cell in which it appears. It therefore cannot change the cell
to which Y refers.
To do this you will need to use a subroutine
Bernard

Gauss said:
I tried to explain my problem by a simple example that did not make sence because of circular reference.
What about the next example:

Public Function MyFunction(ByRef x, ByRef y)
MyFunction = x
y = x
End Function

Adding 'ByRef' to arguments still doesn't get me the pointers to x and y.
Click to expand...
Is there another way to get the pointers?
 
You suggest to use a subroutine. How should this subroutine
look like? Can it be called within the UDF?
 
Back
Top