LIBREOFFICE CALC MACRO not working.
I’m a complete newbie to macros in CALC BASIC language.
I have one whole column with a colour code in each cell: eg #0F320F (dark green)
my column is K, each cell is a different colour code in Hexadecimal.
(I do have another column with R,G,B values if that’s easier)
PURPOSE OF MACRO:
to work down the entire column K, cell by cell, and apply the background colour using the hex contents of the cell ie #0F320F
This is as far as I have got (with help from another forum)
This is based on VB and the syntax is not right for CALC BASIC.
HELP PLEASE!! I really need this.
Much appreciated!!
sub hexBgd4
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService(“com.sun.star.frame.DispatchHelper”)
rem ----------------------------------------------------------------------
rem dim args1(0) as new com.sun.star.beans.PropertyValue
Dim rCell As Object
Dim oSheet As Object
Dim i As Integer
Dim rowCount As Integer
oSheet = ThisComponent.Sheets(0)
REM rowCount = oSheet.getCellRangeByName("K1").End(4).Row + 1 // invalid VB syntax
rowCount = oSheet.getCellRangeByName("K1").End(x1Up).Row
For i = 1 To rowCount - 1
rCell = oSheet.getCellByPosition(10, i)
If Len(rCell.String) >= 7 Then
Dim red As Long
Dim green As Long
Dim blue As Long
red = CLng("&H" & Mid(rCell.String, 2, 2))
green = CLng("&H" & Mid(rCell.String, 4, 2))
blue = CLng("&H" & Mid(rCell.String, 6, 2))
rCell.CellBackColor = RGB(red, green, blue)
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "BackgroundColor"
args3(0).Value = rCell.CellBackColor
dispatcher.executeDispatch(document, ".uno:BackgroundColor", "", 0, args3())
End If
Next i
End Sub
–
Many thanks!