0

Trying to enter cell value in excel but the code is giving value error. Can't under stand why. The subroutine "write_cell" runs fine if there is no argument with it but gives error if there is any argument 

Function Clone_test(num As Integer) As Integer

Dim rng As Range
Dim result As Integer

Set rng = Range("B4")

write_cell rng, num

result = num
Clone_test = Cells(4, "D").Value

End Function

Private Sub write_cell(ByRef cell_rng As Range, ByVal n As Integer)

Dim rng As Range
Set rng = cell_rng

rng.Value = 45    '**this line is creating problems**

MsgBox n

End Sub
skipper ranjan
  • 1
  • How are you calling this? Side note: use `ByVal` instead of `ByRef`, and use `CamelCase` instead of `snake_case` – BigBen Feb 01 '20 at 18:41
  • It depends on how are you calling it. For example `Ret = Clone_test(5)` will not give an error – Siddharth Rout Feb 01 '20 at 18:42
  • 1
    And if you are calling it from an excel cell for example `=Clone_test(5)` then yes, you will get a #Value error as you cannot write to a Cell from inside a UDF... [Well Almost](https://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet) – Siddharth Rout Feb 01 '20 at 18:45

0 Answers0