vba range("A1").value gives gives #Value! When entered in a function

Question

I want it return multiple values from a VBA function called from an Excel spreadsheet but always get the error #Value!

One example found on the web:

Sub ReturnCellValue()
    Worksheets("Sheet1").Range("A1").Value = 3.14159
End Function

This Function returns #Value!

Function ReturnCellValue()
    Worksheets("Sheet1").Range("A1").Value = 3.14159
End Function

This single line of code works in the immediate winnow.

Worksheets("Sheet1").Range("A1").Value = 3.14159

All of the examples I have found have been sub. How do you call a sub from a function?

None of the code you posted works. You cannot use `Sub` with `End Function` (it should be `End Sub`) and a `Sub` does not return a value (`Sub` is equivalent to `void` in C-style languages). — Dai, Jul 14 '17 at 20:01
A UDF can't have side-effects. Writing to the worksheet is a side-effect. Functions *return* a value. — Mathieu Guindon, Jul 14 '17 at 20:04

score 2 · Answer 1 · answered Jul 14 '17 at 20:03

This function:

Function ReturnCellValue()
    Worksheets("Sheet1").Range("A1").Value = 3.14159
End Function

Does not return a cell's value, instead it sets a cell's value (and VBA's = operator forms a statement, not an expression, so you cannot nest assignment).
Does not return any value because you're not returning a value (return a value in a VBA function by assigning-to the function's name).

You probably want this:

Function ReturnCellValue() As Variant
    ReturnCellValue = Worksheets("Sheet1").Range("A1").Value
End Function

1 Answers1