Update cell value while dragging UDF

[Wamme]

Hi,

I'm working on a UDF that makes a calculation based on 2 cell values and
returns it outcome. I coded a UDF that works perfectly while using it on 1
cell at a time, but failes when dragging it over multiple cells.

Now, the 2 cells are always the same (A1 ,A2) but their values are variabel
and defined as arguments in the functioncall.
So before the UDF performs the calculation, the 2 cells have to be updated
by the argumentvalues. (Through the use of 2 extra functions:
A1=UpdateValue1() and A2=UpdateValue2())

After some examining I found that the 2 cells only updates once at then end
of the dragging operation:

evaluate UDF in D4
evaluate UDF in D5
....
evaluate UDF in D20
update CellValue A1
update CellValue A2

The Desired processflow would be:
evaluate UDF in D4
update CellValue A1
update CellValue A2
evaluate UDF in D5
update CellValue A1
update CellValue A2
....
evaluate UDF in D20
update CellValue A1
update CellValue A2

Can someone help plz? Use a wait/break or an event?

As an example I simplified the calculation as a sum of the two Cell values.
(The desired calculation is more complex)



Public UpdateValue1 As Integer
Public UpdateValue2 As Integer

Function setValues(Value1 As Integer, Value2 As Integer)
Application.Volatile True
UpdateValue1 = Value1
UpdateValue2 = Value2
setValues = Range("A1").Value + Range("A2").Value
End Function

Function updateCell1() As Integer
Application.Volatile True
updateCell1 = UpdateValue1
End Function

Function updateCell2() As Integer
Application.Volatile True
updateCell2 = UpdateValue2
End Function

 

[Joel]

You hard coded you UDF to read A1 and A2

setValues = Range("A1").Value + Range("A2").Value


shouldn't this be

setvalues = UpdateValue1 + UpdateValue2

 

[Wamme]

Yes but then I didn't need the use of an updatefunction for A1 and A2.

I'm trying to update the values of A1 and A2 because there are other
functions in the worksheet that use the value of A1 and A2. By updating A1
and A2 with UDF setValues the other functions gets updated too.

Thanks

 

[Wamme]

Is there a way to break up the autofill of the formula into separate cells?
Something like: Autofill(D420)
for eachCell do
...
end for

?

 

[Joel]

1) If there is another cell that needs to be referenced in a UDF you should
add it into the parameter list as a 3rd variable.

2) You can use Copy instead of autofill. Autofill only works if there is a
pattern list
A1 1
A2 2
A3 3

autofill A4 and A5 will give
A4 4
A5 5

Using Autofill will copy the numbers down the sheet.

Copy will work with only one location

B1 = A1 + 5

the copying B1 to Range B2:B5 will produce the following

B1 = A1+5
B2 = A2+5
B3 = A3+5
B4 = A4+5
B5 = A5+5