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.

Answer 1

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

Answer 2

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