BASIC runtime error. Sub-procedure or function procedure not defined when shading every other row

[MattWood]

I’m trying to create a macro to shade every other line in Libreoffice Calc.

Sub altRows
Dim i as integer

for i = 8 to 1001

Range(Cells(i , 1), Cells(i , 4)).Interior.Color = RGB(255, 255, 255)
i=i+1


Range(cells(i , 1), Cells(i , 4)).Interior.Color = RGB(217, 217, 217)
i=i+1
next

End sub

I’m getting an error BASIC runtime error.
Sub-procedure or function procedure not defined on the following line.

Range(Cells(i , 1), Cells(i , 4)).Interior.Color = RGB(255, 255, 255)

Any help would be appreciated

[Zizi64]

@MattWood

to shade every other line in Libreoffice Calc

Without macros - With styles and Conditional format feature:

Calc alternating row colors? LO 6.0.4.2

Hello, Solution 1: Reusable - Define your own Auto format Select range A1:D1 and set the color (e.g. white) Select range A2:D2 and set the alternate color (e.g. grey) Select range A3:D3 and set the color (e.g. white - step 1 needs to repeated here) Select range A1:D4 (important to have on uncoolorized line) (Updated as per comment of OP) Go to menu Format -> AutoFormat Styles Click Add - button, and deselect every option but not Pattern Provide a Name (e.g. AltWhiteGrey) Click button OK N…

[calc] alternating color for row pairs

Using conditional format (Menu/Format/Conditional) with a formula like: [image description] seems can do the job.

[karolus]

thats VBA-syntax you’ll need to insert above Sub altRows:

option VBASupport 1

1. dont change i inside the loop
2. rgb(255,255,255) doesnt make sense because its means no color but sophisticated

REM  *****  BASIC  *****
option vbasupport 1

Sub altRows
    for i = 8 to 1001 step 2
        Range(cells(i , 1), Cells(i , 4)).Interior.Color = RGB(217, 217, 217)
    next
End sub

[MattWood]

Hmm

I tried ```
Sub altRows
for i = 8 to 1001 step 2
Range(cells(i , 1), Cells(i , 4)).Interior.Color = RGB(217, 217, 217)
next

End sub

But am still getting BASIC runtime error.
Sub-procedure or function procedure not defined

on the line

Range(cells(i , 1), Cells(i , 4)).Interior.Color = RGB(217, 217, 217)

[karolus]

@MattWood

Hmm

I tried
…

Hmm…I wrote:

@karolus

thats VBA-syntax you’ll need to insert above Sub altRows:

option VBASupport 1

[MattWood]

Hi, thanks for your help on this…

i have added the option line and am still getting a runtime error

BASIC runtime error. ‘35’ Sub-procedure or function procedure not defined.
Additional information: Cells

option VBASupport 1
Sub altRows
for i = 8 to 1001 step 2
Range(Cells(i , 1), Cells(i , 4)).Interior.Color = RGB(217, 217, 217)
next

End sub

[karolus]

@MattWood

BASIC runtime error.

it works for me with:

Version: 7.4.7.2 / LibreOffice Community
Build ID: 40(Build:2)
CPU threads: 4; OS: Linux 6.6; UI render: default; VCL: gtk3
Locale: de-DE (de_DE.UTF-8); UI: de-DE
Debian package version: 4:7.4.7-1+deb12u2
Calc: threaded

but slow! …
much faster with python:

def set_color_on_odd_rows(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    sheet = doc.CurrentController.ActiveSheet
    bag = [ sheet[ row, :4].RangeAddress for row in range(8, 1001, 2) ]
    multi_ranges = doc.createInstance("com.sun.star.sheet.SheetCellRanges")
    multi_ranges.addRangeAddresses(bag, False)    
    multi_ranges.CellBackColor = int("d9d9d9",16)

[sokol92]

@karolus

but slow! …
much faster with python

This is a respected @karolus guru making such a joke.

Sub altRows
  Dim doc as Object, sheet as Object, multi_ranges As Object
  Dim i As Long, j As Long, bag, adr
  doc=ThisComponent
  adr = doc.CurrentController.ActiveSheet.rangeAddress
   multi_ranges = doc.createInstance("com.sun.star.sheet.SheetCellRanges")
  ReDim bag((1000 - 8)/2)
  For i = 8 to 1001 Step 2
    adr.StartColumn=0 : adr.EndColumn=3
    adr.StartRow=i-1  : adr.EndRow=i-1
    bag(j)=adr
    j=j+1
  Next i  
  multi_ranges.addRangeAddresses bag, False
  multi_ranges.CellBackColor = Rgb(217, 217, 217)
End sub

[karolus]

@sokol92

This is a respected @karolus guru making such a joke.

indeed, casting sheet[ row, :4].RangeAdress ~500 times is also slow, so:

import uno

def set_color_on_odd_rows(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    sh_index = doc.CurrentController.ActiveSheet.RangeAddress.Sheet
    bag = []
    color = int("d9d9d9",16) # some grey
    for row in range(8, 1001, 2):
        adr = uno.createUnoStruct("com.sun.star.table.CellRangeAddress")
        adr.StartColumn, adr.EndColumn, adr.Sheet = 0, 3, sh_index
        adr.StartRow = adr.EndRow = row
        bag.append(adr)
    multi_ranges = doc.createInstance("com.sun.star.sheet.SheetCellRanges")
    multi_ranges.addRangeAddresses(bag, False)    
    multi_ranges.CellBackColor = color