1

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.

Improve this question
2
  • 1
    Are you calling this function from the worksheet formula? Commented Apr 18, 2013 at 23:13
  • Yes, by entering =CourseSetup(+srof) Commented Apr 19, 2013 at 7:06

3 Answers 3

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This was the behavior I saw but needed your confirmation! It does make sense from a security stand point Thank you
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.

Improve this answer

1 Comment

This was the behavior I saw but needed the confirmation. Thanks!
-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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.