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

Community
  • 1
  • 1
user2342380
  • 33
  • 1
  • 1
  • 4
  • do you want to call your functions from VBA code or from Sheet cell? – Kazimierz Jawor May 02 '13 at 09:17
  • additionally you could check [this](http://stackoverflow.com/a/15659852/2143262), too – Kazimierz Jawor May 02 '13 at 09:19
  • from another Sheet cell, e.g. A2 – user2342380 May 02 '13 at 09:55
  • 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. – user2342380 May 02 '13 at 10:08
  • 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. – user2342380 May 02 '13 at 10:09
  • 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`. – Kazimierz Jawor May 02 '13 at 10:23
  • possible duplicate of [Set a cell value from a function](http://stackoverflow.com/questions/15659779/set-a-cell-value-from-a-function) – chris neilsen May 02 '13 at 11:22
  • 1
    Thanks very much everyone. A good answer is [link](http://stackoverflow.com/questions/15659779/set-a-cell-value-from-a-function) which has inspired me to call the function in a sheet-specific subroutine so it runs whenever Target is altered. – user2342380 May 02 '13 at 13:39

2 Answers2

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.

brettdj
  • 54,857
  • 16
  • 114
  • 177
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

Community
  • 1
  • 1
chris neilsen
  • 52,446
  • 10
  • 84
  • 123