How to get the results in LibreOffice Macro while VBA - Spliting Cell String into Rows got Results?

sv.thiyagarajan · January 17, 2024, 2:43pm

Hi Friends,
I want to get the results from Libre office Calc Macro Using below VBA Macro Function.
Please Help …

VBA - Function Code

Function SplitCellToRows(CellValue As Range, Delimiter As String)
    Dim SplitValues() As String
    
    'Split the value by the specified delimiter into an array
    SplitValues = Split(CellValue.Value, Delimiter)
    
    'Go through each element of the array and remove any leading or trailing spaces
    For i = LBound(SplitValues) To UBound(SplitValues)
            SplitValues(i) = Trim(SplitValues(i))
    Next i
    
    'Return the array
    SplitCellToRows = WorksheetFunction.Transpose(SplitValues)
    
End Function

and Results Screen sShots

karolus · January 17, 2024, 3:48pm

function udf_split( var as string, delim as string)
	result = split(var, delim)
	for i =  0 to ubound(result)
		result(i) = trim(result(i))
	next
	udf_split = result
end function

You may use it alone with ctrl+shift+enter

=udf_split(A2; ",")

or with transpose around:

=transpose(udf_split(A2; ",")  )

sv.thiyagarajan · January 17, 2024, 4:00pm

Hi @karolus , did you get result ? because, I got error message…and it did not show the function in LibreOffice Calc cell ie = udf_split … did not show…

karolus · January 17, 2024, 4:08pm

DID YOU READ AND UNDERSTAND ctrl+shift+enter

yes its proved:
MTRANS is german localised TRANSPOSE

sv.thiyagarajan · January 18, 2024, 7:30am

Hi Karolus , Still i got error

sv.thiyagarajan · January 18, 2024, 7:31am

HI @karolus , Is this code Ok… and but did not get result…

Function SplitCellToRows(Cell As Object, Delimiter As String)
		Dim SplitValues() As String 
    
	    'Split the value by the specified delimiter into an array
	    SplitValues = Split(Cell.String, Delimiter)
	    
	    'Go through each element of the array and remove any leading or trailing spaces
	    Dim i As Long
	    For i = LBound(SplitValues) To UBound(SplitValues)
	            SplitValues(i) = Trim(SplitValues(i))
	    Next i
	    
	    
	    theSelection = ThisComponent.CurrentSelection
    	SplitCellToRows = theSelection.setFormula("Transpose(SplitValues)")	
End Function

sv.thiyagarajan · January 18, 2024, 7:36am

Whil copying your code and paste it in my Calc Macro it does not show the IntelliSense…

karolus · January 18, 2024, 8:54am

???

sv.thiyagarajan · January 18, 2024, 9:45am

Is this Code Correct ? or any line made mistake …?