Ask Your Question
1

Convert VBA to Basic LO

asked 2018-03-22 17:46:15 +0200

BDA5 gravatar image

updated 2018-03-23 11:25:52 +0200

Hello Everyone! I have a VBA macro that I need help converting to LibreOffice Basic. What the macro does is clear the content in a variable range and then display some dates in that same space. Here is the macro in VBA!

Further explanation about the macro: This macro was created to select a range starting in cell A20 of the worksheet "Consultar" and the last cell should be the last one in collumn A that has content (that's why it is a variable range). After selecting this range the macro deletes its content.

Write Dates Part: Here what it does is subtract to dates (G4 - G3 - 1 day) and this play the dates starting in cell A20 and going down. For example if G4 is 25/May/2018 and G3 is 21/May/2018 the macro displays in A20 21/May/2018; A21 22/May/2018; A22 23/May/2018; A23 24/May/2018. The date in G4 never appears in this range.

If you have any doubt about the macro let me know.

Thanks in advance for your help!

Sub WriteDates()

' Clear Contents
Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range

Set sht = Worksheets("Consultar")
Set StartCell = Range("A20")


' Find Last Row
LRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row

Range("A20:A" & LRow).ClearContents


' Write Dates
Dim rng As Range
Dim StartRng As Range
Dim EndRng As Range
Dim OutRng As Range
Dim StartValue As Variant
Dim EndValue As Variant
xTitleId = "KutoolsforExcel"
Set StartRng = Application.Selection
Set StartRng = Range("G3")
Set EndRng = Range("G4")
Set OutRng = Range("A20")
Set OutRng = OutRng.Range("A1")
StartValue = StartRng.Range("A1").Value
EndValue = EndRng.Range("A1").Value
If EndValue - StartValue <= 0 Then
Exit Sub
End If
ColIndex = 0
For i = StartValue To EndValue - 1
    OutRng.Offset(ColIndex, 0) = i
    ColIndex = ColIndex + 1
Next
End Sub
edit retag flag offensive close merge delete

Comments

Quoting @BDA5: "...clear the content in a variable range and then display some dates in that same space.."
That's yor view. As I wouldn't assume yous know LibO Basic at a high level, you shouldn't assume everybody her speaks Excel-VBA. I myself, e.g. don't at all. You will have to explain detailed what you want to achieve.
Please do it in clear words and sufficiently complete. Beyond that, concise is good.
Please edit your question for the purpose. (Don't post the explanation as an ans

Lupp gravatar imageLupp ( 2018-03-23 00:00:39 +0200 )edit

sorry for that. The explanation is added to the question. Thank you

BDA5 gravatar imageBDA5 ( 2018-03-23 11:26:44 +0200 )edit

2 Answers

Sort by » oldest newest most voted
2

answered 2018-03-23 12:36:23 +0200

Lupp gravatar image

updated 2020-01-21 13:01:42 +0200

Well, VB and the means usable from LibreOffice Basic look similar in some cases. However there are relevant differences lying in the depth of the design, and someone wanting to port VBA code to LibO should basically consider if this is the appropriate way. In most cases it's not, I would claim.

If you decide differently, you will have a next case. Thus you shold study the basic guide (here e.g.) and learn about the API, the usage of interfaces and services. What this is about you may learn from the famous texts by Andrew Pitonyak ( to find here).

Well, I could do it. But before I consider to help you this way, you will have to explaint to me for what reasons a solution without "macros" as demonstrated in this attached example is not applicable in your case. After all, using macros to make office documents work is basically evil, and only acceptable under rare conditions. That MS tells us otherwise is just a reinforcement of that claim in my view.

(The example will only work in a not too oöld version of LibO as it uses the IFERROR() function.)

=== Edit 2020-01.21 about 11:30UTC ===
(regarding the recently added answer and the related comments)

Since the LibreOffice API often is denigrated as something "incredibly complicated" I want to add a piece of code showing in what clear and simple way the task I retrieved from the VBA code in the question can be done in LibreOffice Basic if the API is properly used:

Sub WriteDatesUsingAPI()
doc             = ThisComponent
mySheet         = doc.Sheets.getByName("Consultar")
rangeToClear    = mySheet.getCellRangeByPosition(0, 19, 0, mySheet.RangeAddress.EndRow)
firstDate       = mySheet.getCellRangeByName("G3").Value
afterLastDate   = mySheet.getCellRangeByName("G4").Value
numDatesToWrite = afterLastDate - firstDate
If numDatesToWrite<1 Then Exit Sub
rangeToClear.clearContents(1+2+4+16) REM ordinary numbers, dates, texts, and formulas
outRange        = mySheet.getCellRangeByPosition(0, 19, 0, 19 + numDatesToWrite)
dA              = outRange.getDataArray()
oneDate         = firstDate
For i = 0 To numDatesToWrite - 1
  dA(i)(0)        = oneDate
  oneDate         = oneDate + 1
Next i
outRange.setDataArray(dA)
End Sub
edit flag offensive delete link more
0

answered 2020-01-20 21:10:31 +0200

tedtorres505 gravatar image

updated 2020-01-20 21:17:46 +0200

OK, a couple of things: First, any function that uses the XL-specific variables isn't going to work. Thats because they don't exist in LO basic.. So, your call to find the last row will not work. Here's an alternative way to get the last row: ( this starts from "A1", but you can modify)

 Sub findlastrow()
'
' findlastrow Macro
' T Torres'
'
' finds last occupied cell in column "A"
'
''

Dim c$, m$
Dim tempval As String
Dim lastrow As Integer
Dim x As Integer


lastrow = 1
    Range("A1").Select
    tempval = ActiveCell.Value

    Do While tempval <> ""
        lastrow = lastrow + 1
        c$ = "A" + Trim(Str(lastrow))
        Range(c$).Select
        tempval = ActiveCell.Value

    Loop
    m$ = "Last row is" + Str(lastrow - 1)
     x = MsgBox(m$, 0)
    '''' Debug.Print lastrow - 1
    ''  OR use Activecell.row to get row
    Rem  Debug.Print ActiveCell.Row - 1


End Sub

I don't normally use ranges very much. So, you have to "brute force" your way through it....

Second thing i've found is that any DISK functions (like Dir$) don't work as expected. i think this is due to the differing OS uses of folder identifiers... i.e "\" in Windows, "/" in Linux, ":" in MAC.

Third, built-in dialogs (file open, etc) don't seem to work.

BUT: Having said all that, VBA macros DO work in Libreoffice, you just have to spend some time seeing what "translates" and what doesn't.

edit flag offensive delete link more

Comments

Welcome @tedtorres505.
Since you are answering a nearly 2 years old question I had answered shortly after it was posted:
VBA support has improved since in LibO. Under Option VBAsuport 1 the given code is running and seems to do as expected if I read it correctly. (Tested with LibO V6.4.0.2RC, It looks rather funny to me, however.)

This shall not encourage anybody to run Excel-VBA under LibO, but in rare cases a module supporting VBA can help to ease some tasks.

Lupp gravatar imageLupp ( 2020-01-21 00:48:56 +0200 )edit

Agree, i jump through hoops to convert the range string to proper format, i have another that is much faster. I have been programming VB (and then VBA) for a long time. I'm interested in how well LO actually runs VBA code..... Since a lot of "converts" may have VBA code, and it would be good if LO runs it. It would help the case of those who want to convert. So I'm looking at it. I have both Excel and LO installed, and trying stuff. Found an issue today with LO not saving VBA userforms....(posted question today).

tedtorres505 gravatar imagetedtorres505 ( 2020-01-26 23:21:54 +0200 )edit

I'll be learning OO basic. downloaded the book this week.

tedtorres505 gravatar imagetedtorres505 ( 2020-01-26 23:22:34 +0200 )edit
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2018-03-22 17:46:15 +0200

Seen: 4,569 times

Last updated: Jan 21