VBA output in a different cell

[kokopelli]

I have a function and I want it to output one word in the current cell and one in the cell to the right (i.e., the next column). Is there a way to write this in VB?

So, something along these lines (though this doesn't work):

Function test(word1 As String, word2 As String)
test = word1
test.Offset(0, 1) = word2
End Function

I suppose the general version of my question is how can I output an item in a cell other than the one the function is called from?

Thanks!

 

[ribbs2521]

You have the context right but you are using test. instead of ActiveCell. Change those and you should be good to go.

 

[Norie]

Well I think the general version of the answer to your question is you can't.

 

[kokopelli]

Thanks guys for your quick responses, but this just returns 0 when I call =test3("a","b")

Function test3(word1 As String, word2 As String)
ActiveCell = word1
ActiveCell.Offset(0, 1) = word2
End Function

Any other suggestions? Norie - is this really impossible? It seems like a pretty obvious functionality.

 

[Norie]

Theoretically I suppose it's not impossible.

But user defined functions (UDFs) aren't really designed to alter values on a worksheet, their main purpose is to return a value.

And that value is returned to the cell the UDF is in, not another cell.