Writing Value to a Cell which is not calling the function, via VBA Function

Question

I'm wondering if it is possible to write values to a cell in Excel via a VBA Function?

Aka neither line in the following function will call:

Function OffsetValue(myinput As Double) As Double
    ThisWorkbook.Sheets("Sheet1").Cells(1, 1).Value = myinput
    Call OffsetValueSub(myinput)
End Function

Sub OffsetValueSub(myinput As Double)
    ThisWorkbook.Sheets("Sheet1").Cells(1, 1).Value = myinput
End Sub

But The following Dummy Sub will work correctly:

Sub Dummy()
    Call OffsetValueSub(100)
End Sub

Sub OffsetValueSub(myinput As Double)
    ThisWorkbook.Sheets("Sheet1").Cells(1, 1).Value = myinput
End Sub

Is there something one has to do to be able to write to a cell which is not the cell which is calling the specific function?

Already covered here, search this link : http://stackoverflow.com/questions/9476282/cannot-vba-write-data-to-cells-in-excel-2007-2010-within-a-function — Shai Rado, Sep 12 '16 at 05:37

user3598756 · Accepted Answer · 2016-09-12T07:33:56.093

may be this workaround could do for you:

shorten down your OffsetValue function to:

Function OffsetValue(myinput As Double) As Double
    OffsetValue = myinput '<--| this will trigger worksheet 'Change' event
End Function

place the following sub in the code pane of the worksheet you want to use Function OffsetValue() in:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ExitSub
    Application.EnableEvents = False
    Call OffsetValueSub(Target.Value) '<--| call 'OffsetValueSub()' and write input value where it has to
    Target.ClearContents '<--| should you ever want to erase any trace of the OffsetValue function
ExitSub:
    Application.EnableEvents = True
End Sub

if you don't want to remove the call to OffsetValue() from the cell you wrote it in then just remove/comment Target.ClearContents

this is just a base code that you may want to enhance with:

some checking of the Target range passed in to Worksheet_Change()

you may want to check if it matches specific Value (e.g: checking Target.Value against some value range) or Range (e.g: checking Target range itself belonging to a predefinedRange) criteria
handle the same need throughout the whole workbook

then you have to switch to Sub ThisWorkbook_Change() event handler use in ThisWorkbook code pane

Writing Value to a Cell which is not calling the function, via VBA Function

1 Answers1