How can I get a function to output multiple variables?

[spurs]

I need to use an excel function instead of a sub because using a function allows Excels internal goal seek to still work.

My desired function - simplified here has in this case 2 inputs but I not only want the function to output in the active cell but also to other cells

Here is my intent but it does not work
If I comment out the 2 lines with "Sheets" it obviously returns the value test into the active cell
But i need a function to also output to 2 other cells.
How can I achieve that?

Function Test(A,B)
Test = A * B
Sheets("Sheet1").Range("B1") = A ^ 2
Sheets("Sheet1").Range("B2") = B ^ 3

End Function

In my real function, the calculations are much more complex and iterative, so its not possible just to do cell entries

 

[Bosquedeguate]

I did some checking on the internet. I found this Stack Overflow article where someone was asking about functions that impact multiple cells. Here's segment of the response.

It can't be done, which makes sense because:

• When a worksheet function is called, the cell containing the function is not necessarily the active cell. So you can't find the adjacent cell reliably.
• When Excel is recalculating a worksheet, it needs to maintain dependencies between cells. So it can't allow worksheet functions to arbitrarily modify other cells.

The best you can do is one of:

• Handle the SheetChange event. If a cell containing your function is changing, modify the adjacent cell.
• Put a worksheet function in the adjacent cell to return the value you want.

so your SheetChange event could trigger a Sub (macro) that outputs to multiple cells.

I worked with your code a bit and could not get it to work as a function.

So, here is code that works using an event handler:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim sheet As Worksheet
  If Target.Row = 1 And (Target.Column = 3 Or Target.Column = 4) Then
    Set sheet = Target.Parent
    With sheet
      .Range("B1") = .Range("C1") ^ 2
      .Range("B2") = .Range("D1") ^ 3
    End With
  End If
End Sub

 

[spurs]

The problem with a subroutine is that you cannot use built in excel goal seek to get to a result form a differnt input

 

[Bosquedeguate]

Here is a way that might work for you ...
Put your results into a comma delimited string and store this string in a cell.
Then parse the cell for your desired results.

In the example below the result comma delimited string is in column A, the parsed value are in Columns B and C
Column A: =MyFunc(D2,E2)

Results	X2	Y3	X	Y
4,27	4	27	2	3

Function MyFunc(x As Range, y As Range)
   MyFunc = x ^ 2 & "," & y ^ 3
End Function

 

[spurs]

I got this to work

unction Test(A, B)
Dim First, Second, Third As Variant
First = A * B
Second = A ^ 2
Third = A ^ 3

Test = First & ", " & Second & ", " & Third

End Function

the result is 35,25,125
How do i parse that

 

[spurs]

Here is a way that might work for you ...
Put your results into a comma delimited string and store this string in a cell.
Then parse the cell for your desired results.

In the example below the result comma delimited string is in column A, the parsed value are in Columns B and C
Column A: =MyFunc(D2,E2)

Results	X2	Y3	X	Y
4,27	4	27	2	3

Function MyFunc(x As Range, y As Range)
   MyFunc = x ^ 2 & "," & y ^ 3
End Function

The problem is that in cell A7 I h
=Test(A4,A5)
visually i get 35,25,125 on the scree while the formula in the cell is =Test(A4,A5)

How do I delimit the result and not the formula ?

 

[Bosquedeguate]

Here is a simple (no frills) function to parse the result string

Function GetValue(rng As Range, index As Integer)
'assumes that range is a comma delimited string
'first parsed value has an index of 0 (...1,2, ...)
  Dim tmp
  
  tmp = Split(rng, ",")
  If index > UBound(tmp) Then
    MsgBox "index is out of range"
  Else
    GetValue = tmp(index)
  End If
End Function

GetLastRow.xlsm
	A	B	C	D
4	35,25,125	35	25	125
Sheet1

Cell Formulas
Range	Formula
B4	B4	=Getvalue($A4,0)
C4	C4	=Getvalue($A4,1)
D4	D4	=Getvalue($A4,2)

 

[spurs]

Thank you
That solution works perfectly and keeps the goal seek function operational
Its working great

Best Regards !

 

[Bosquedeguate]

Glad I could help. There are better ways to create your result string; take a look at the VBA JOIN function.
But if you only have two or three values to concatenate the code you have is fine.

Please flag your post as resolved or completed!