How to Write the StarBASIC code and get the Answers in LibreOffice Like VBA Code below I given?

sv.thiyagarajan · August 26, 2023, 9:39am

Hai Friends,

Can you Help me how to write Macro Code in StarBASIC -Calc Macro For the Below VBA Code and get the Answers in Column F & Column G.

Please, See the below Screen Shot and Attahed Calc Document
Part 25 - Arrays.ods (22.6 KB)

Sub CalculateWithArray()

Dim FilmLengths() As Variant
Dim Answers() As Variant
Dim Dimension1 As Long, Counter As Long

Sheet1.Activate

FilmLengths = Range("D3", Range("D2").End(xlDown))
Dimension1 = UBound(FilmLengths, 1)

ReDim Answers(1 To Dimension1, 1 To 2)

Counter = 1
For Counter = 1 To Dimension1
    Answers(Counter, 1) = Int(FilmLengths(Counter, 1) / 60)
    Answers(Counter, 2) = FilmLengths(Counter, 1) Mod 60
Next Counter

Range("F3", Range("F3").Offset(Dimension1 - 1, 1)).Value = Answers

Erase FilmLengths
Erase Answers

End Sub

sokol92 · August 26, 2023, 11:52am

Note. If you put the first line of your macro

Option VbaSupport 1

and replace the line

Sheet1.Activate

to line

WorkSheets("Sheet1").Activate

then the macro will work in LibreOffice.
This testifies to the great work that the LO developers have done.

Of course, translation to the “native” object model leads to more efficient code.

sv.thiyagarajan · August 26, 2023, 12:08pm

Hi Sokol92,
Thanks Your Replay to me. But, I want to Re-Write Code in Native StarBASIC to Work With CALC.
Can you please, give me the Snippet Code…

Villeroy · August 26, 2023, 12:40pm

Then go ahead and do it.

karolus · August 26, 2023, 3:10pm

Simple python&native Api:

def simple_python_foo(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    sheet = doc.CurrentController.ActiveSheet
    cursor = sheet.createCursorByRange(sheet['D3'])
    cursor.collapseToCurrentRegion()
    
    cursor.collapseToSize(1, ( height:=cursor.Rows.Count ))
    out = []
    for entry in cursor.DataArray:
        out.append((divmod(entry[0],60)))
    
    cursor.gotoOffset(2,0) # 2→,0↓
    cursor.collapseToSize( 2, height )
    cursor.DataArray = out

sv.thiyagarajan · August 26, 2023, 4:05pm

Hi Karlous ,
Thanks for The Python Code…
But , I want Native StarBASIC - LibreOffice Calc - Macro…
…Any idea…

karolus · August 26, 2023, 4:18pm

This is ~95% native Api-syntax, if you can’t translate that into Basic, then you should stop your programming attempts!

sokol92 · August 26, 2023, 5:45pm

@karolus , thanks!
Let’s work as a VBA->Python->LO Basic translator.
One note: the design

Sheet1.Activate

means activating the sheet with CodeName Sheet1.

Option Explicit
Sub simple_basic_foo()
  Dim oDoc As Object, oSheet As Object, oCursor As Object, height As Long  
  Dim out, v, dataArray, i As Long
  oDoc = ThisComponent
  oSheet = oDoc.Sheets.getByName("Sheet1")
  oCursor = oSheet.createCursorByRange(oSheet.getCellRangeByName("D3"))
  oCursor.collapseToCurrentRegion()
    
  height=oCursor.Rows.Count-2
  oCursor = oSheet.createCursorByRange(oSheet.getCellRangeByName("D3")) ' Return to D3!
  oCursor.collapseToSize(1, height)
  ReDim out(height-1)
  dataArray=oCursor.dataArray
  For i=0 To height-1
    v=dataArray(i)(0)
    out(i)=Array(Int(v / 60), v Mod 60) 
  Next i
    
  oCursor.gotoOffset(2,0) ' 2→,0↓
  oCursor.collapseToSize(2, height)
  oCursor.DataArray = out
End Sub

sv.thiyagarajan · August 26, 2023, 6:37pm

100000 of …Thanks sokul92… great Efforts by You…

I just Added Extra Code for Selecting Mouse GuI Mode

For Easy Understanding, Mouse Coursor Selet Method (Gui Mode ) I used… Added Extra Code … For Easy Understanding… Purpose for All the People…

Note : Use Mouse Click by Clicking One By One → StepIntoF8 and Don’t use F8 in Key Board

Sub simple_basic_foo()
Dim oDoc As Object, oSheet As Object, oCursor As Object, height As Long
Dim out, v, dataArray, i As Long

  oDoc = ThisComponent
  oSheet = oDoc.Sheets.getByName("Sheet1")
  
  oDoc.CurrentController.setActiveSheet(oSheet)
  
  oCursor = oSheet.createCursorByRange(oSheet.getCellRangeByName("D3"))
  View = ThisComponent.CurrentController : frame = View.getFrame
  frame.Activate : View.Select(oCursor)
  
  oCursor.collapseToCurrentRegion()
  View = ThisComponent.CurrentController : frame = View.getFrame 
  frame.Activate : View.Select(oCursor)
    
  height=oCursor.Rows.Count-2
  oCursor = oSheet.createCursorByRange(oSheet.getCellRangeByName("D3")) ' Return to D3!
  View = ThisComponent.CurrentController : frame = View.getFrame
  frame.Activate : View.Select(oCursor)
  
  oCursor.collapseToSize(1, height)
  View = ThisComponent.CurrentController : frame = View.getFrame
  frame.Activate : View.Select(oCursor)
  
  ReDim out(height-1)
  dataArray=oCursor.dataArray
  View = ThisComponent.CurrentController : frame = View.getFrame
  frame.Activate : View.Select(dataArray)

  For i=0 To height-1
    v=dataArray(i)(0)
    out(i)=Array(Int(v / 60), v Mod 60) 
  Next i
    
  oCursor.gotoOffset(2,0) ' 2→,0↓
  View = ThisComponent.CurrentController : frame = View.getFrame 
  frame.Activate : View.Select(oCursor)
  
  oCursor.collapseToSize(2, height)
  View = ThisComponent.CurrentController : frame = View.getFrame 
  frame.Activate : View.Select(oCursor)

  oCursor.DataArray = out

End Sub

Thats All …Friends… Thanks Again.