3

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

Improve this question
8
  • do you want to call your functions from VBA code or from Sheet cell? Commented May 2, 2013 at 9:17
  • additionally you could check this, too Commented May 2, 2013 at 9:19
  • 2
    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. Commented May 2, 2013 at 10:08
  • 1
    yes, you can't manipulate cells from function if you call this function from excel cell... if you need to return value to more than one cell use Sub. If you than need to do it as a result of any action you can explore events. Commented May 2, 2013 at 10:23
  • 1
    Thanks very much everyone. A good answer is link which has inspired me to call the function in a sheet-specific subroutine so it runs whenever Target is altered. Commented May 2, 2013 at 13:39

2 Answers 2

Reset to default
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 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.

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

Comments

0

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

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.