CODEWITHSUNDEEP
excelrangenamedvba
Moshe Yalovsky
Moshe Yalovsky

Reputation: 183

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

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.

Upvotes: 1

Views: 10392

Answers (3)

user2015833
user2015833

Reputation: 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.

Upvotes: -1

David Zemens
David Zemens

Reputation: 53623

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

Upvotes: 2

Charles Williams
Charles Williams

Reputation: 23520

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.

Upvotes: 2

Related Questions