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 : stackoverflow.com/questions/9476282/… — Shai Rado
– Shai Rado, Commented Sep 12, 2016 at 5:37

user3598756 · Accepted Answer · 2016-09-12 07:33:56Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related