CODEWITHSUNDEEP
excelvba
user2342380
user2342380

Reputation: 33

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

and

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

where B is some numerical value?

Thanks

Upvotes: 3

Views: 13039

Answers (2)

brettdj
brettdj

Reputation: 55692

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

Upvotes: 1

chris neilsen
chris neilsen

Reputation: 53136

You are right (in your comment) that a UDF cannot change a sheet, only return a value.

This holds true event if you call a Sub or Function from the UDF and try to have that change the sheet. It will fail too.

Note: there is a (quite ugly) work around: see this answer

Upvotes: 0

Related Questions