Set a cell value from a function

Question

The contents of cell A1 is =test(2) where test is the function:

Function test(ByRef x As Double) As Double
  Range("A2") = x
  test = x * x
End Function

Can you explain why this gives #VALUE! in cell A1 and nothing in cell A2? I expected A2 to contain 2 and A1 to contain 4. Without the line Range("A2") = x the function works as expected (squaring the value of a cell).

What is really confusing is if you wrap test with the subroutine calltest then it works:

Sub calltest()
  t = test(2)
  Range("A1") = t
End Sub

Function test(ByRef x As Double) As Double
  Range("A2") = x
  test = x * x
End Function

But this doesn't

Function test(ByRef x As Double) As Double
  Range("A2") = x
End Function

As in http://stackoverflow.com/questions/8520732/i-dont-want-my-excel-add-in-to-return-an-array-instead-i-need-a-udf-to-change it is actually possible for a UDF to change another cell, but it's ugly. — brettdj, Mar 29 '13 at 12:36

Przemyslaw Remin · Answer 1 · 2019-09-10T11:00:06.097

Yes, you can have your own user defined function which writes values to any cell, not just the one which you type in the formula.

Here is a simple example. The UDF function takes two arguments A and B and returns their product A*B. But what is interesting it returns the result in the adjacent cell just to the right of the cell in which we entered the formula.

Put this code in standard VBA module:

Function UDF_RectangleArea(A As Integer, B As Integer)
    Dim MagicSpell As String
    MagicSpell = "Adjacent(" & Application.Caller.Offset(0, 1).Address(False, False) & "," & A & "," & B & ")"
    Evaluate MagicSpell
    UDF_RectangleArea = "Hello world"
End Function

Private Sub Adjacent(CellToChange As Range, A As Integer, B As Integer)
    CellToChange = A * B
End Sub

Now type in B2 the formula: =UDF_RectangleArea(3,4)

The function returns results in two cells: "Hello world" in B2 (which is not surprising) and the rectangle area in C2 (which is a rabbit out of a hat). Both results as well as the place of "rabbit" appearance can be easily customized. The job is done by the VBA EVALUALTE command. The value of variable MagicSpell becomes in this case Adjacent(C2,3,4) which is fired from within the UDF before the UDF result is returned. Have fun!

Maybe explained in other comments but this returns correct answer (after crashing/recovering) every time I use it. — JB-007, Aug 16 '22 at 13:50
Ahh - of course; only works with iterative calculation since the value in B2 updates which calls itself again, and so forth... (I gather) — JB-007, Aug 16 '22 at 13:58

Kazimierz Jawor · Answer 2 · 2013-03-27T13:40:01.273

10

Due to Function fundamentals which state that you can not change or set sheet cells. You need to delete the row with Range("A2") = x

EDIT Some additional link (which I believe is always useful to provide for those who want to analyse UDF topic): Creating custom functions by Microsoft

edited Mar 27 '13 at 13:40

answered Mar 27 '13 at 13:19

Kazimierz Jawor

18,861
7
35
55

Can you explain why the function works if you call it from a subroutine? – Chris Seymour Mar 27 '13 at 13:28
2

There are some other rules which results are that functions which are used in Excel App must be more strict that those used in VBA. Therefore writing and using UDF's is a bit tricky. – Kazimierz Jawor Mar 27 '13 at 13:34
A subroutine is allowed to manipulate worksheet cells, and a function is not. A function can only return a value to itself (or to a variable in another subroutine) – David Zemens Mar 27 '13 at 14:05
@DavidZemens this doesn't explain why a function **can** modify worksheet cells when called from a subroutine but not when called directly? – Chris Seymour Mar 27 '13 at 14:12
2

