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
- right click the tab of the sheet you want to track
- View Code
- 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
They are identical.