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?

 

[MARK858]

At a guess Jennifer one of the reasons would be to do with the processing resources needed, if you had functions in a 10000 cells all triggering actions on cells every time the sheet was calculated the resources needed would be huge and would kill the worksheet performance.

But like I stated it is only a guess.

 

[JenniferMurphy]

At a guess Jennifer one of the reasons would be to do with the processing resources needed, if you had functions in a 10000 cells all triggering actions on cells every time the sheet was calculated the resources needed would be huge and would kill the worksheet performance.

If I had a UDF call in 10,000 cells and those 10,000 cells didn't already bring the worksheet performance to its knees, would adding a command to select a cell make any difference?

Using this logic, Do loops should be banned. They would seem to pose a far greater threat to worksheet performance than one or two environment commands. I have written loops that were (unintentionally) infinite. After a couple, I learned to be more careful about the loop parameters.

 

[MARK858]

10,000 cells and those 10,000 cells didn't already bring the worksheet performance to its knees, would adding a command to select a cell make any difference?

Whether it is 100 or 10000 cells having an action actions carried out (virtually) simultaneously at each calculation would be a huge drain on resources.

Using this logic, Do loops should be banned. They would seem to pose a far greater threat to worksheet performance than one or two environment commands.

No they aren't as they do not act (virtually) simultaneously at every calculation, they act in series and much slower than a function.

 

[RoryA]

Suppose you have several different UDFs that all attempt to alter the environment - what precedence should you give each one? If a UDF selects a cell, should that trigger an event or not? If your UDF is volatile and selects cells, data entry would be nigh on impossible unless you remember to set calculation to manual first.

In any event, if you want to know why MS does/did anything, you'd really need to ask them. All we can do is guess.