@sudo_O see the response [here](http://stackoverflow.com/a/15647054/1467082) for possibly a better explanation. It seems that Excel treats a UDF differently when it is called from a Worksheet Cell, than when it is called from a subroutine. I have always used Functions only to *return* values (i.e., to compartmentalize & streamline calculation steps) to subroutines, which then the subroutine sets the cell value. – David Zemens Mar 27 '13 at 14:30

score 7 · Accepted Answer · answered Mar 27 '13 at 14:30

When you call a function from a worksheet cell, you are effectively using the function as a User Defined Function, which has the limitations as described here:

http://support.microsoft.com/kb/170787

In the text there is a line:

Any environmental changes should be made through the use of a Visual Basic subroutine.

It's interesting how they use the word should rather than must. I wonder if the author of the KB knew that environment changes can happen from a VBA Function.

Now, when you call the function from another VBA Sub / Function, it is treated differently. From the help docs (sorry I couldn't find a web page reference - basically, in VBE, highlight the word Function and press F1):

Like a Sub procedure, a Function procedure is a separate procedure that can take arguments, perform a series of statements, and change the values of its arguments. However, unlike a Sub procedure, you can use a Function procedure on the right side of an expression in the same way you use any intrinsic function, such as Sqr, Cos, or Chr, when you want to use the value returned by the function.

So it sounds like Subs and functions can do the same things when used in VBA only, except that Functions can return values back to the calling function/sub.

This is pretty interesting, actually, since Excel can call a function with some sort of "Read-Only" restriction to Excel's environment.

I think that, in the end, Excel can call the function from a worksheet cell in a different way than VBA does. When you call it from a Cell, it's considered a User Defined Function, which includes the restrictions of changing Excel's environment. Where when called from within VBA (where the original caller from a chain of calls is from VBA), it has all the power a Sub does, plus it can return values.

It's *should* and not *must*, because you **can** legally [write a UDF that messes with `Application.EnableEvents`](https://i.stack.imgur.com/P6i60.png), for example. ...which is a beautiful source of unexpected bugs and unintentional behavior. — Mathieu Guindon, Dec 09 '16 at 01:50

David Wooley - AST · Answer 4 · 2020-03-19T23:33:50.887

I've adapted your code to work for strings and functions and anything else in-between I hope (still testing).

Alot of questions resulting from testing, but 2 come to mind first:

What other ways could you use to execute / process the parameters and return the desired results;

a. As I have done b. Not as I have done (i.e. differently).

Many thanks to Rick Rothstein MrExcel MVP & many others on SO for enabling & helping for this to happen.

Function MOVEME27(a As Variant, b As Variant, Optional CELLR As Variant, Optional cellq As Variant) '21/05/2018 works copied to ar4' 03/06/2019 23:30 was cellr as range , cellq as range - changed to variants
 Dim WTVR1 As Variant '' ''20/05/2019'' '09/06/2019 Code by S Tzortzis/David Wooley
 Dim WTVR2 As Variant
 Dim P As String
 Dim P1 As String
 Dim bb As String
 Dim bb1 As String
 Dim A1 As Long
 Dim A2 As Long

 Dim c As String

 'x' a = Evaluate(a)
P = Chr(34) & a & Chr(34)
P2 = Chr(34) & [P] & Chr(34)

bb = Chr(34) & b & Chr(34)
bb1 = Chr(34) & [bb] & Chr(34)

c = Chr(34) & CELLR & Chr(34)
f = Chr(34) & callq & Chr(34)


'P2 = Chr(34) & "'''" & [P] & "'''" & Chr(34)
'p1 = Chr(34) & p & Chr(34)

''WTVR1 = "MOVEUS1(" & Application.Caller.Offset(0, 2).Address(False, False) & "," & Chr(34) & P2 & Chr(34) & "," & b & ")"
   WTVR1 = "MOVEUS11h(" & Application.Caller.Offset(0, 2).Address(False, False) & "," & [P2] & "," & [bb1] & ")"
Evaluate WTVR1


WTVR2 = "MOVEUS22h(" & Application.Caller.Offset(0, 1).Address(False, False) & "," & [P2] & "," & [bb1] & ")" ' used or be adjacent - maybe redo rhat pr put a GO TO sub. '' ''20/05/2019''

Evaluate WTVR2

A1 = cellq.Row
A2 = cellq.Column

CELLRR = Chr(34) & CELLR & Chr(34)
CELLRR1 = Chr(34) & [CELLRR] & Chr(34)
cellqq = Chr(34) & cellq & Chr(34)
cellqq1 = Chr(34) & [cellqq] & Chr(34)


''wtvr3 = "CopyFrom.Parent.Evaluate CopyOver234h(" & c & "," & f & ")" ''''20190531 1929
wtvr31 = "MOVEUS33h(" & Application.Caller.Offset(A1 - ActiveCell.Row + 1, A2 - ActiveCell.Column).Address(False, False) & "," & [CELLRR] & "," & [cellqq] & ")"

    Evaluate wtvr31

MOVEME27 = "Hello world       " & " / " & WTVR1 & " / " & WTVR2 & "\\\\\/////" & wtvr31 & "\\\\\/////---" & ActiveCell.Row - A1 & "//////---" & ActiveCell.Column - A2

' DO AS WHATVER = "MOVEUS3(" APPLICATION.CALLER.OFFSET(THE ROW & COLUMN IE CELL         YOU REFERENCES IN a as variant (copy from)'
    'with ="" in sub 30052019 19:28

    'CopyFrom.Parent.Evaluate "CopyOver2(" & CELLR.Address(False, 1) & "," &              CELLR.Address(False, False) & ")"  ''''2019050 1929



    End Function

    Private Sub MOVEUS11h(CELL1 As Range, G1 As Variant, G2 As Variant)

        '[ak333] = a


        CELL1 = Chr(34) & G1 & Chr(34) & "B" & "//" & G2


    End Sub


    Private Sub MOVEUS22h(CELL2 As Range, G3 As Variant, G4 As Variant)

        CELL2 = Chr(34) & G3 & Chr(34) & "<>" & G4

    End Sub

    '' with chr(34) arond the p's and a's in sub or fucntion changes behavior. thinking of doing if a is string, then a=x , x as string, if not kep as variant
    ''27/05/2019 :(

    '''''30/05/2019 .....'''''''


    '------------------------------------------------------------------------------- ADD THIS 30052019 -------------------------------
    'private sub Movus3(cellfrom as range, cellto as range)
    'End Sub

    Private Sub moveus33h(cell3 As Range, CopyFrom As Variant, copyTo As Variant) ''''2019050 1929 ''' 03062019 change ema back to as Range here. :)
       '' copyTo.Value = CopyFrom.Value ''''2019050 1929

       ''CopyFrom.Value = ""

       cell3 =  CopyFrom  'Chr(34) & CopyFrom & Chr(34)

    End Sub ''''2019050 1929

Ive just Realised Python & Juyter Does this, so I highly doubtful this ability/func is a useful function as we currently perceive and use Excel (but it may be in future). .With things like Jupyter in existence (which I found out about on Friday evening by chance) I don't know if there will ever be (but it could!) (note to self: need to get Jupiter & Python which im doing from anaconda now).. :) have a nice day. Catching up on life /abilities — David Wooley - AST, Jun 09 '19 at 16:12
well, for starters, adapting it for colors, it could be used in conjuction with stackoverflow.com/questions/51028036/… to highlight any matched cells in an array. I know conditional formatting does this, but with a function like above (which could be easily be made) could do this too. / time to learn SAS (again), Jupyter, Python. — David Wooley - AST, Jun 09 '19 at 16:47
Apart from the fact that that it is what makes it work ( in my version ), What are the implications of using evaluate variable parameter to excels execution, excels processing and ? That is why I asked & this is why it is super hard/ confusing to ask a question about it (this answer). & wrapping the parameters-treating as string (which i did x2 for just in case). what if i don't treat as a string, will test that too - already did before in previous version. too much Bye/ Too Much — David Wooley - AST, Jun 09 '19 at 18:33
excels processing and the mind boggling implications and further actions things you could do from this - im testing it on all fronts - accepts if functions, even refering to itself !?! pandoras box is right, its basically allowing (or appearing to allow for) mad circular logic .this just blows things out of the water ! I had fun yes, goes into real time ... excel is allowing for this ... imo brilliant . used as a tool and responsibly it can help. do other stuff , automations (perhaps go into ML?) idk. — David Wooley - AST, Jun 09 '19 at 19:30
I started doing it with char(34)&A1&char(34) entered manually to see what would happen/be returned; That worked (to carry these results somewhere and do/add things to them) ,so did calling the text of the function in A1, as did substititing "=" sign in functions in A1. Then i saw the task required me entering the char(34) in the code or refining the input variables in the parameters. — David Wooley - AST, Jun 12 '19 at 16:52
There is the interesting case where you can use this to get the right result, print/write it over your formula, and make your formula disapear that was used to calcilate the result Thus making it appear no formula or calculations where used to get the value in the cell. — David Wooley - AST, Jun 22 '19 at 19:33
There is the interesting case where you can use this to get the right result, print/write it over your formula, and make your formula disapear that was used to calcilate the result Thus making it appear no formula or any calculations where used to get the value in the cell. Write over your own formula with the formula. Idk if there is any use in this!!! But its impressive!! (While still showing the formula used elsewhere or adjacent.) / feels that anything could be possible.! — David Wooley - AST, Jun 22 '19 at 20:02

Set a cell value from a function

4 Answers4

Linked