0

Sorry if this is a super basic VBA question but I am very new to Excel. But suppose I have an input cell E6, where I can just put any number into it. I also have a cell S12, and its formula is defined in the worksheet and is complex (depends on ~15 other cell values, including E6). How can I write a VBA function that takes in one input X, put it into E6, and returns the value of S12?

I tried doing this

Public Function Neville(X As Double) As Double
    Range("E6").Value = X
    Neville = Range("S12").Value
End Function

but it is giving me value errors. I think it might be because changing the value of a cell in VBA does not make the value of other cells in the worksheet change?

braX
  • 10,905
  • 5
  • 18
  • 32
CharlieCornell
  • 111
  • 2
  • There is a way to do this using a sub that is called using worksheet.evaluate. It is exploiting a loop hole. See here for an example: https://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet But in general a udf called from a sheet, by itself, cannot change the value of a different cell. – Scott Craner Feb 28 '22 at 19:39

0 Answers0