0

As per official documentation the Worksheet.Range function should return the Range object. However, in my case, this function returns the Value of the specified cell.

Is there some configuration I'm missing? I'm using Excel 2016.

My super simple code:

Public Function fn(param) As Variant
    Worksheets(1).Range("A1").Value = "hi"
    fn = 1
End Function

Error message after running function: #VALUE! Error in Value

When I inputted the range of cells ("A1:D1") the function returned an array of cell values. I also tried assigning the range to a temporary variable and specifying its type as Range did not help:

Dim temp As Range
temp = Worksheets(1).Range("A1") 'Would break here

I also tried with ActiveWorksheet.Range and Sheets("name") and the results were the same.

Karma
  • 1
  • 1
  • 3
  • 9
Firedog01
  • 1
  • 1
  • Are you calling that `fn(param)` from a sheet cell? – GSerg Aug 13 '23 at 17:37
  • yes, I was hoping to use it in the function in the future – Firedog01 Aug 13 '23 at 17:39
  • You have two unrelated effects: changing sheets from a sheet and inadvertently using the [default property](https://stackoverflow.com/a/18051644/11683) of the Range object which is `Value`. Please refer to the links at the top. – GSerg Aug 13 '23 at 17:47

0 Answers0