0

I have an User-Defined function in Excel Vba. I want to change the values of the cells in the range passed through as parameter using this function.

=ChangeCellValues(J18:J19)

When I call the function in formula editor as above in excel, the execution stops in the line where the assignment is performed and I get an value error in runtime. I have tried many syntactical options but no way.

However when I call the function in a main sub procedure, it works somehow and changes the values of the cells

Function ChangeCellValues(myrange As range) As Double
    Dim i As Integer
    For i = 1 To myrange.Rows.count
        'None of them works.
        mycell.Cells(i, 1).Value = 1
        mycell.Cells.Item(i, 1).Value = 1
        mycell.Item(i, 1).Value = 1
    Next i
    ChangeCellValues = 0
 End Function
 Sub Main()
     Debug.Print ChangeCellValues(range("J17:J18")) 'This works.
     range("J17").Value = 10 'This works also.
 End Sub

In essence, the question is how can I change the values of some cells in a worksheet passed through as argument in Excel Vba function?

jtxkopt
  • 916
  • 1
  • 8
  • 21
  • Using the *Function* keyword and even returning a result (`0`) doesn't make it a function. You are changing a value in a cell which makes it a method (sub). That is not the purpose of a UDF. – VBasic2008 Dec 10 '22 at 13:30
  • @VBasic2008 I am a novice and AFAIK I cannot call a sub in formula editor. This is why I use a function returning zero. – jtxkopt Dec 10 '22 at 13:35
  • @VBasic2008 Do you have an idea about my problem? Otherwise, I think the range object is immutable when it is passed through as parameter. – jtxkopt Dec 10 '22 at 13:37
  • 1
    Try to read [this post](https://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet) by Tim Williams. – VBasic2008 Dec 10 '22 at 13:42
  • 2
    VBA functions used as formula _cannot_ modify cells (value, color etc.). Only when you call it through VBA editor you can modify cells. – JohnyL Dec 10 '22 at 13:42
  • @JohnyL Thanks for the information. Now it is clear that why it doesn't work. – jtxkopt Dec 10 '22 at 13:46

0 Answers0