Excel VBA assign value to named range from user defined function fails error 1004

Question

I have a function in a module. The function is defined as follows:

Public Function CourseSetup(ByVal SROfferId As Integer) as string

When the function called, The following line fails:

Worksheets("CourseEvaluation").Range("CourseName").value = "1234"

fails with error 1004.

The same line in a sub (macro) works. CourseName is a one cell named ranged. Getting the value for the above range works.

In the immediate window:

?Worksheets("CourseEvaluation").Range("CourseName").value

returns the current value in the range.

Are you calling this function from the worksheet formula? – David Zemens Apr 18 '13 at 23:13 — David Zemens, Apr 18 '13 at 23:13
Yes, by entering =CourseSetup(+srof) – Moshe Yalovsky Apr 19 '13 at 07:06 — Moshe Yalovsky, Apr 19 '13 at 07:06

score 2 · Answer 1 · answered Apr 19 '13 at 08:05

2

user defined functions called from cells can only return values to the cell(s) they are called from. So your UDF is not allowed to write values to a defined name.

answered Apr 19 '13 at 08:05

Charles Williams

23,121
5
38
38

This was the behavior I saw but needed the confirmation. Thanks! – Moshe Yalovsky Apr 19 '13 at 18:12

score 2 · Accepted Answer · edited May 23 '17 at 11:43

Functions called from the worksheet cannot manipulate worksheet objects other than the active cell wherein the formula is entered.

This preserves dependencies in formulae, prevents circular reference errors, etc.

As a general rule, use Subroutines to manipulate objects, and use Formulae to return values (whether to a cell reference or to a variable).

One exception is that a UDF called from within a subroutine can manipulate worksheet objects, but this is probably not a good habit to start. Keep your functions limited to returning values to variables/cells, and use subroutines to manipulate objects.

Further reading:

Making Excel functions affect 'other' cells

http://www.excelforum.com/excel-programming-vba-macros/477157-changing-cell-values-from-within-udf.html

http://www.ozgrid.com/Excel/free-training/ExcelVBA1/excelvba1lesson21.htm

This was the behavior I saw but needed your confirmation! It does make sense from a security stand point Thank you — Moshe Yalovsky, Apr 19 '13 at 18:12

score -1 · Answer 3 · answered Aug 09 '18 at 18:23

-1

I ran into this problem a few days ago and found this answer. converting to a sub from a function solved the problem. However, if I called the function in the immediate window, it performed as desired. thought that was odd.

answered Aug 09 '18 at 18:23

user2015833

1

Excel VBA assign value to named range from user defined function fails error 1004

3 Answers3