How to set the active cell from a UDF?

[JenniferMurphy]

I am working on a UDF that processes a large table of data. If it detects an error, I would like it to make that the active cell on return to the sheet. Here's my code that isn't working. I've simplified the code, but I think all the relevant parts are included. The statement in question is the next to last one.

Public Function MyFun(Data as Range) As Variant
Dim errmsg As String
Dim RngColBeg As Long
Dim RngColEnd As Long
RngColBeg = 1
RngColEnd = Data.Columns.Count

Dim iCol As Long
Dim CellAddr As String
For iCol = RngColBeg To RngColEnd
   CellAddr= Data(1, iCol).Address
      . . .
  If (some error occurs) Then
    errmsg = "...text of error message..."
    MyFun = ErrCode(errmsg,CellAddr): Exit Function
  End If
      . . .
Next iCol
   . . .
End Function


Public Function ErrCode(errtxt as string, celladdr as string) As Variant
   . . .
Dim errmsg as string
errmsg = (standard error message including the cell address)
Dim Button As Long
Button = MsgBox(errmsg, vbYesNoCancel + vbDefaultButton2)
   . . . 

ErrCode = (some error code)
ActiveSheet.Range(pErrCellAddr).Select

End Function

Can someone suggest a working solution?

 

[footoo]

A function cannot select a cell, it can only return a result (unless called by a macro).

 

[JenniferMurphy]

A function cannot select a cell, it can only return a result (unless called by a macro).

Well, I guess that's why it didn't work. Rats!

Do you know why the geniuses at M$FT designed it that way? Would something horrible happen if it was allowed?

Why doesn't that statement get an error to let me know it isn't going to work? Would that make VBA less cryptic and too easy to use?

Thanks

 

[footoo]

Do you know why the geniuses at M$FT designed it that way? Would something horrible happen if it was allowed?

All worksheet functions (not just UDF's) work that way. That is, they cannot change the worksheet environment (e.g. formatting, changing the selection, etc.)
For example, with the SUM function you cannot have it select another cell or italicize the result if it doesn't produce a predetermined total.

 

[JenniferMurphy]

All worksheet functions (not just UDF's) work that way. That is, they cannot change the worksheet environment (e.g. formatting, changing the selection, etc.)

OK, but why?

For example, with the SUM function you cannot have it select another cell or italicize the result if it doesn't produce a predetermined total.

What do you mean, "if it doesn't produce a predetermined result"?

It seems to me that I cannot make SUM do anything other than add up the contents of some cells. With the built-in functions, like SUM, they do what they do. But with UDFs, they do what I tell them to do. So why can't I tell them to select a cell (for example)?

 

[footoo]

OK, but why?


What do you mean, "if it doesn't produce a predetermined result"?

It seems to me that I cannot make SUM do anything other than add up the contents of some cells. With the built-in functions, like SUM, they do what they do. But with UDFs, they do what I tell them to do. So why can't I tell them to select a cell (for example)?

For the same reasons that built-in functions can't do it. If you want to change the worksheet environment, you need a macro - not a function. Functions return results.

 

[footoo]

Have a look here : https://support.microsoft.com/en-hk...n-of-limitations-of-custom-functions-in-excel

 

[JenniferMurphy]

For the same reasons that built-in functions can't do it.

And what are those reasons?

If you want to change the worksheet environment, you need a macro - not a function. Functions return results.

You are giving me the rule, not the reason behind the rule. Why did M$FT decree that macros can change the environment, but functions cannot?

 

[footoo]

See post # 7

 

[JenniferMurphy]

Have a look here : https://support.microsoft.com/en-hk...n-of-limitations-of-custom-functions-in-excel

That's a very helpful reference, thanks.

But it still does not tell me why this is how it is.

This is like me getting pulled over for going 45 in a 35 zone. The exchange between me and the officer goes like this:

• M: Why did you pull me over.
• O: You were going 45 in a 35 zone.
• M: Why can't I go 45?
• O: Because the speed limit here is 35.
• M: Why it is 35?
• O: Because that's what it says on the sign.
• M: But why does it say that?
• O: Because that is the speed limit in all zones like this one.
• M: But why is the speed limit 35 in zones like this one?
• O: Because that's what the law says.
• M: But why does the law say that?
• O: Because that's what the legislature passed.
• M: But why did they pass that law?
• O: Because they got a majority of the votes.

At no point did the cop say, "because studies have shown that going over 35 is unsafe in areas like this". That's the "why". All of the other answers were the "what".

So now I ask again: Why is there a rule that functions cannot alter the worksheet environment?