1

Consider this code:

Public Function HelloWorld() As String
    On Error GoTo ErrorHandler    
    Application.Workbooks("test.xlsm").Worksheets("Sheet1").Activate
    Application.Workbooks("test.xlsm").Worksheets("Sheet1").Range("A1").Value = "Hello World"
    HelloWorld = "Success"    
Exit Function

ErrorHandler:
    HelloWorld = Err.Description
End Function

When I run this within the developer's IDE with F5/F8 it will put "Hello World" in cell A1 as desired. But when I run this in the spreadsheet with "=HelloWorld()" I get the error "Application-defined or object-defined error".

What am I missing? Thanks in advance!

Community
  • 1
  • 1
Sam G.
  • 11
  • 3
  • 1
    UDF can't directly change other cells https://support.microsoft.com/en-us/help/170787/description-of-limitations-of-custom-functions-in-excel – Slai Jul 01 '18 at 15:31
  • You may execute actions restricted in UDF by scheduling, take a look [here](https://stackoverflow.com/a/23232311/2165759) – omegastripes Jul 01 '18 at 16:08
  • Thanks Slai for the reference. I didn’t realize this limitation. – Sam G. Jul 01 '18 at 16:41
  • 2
    @SamG. Not a limitation as much as way to help prevent paradoxical circular references and such. Same reason you can't have a formula that changes itself. – ashleedawg Jul 01 '18 at 18:44

0 Answers0