Is it really impossible to change cells value with function?

[Joel Horowitz]

Hye,

I never managed to make a function like

Function Test()

range("A1").value = "Hello"
Test = 34

End function

I always read it is impossible, yet I've seen add-ins that download stock quotes and manage to change the value of cells other than the one in which the function is put. How do they do that?

Thanks

 

[Damon Ostrander]

Re: Is it really impossible to change cells value with funct

Hi Joel,

The real restriction is that a function called from a worksheet cell cannot do anything that could trigger a worksheet calculation. Thus it cannot change a cell's value or any other cell property. This means that essentially it cannot do anything but return a value into the cell's formula during calculation.

With that said, there are numerous variances from this restriction:

1. A function can return an array, and thereby modify multiple [contiguous] cells.

2. A function can modify multiple non-contiguous cells and do just about anything, providing that it is NOT called from a worksheet cell, i.e., it is called from another VBA procedure.

3. A Sub procedure can of course also modify multiple cells since by definition it cannot be called from a worksheet formula.

4. Worksheet events Calculate and Change can modify cells when data is entered into a cell or when a worksheet calculates, thereby enabling nearly the same functionality as a worksheet function, but with no function in the cell. Again, this eliminates the restriction on modifying cells.

I hope this helps.

Damon

 

[Joel Horowitz]

Re: Is it really impossible to change cells value with funct

4. Worksheet events Calculate and Change can modify cells when data is entered into a cell or when a worksheet calculates, thereby enabling nearly the same functionality as a worksheet function, but with no function in the cell. Again, this eliminates the restriction on modifying cells.

Damon

This seems for me the solution. The problem is that it seems quite difficult to coordinate the function with the calculate or change events. I am not sure yet how I'll get a sub to get the arguments of the function called. But I'll try to figure out

Thanks

 

[Damon Ostrander]

Re: Is it really impossible to change cells value with funct

Hi again Joel,

To get the arguments to a Sub procedure, you must read them directly from the cells within the procedure, i.e., NOT pass them as arguments. For example if you want a sub to put the sum of the numbers in A1 and A2 into cell B4, and the difference between A1 and A2 into C9 whenever either A1 or A2 are manually changed, use the worksheet Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target,[A1:A2]) Is Nothing Then
[B4] = [A1] + [A2]
[C9] = [Z2] - [A1]
End If
End Sub

As you can see, this modifies two different disjoint cells in one routine.

I hope this helps.

Damon