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.
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
=SETVALUE(A2, 1)
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, at A2, 1. It need to be ;. Thanks!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