excel - setting value of a cell based on activeCell in same sheet - Stack Overflow

admin管理员组

文章数量:1279015

i am new to excel and VBA and wanted to make some calculations then change the values of some cells that are located in same row as my ActiveCell based on ActiveCell value, this is my code which is not working and gives value error :

Function icalcs(MainCell as long)
dim cellV as long
cellV = 2+2
icalcs = cellV
ActiveSheet.cells(ActiveCell.Row, ActiveCell.Column - 2).Value = icalcs
End Function

i am new to excel and VBA and wanted to make some calculations then change the values of some cells that are located in same row as my ActiveCell based on ActiveCell value, this is my code which is not working and gives value error :

Function icalcs(MainCell as long)
dim cellV as long
cellV = 2+2
icalcs = cellV
ActiveSheet.cells(ActiveCell.Row, ActiveCell.Column - 2).Value = icalcs
End Function

• excel
• vba
• excel-2019

Share Improve this question Follow asked Feb 24 at 13:54 fardadfardad 2922 silver badges99 bronze badges 7

• 4 Unfortunately UDFs (User-Defined Functions), when called from a sheet cell, are not meant to modify other cells. – BigBen Commented Feb 24 at 13:55
• @BigBen thanks, so all hope is lost? – fardad Commented Feb 24 at 13:58
• 2 If you want to modify other cells, maybe the Worksheet.Change event or Worksheet.Calculate events would be helpful. – BigBen Commented Feb 24 at 13:59
• Don't put a world from legs to head, just call the function from the cell where you need to get the result. – rotabor Commented Feb 24 at 14:06
• 1 "Gives value error"... – BigBen Commented Feb 24 at 14:11

 |  Show 2 more comments

2 Answers 2

Sorted by: Reset to default 1

Here is the typical workaround for this type of question.

Note not "recommended workaround" since you are side-stepping a restriction of UDF which is in place for a reason.

Option Explicit

Function icalcs(MainCell As Long)
    Dim cellV As Long, c As Range, addr As String
    
    icalcs = MainCell * 3 'for example
    
    Set c = Application.Caller 'the cell with the formula calling this function
    addr = c.Offset(0, -2).Address(external:=True) 'two columns to the left of `c`
    'trigger the update
    Application.Evaluate "SetOther(""" & addr & """, " & icalcs & ")"
    
End Function

Function SetOther(addr As String, v)
    Debug.Print addr, v
    Range(addr).Value = v
End Function

You can use the subroutine instead of the function to modify all cells you need. You can run a subroutine manually by Alt+F8 or put a button to the sheet and associate the subroutine with the button.

You can create a function which outputs an array of values to provide multiple values as the result.

本文标签： excelsetting value of a cell based on activeCell in same sheetStack Overflow