Returning multiple values from a worksheet function

  • Thread starter Thread starter Steve Lloyd
  • Start date Start date
S

Steve Lloyd

I am trying to return multiple cell values from a single worksheet function
but when i specify the cell value I get an error.

Can anyone tell me if this is possible and guide me in the right direction.

example function is below:

Public Function ReturnValues() As Variant

On Error GoTo Err_Values

ActiveCell.Offset(1, 0).Value = 12

ReturnValues = 11

Exit Function

Err_Values:
MsgBox Err.Description

End Function
 
Steve,

You can't change the value of any other cell than the cell with the
function call - your offset statement will simply be ignored. But you
can do it - using array formulas.

Before you do anything, you need to change the function definition to

Function ReturnValues() As Variant
If Application.Caller.Rows.Count = 1 Then
ReturnValues = Array(11, 12)
Else: ReturnValues = Application.Transpose(Array(11, 12))
End If
End Function

To use two cells at once, you need to array enter your function:
select two cells, type =ReturnValues() in one, and use
Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP
 
Thanks for that Bernie, is there anyway of automating the Ctrl+Shift+Enter
opertaion.
 
I am trying to return multiple cell values from a single worksheet function
but when i specify the cell value I get an error.

Can anyone tell me if this is possible and guide me in the right direction.

example function is below:

Public Function ReturnValues() As Variant

On Error GoTo Err_Values

ActiveCell.Offset(1, 0).Value = 12

ReturnValues = 11

Exit Function

Err_Values:
MsgBox Err.Description

End Function
Click to expand...

A Function returns a value. You are trying to alter a cell.

You could do it with a Sub, or you could have the Function return an array of
values, and use the UDF as an array function.


--ron
 
Steve Lloyd said:
Thanks for that Bernie, is there anyway of automating the Ctrl+Shift+Enter
opertaion.
Click to expand...

No. It's only two extra keys. :)

HTH,
Bernie
MS Excel MVP
 
Back
Top