New function in Excel using VBA

Question

For first of all, this is my first time when try to use vba in excel. I know nothing about it.

I try to create a new function, into excel, to set a value in a specific cell.

I have this module:

Public Function SETVALUE(cell As Range, newValue As Integer) As String

    cell.value = newValue

    SETVALUE = "-"

End Function

This is my module:

If I type: =SETVALUE(A2, 1) in cell A1, it not work.

In cell A1 appear #Value! and in cell A2 appear nothing.

A function called by the worksheet cannot modify the value of another cell. — Scott Craner, Jul 26 '19 at 19:36
I would say it's probably not a good idea to do it but it's possible, see [here](https://stackoverflow.com/questions/55990057/udf-how-to-turn-a-string-that-contains-a-path-workbook-name-worksheet-name-a/55993127#55993127) — Storax, Jul 26 '19 at 19:39

Storax · Accepted Answer · 2019-07-26T19:55:41.977

1

Try this

Option Explicit

Public Function SETVALUE(cell As Range, newValue As Integer) As String

    Evaluate "mySetValue( " & Chr(34) & cell.Address & Chr(34) & "," & Chr(34) & newValue & Chr(34) & ")"
    SETVALUE = "-"

End Function


Sub mySetValue(cell As String, newValue As Integer)
    Dim rg As Range
    Set rg = Range(cell)
    rg.Value2 = newValue
End Sub

Update: Image how to use it

edited Jul 26 '19 at 19:55

answered Jul 26 '19 at 19:48

Storax

11,158
3
16
33

The formula you typed contains an error: `=SETVALUE(A2, 1)` – KunLun Jul 26 '19 at 19:51
What do you mean? – Storax Jul 26 '19 at 19:52
I type that formula into `A1` and hit enter and get that error: `The formula you typed contains an error`. Also, in module, after type `Range` it change into `range` – KunLun Jul 26 '19 at 20:01
My mistake I put `,` at `A2, 1`. It need to be `;`. Thanks! – KunLun Jul 26 '19 at 20:02

score 0 · Answer 2 · answered Jul 26 '19 at 20:58

See here this article that says:

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.

The solution is to pass by a sub-routine Like this

Public Function SETVALUE(cell As Range, newValue As Integer) As String

Dim updateCell As String

SETVALUE = "-"

updateCell = "updateRange(" & cell.Address(False, False) & "," & newValue & ")"
Evaluate (updateCell)

End Function

Sub updateRange(vCell As Range, vNewValue As Integer)

vCell = vNewValue

End Sub

New function in Excel using VBA

2 Answers2