Can anyone tell me why this particular function doesn't work as a formula on a spreadsheet but it works fine in the immediate window?
Public Function test() As String
Range("A1:A3").Value = Application.WorksheetFunction.Transpose(Array("Apple", "Banana", "Orange"))
test = "Completed"
End Function
If, by "doesn't work as a formula on a spreadsheet ", you mean calling it as a formula in a cell (AKA: A User Defined Function or UDF), then UDFs cannot alter any cell other than the one they were called from. Comment out the Range line and it will return 'Test' perfectly well.
Last edited by cytop; 05-24-2016 at 09:29 AM.
Thanks, I actually didn't even know that!
When called from a spreadsheet formula, functions can return values, but can't do anything to cells.
Funcitons can't set cell values.
What you could do is write a UDF like
Then select A1:A3 and enter the array formula =Transpose(MyTrial) with Ctrl-Shift-Enter.
Function MyTrial() As Variant MyTrial = Array("Apple", "Banana", "Orange") End Function
Or A1:C3 and the formula =MyTrial
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks