VBA Output Write To Cell - #VALUE! Error

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


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

where B is some numerical value?



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


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.

Need Your Help

Apache HTTPD/mod_proxy/Tomcat and SSL with client auth

apache tomcat ssl mod-proxy mod-proxy-balancer

I'm sure this is an FAQ but I couldn't find anything I recognized as being the same question.

2-Dimension array of List in C#?

c# arrays list

I just want get a 2 dimension array of List in c#. In my thought, below should works but it didn't, the 1 dimension array of List works. I use Unity3D with Mono, however I think it's language related