Application.Transpose inside Macro Function doesn't return any value

[srvnrsprm]

I am trying to write a month index to name converter. It must return the entire array if no arguments are passed, only the month name if a valid index is passed or a transposed vertical array if negative index is passed.

The first two cases are working good but when i pass -1 as an argument, it doesn’t return anything at all. I can pinpoint Application.Transpose is not returning anything. So the cell is empty after function invocation.

Function MonthNames( Optional Mindex )
	Dim Allnames As Variant
	Allnames = Array( "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" )
	Select Case IsMissing( Mindex )
		Case True
			MonthNames = Allnames
		Case False
			Select Case Mindex
				Case Is >= 1
					monthval = ((Mindex - 1 ) Mod 12 )
					MonthNames = Allnames( monthval )
				Case Else
					MonthNames = Application.Transpose( Allnames)
			End Select
	End Select
End Function

I have Option VBASupport 1 set on the top of the script.

[mikekaganski]

Don’t you also have something like On Error Resume Next, to hide the errors? I don’t see an implementation (even a stub) of Application.Transpose - so I’d expect an error, not a silent nothing.

[Villeroy]

Why don’t you use the spreadsheet for this conversion? With a lookup vector and a simple formula, there are no compatibility issues.

[mikekaganski]

The call results in an array of arrays. It may be different from what VBA call would return, which would be a bug (Application is a VBA compatibility object, and any difference in its behavior is a bug). But anyway, you can inspect the return result in the IDE, setting a breakpoint at the very end of the function, and see. Possibly you might want to post-process the result to make it a 2D (1x12)-array.

[Lupp]

As far as I could test (not knowing the profile of the questioner) I didn’t get anything like the reported error.
Yet I would tend to recommend one of the many alternative solutions not needing any UDF. See:
disask112541FormattedDatePartsByMacroOrByFormulasAndStyles…ods (20.7 KB)
Anyway I didn’t understand how “Application.Transposed” was meandt. Is it about a VBA construct not contained in the posted cde.
Well, maybe that VBA also can’t transpose a 1-index-array. (I also don’t know how Excel functions are correctly called in a Excel VBA script. In fact I think a discussion about this peculairity would be displaced in this forum. Transposition can easily be applied in the spreadsheet formula.

[Villeroy]

Application.Transpose calls Excel’s sheet function TRANSPOSE.

[Lupp]

Do you also know if this can be applied to single-index-arrays? (It’s illogical, of course, but MS might not worry.)

[Villeroy]

MS never worries about anything. objCell.Value may return string, double, date or boolean. I don’t remember how exactly Application.Transpose implicitly converts the input, but I’m confident that it does.

[Villeroy]

2D_arrays.ods (14.0 KB)
As you might know, it is Calc which passes 2D arrays to Basic functions instead of nested DataArrays. In fact, it is some Excel object when VBASupport is enabled.
This makes everything complicated and incompatible by design because the Application.Transpose that is implemented in LibreOffice may handle either one of the array types but not both.

EDIT: Application.Transpose takes nested data arrays and returns nested data arrays
EDIT2: sample file with a user defined array function:
vb_transpose2.ods (18.2 KB)