VBA Output Write To Cell - #VALUE! Error

Question

I'm trying to make a UDF of mine work (in Excel 2003), and after debugging the problem appears to be summarized in this condensed version of my function:

Function btest(b_1 As Double) As Double    
    btest = 1    
    Worksheets("Sheet1").Range("A1").Value = b_1
    '^this is the bit I want to work but doesn't^
    End Function

This simulates my real function, which has a value assigned to it with no problems before the following cell output line which doesn't execute. I believe this is related to the #VALUE! error I get as a return, even though I used a MsgBox which showed that the function did have a numeric value.

Can anyone shed light on this please?

Also: what is the difference between

Worksheets("Sheet1").Cells(1, 1) = B

and

Sheets("Sheet1").Range("A1").Value = B

where B is some numerical value?

Thanks

Answer 1

As you had already realised with

it looks like the problem is any UDF is not allowed to edit sheets, only return a single value...so if I want to edit another cell as part of the same process, I need to use a sub"

a standard UDF can't change the sheet.

But in terms of your follow-up comment

Is this correct, and if so, how would I go about that - sub within a function or function within a sub? I want my spreadsheet to automatically react to inputs as it would with a function - no buttons or special actions required.

You can use an Event

As an example:

• You want to track A1:A10 on a certain sheet for an input
• if this area is used you want to set Worksheets("Sheet1").Range("A1").Value to this value

Question 1

1. right click the tab of the sheet you want to track
2. View Code
3. Copy and Paste in the code below 4 Press altf11 to return to Excel

code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range
Set rng1 = Intersect(Target, Range("a1:10"))
If rng1 Is Nothing Then Exit Sub
Application.EnableEvents = False
Worksheets("Sheet1").Range("A1").Value = rng1.Value
Application.EnableEvents = True
End Sub

Question 2

They are identical.