Modifying cells in Excel with a VBA function

Question

Every cell modifying line in the code below "throws" a 1004 error.

The following Function is called through a cell, this way: =bonjour()

Here is the code:

Public Function bonjour() As Integer
On Error GoTo Handler
    Range("B2").Value = 41
    Cells(2, 2) = 42
    ThisWorkbook.Sheets("Feuil1").Range("B2").Value = 43
    ThisWorkbook.Sheets("Feuil1").Cells(2, 2) = 44
Handler:
    If Err.Number <> 0 Then
        Debug.Print ("Error n° " & Err.Number & " - " & Err.Description)
        Err.Clear
        Resume Next
    End If
    bonjour = 45
End Function

errors are only handled for the immediate next line, so you would need that error handler in between each value... you are also missing .value or .text on the 2nd and 4th lines after the existing On Error statement. additionally, should not your resume next be the last step in your handler, not inside the if statement? — Cyril, Jul 26 '18 at 12:56
@Cyril - *errors are only handled for the immediate next line* - That is not correct. `On Error Goto xxx` will jump to `xxx:` at the first error it encounters within that Sub/Func until either the Sub/Func finishes or the error trap is cancelled. — CLR, Jul 26 '18 at 13:18

CLR · Accepted Answer · 2018-07-26T13:11:26.123

0

You cannot modify cells of the worksheet using a UDF (User Defined Function) called from the worksheet. Sorry. Excel will not allow this. Attempting it will cause an error every time.

From Microsoft Website :

A user-defined function called by a formula in a worksheet cell cannot change the environment of Microsoft Excel. This means that such a function cannot do any of the following:

Insert, delete, or format cells on the spreadsheet.

Change another cell's value.

Move, rename, delete, or add sheets to a workbook.

Change any of the environment options, such as calculation mode or screen views.

Add names to a workbook.

Set properties or execute most methods.

You need to find another way to call bonjour. Maybe using a button/shape or perhaps using Events.

edited Jul 26 '18 at 13:11

answered Jul 26 '18 at 13:00

CLR

11,284
1
11
29

Hi, by UDF, you mean a user defined function ? – Anon Jul 26 '18 at 13:02
Yes. I'll amend the answer. – CLR Jul 26 '18 at 13:05
Note that there actually are ways to modify cells using a UDF - e.g. https://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet - probably not recommended in this case. – BigBen Jul 26 '18 at 13:17
Okay, let me rephrase, it *is* possible, but notoriously unreliable. Nothing worse than building something that may or may not crash your Excel every time you use it! – CLR Jul 26 '18 at 13:22
Thank you @CLR, that answers my question. – Anon Jul 26 '18 at 13:28

Modifying cells in Excel with a VBA function

1 Answers1