Help with converting VBA code to Calc code

[bobsmith79]

The following code works in Excel VBA but not in Calc LO Basic. The code moves a set of numbers (eg. 2-10 or 9-44) down one cell.
If you could please take a look and see what adjustments are needed. I can add the spreadsheet if that helps. Please let me know.
Thank-you.

• I’m using Cal Version: 6.4.7.2
• OS: Linux

Sub MOVE10PLUSDOWN()

    Dim rng As Range, tmp, i As Long
    'THE NUMBER SET COLUMN
    Set rng = Range("B23:B45")
    tmp = rng.Cells(rng.Cells.Count).Value
    For i = rng.Cells.Count To 2 Step -1
        rng.Cells(i).Value = Increment(rng.Cells(i - 1).Value)
    Next i
    rng.Cells(1).Value = Increment(tmp)

End Sub

'If value has a dash, increment the second number
Function Increment(v)
    Dim rv, arr
    rv = v
    arr = Split(v, "-")
    If UBound(arr) = 1 Then rv = arr(0) & "-" & CLng(arr(1)) + 1
    Increment = rv
End Function

[erAck: edited to format as code, see This is the guide - How to use the Ask site? - #6 by erAck ]

[sokol92]

Your situation is similar to the one discussed today and the answer is the same.

“BASIC runtime error

You stop getting this error if you add line Option VBASupport 1 at the beginning of the module.

[bobsmith79]

Hi sokol92

Sorry for the delay in getting back to you. Thank you for the suggestion but no luck with adding the line Option VBASupport 1 at the beginning of the module. I tried it with other VBA code I use as well with no luck.

It may be the use of “worksheets or Range” etc in the code.

[sokol92]

If you upload an example of your file, we will try to figure it out together.

[bobsmith79]

If you could please look at my spreadsheet. I must have an incorrect setting then. I’ve included the code in the macro area and
10 plus down.ods (46.4 KB)
have attached the file.
Thank you

[sokol92]

I did the following.

• Opened your file in Calc ver. 7.2.2.2 Ubuntu 20.04
• Menu Tools / Macros / Run Macro. Library: 10 plus down.ods / VBAProject / Module1. Macro name: MOVE10PLUSDOWN13. Press Run.

The macro runs and the cells in column B have changed.

[Villeroy]

works for me
t72306.ods (9.6 KB)

[sokol92]

For me too.

[bobsmith79]

Hi

I downloaded the sample and I looked at the code to make sure my code is the same. Ran the code and receive Basic runtime error ‘35’ Range error. the code gets highlighted at the following line:

Set rng = Range(“B23:B45”)

I don’t know what to say. I’m using version 6.1 are you using version 7?

[karolus]

Replace the typografic Quotes by normal "B23:B45" Doublequotes around the Range-address

[Villeroy]

I copied your code into a document library, attached the push button and added the Msgbox “Done” statement. Since this forum software adds typographic quotes to all text that is not explicitly tagged as source code, I know that I have to replace these artifacts with straight quotes. The Basic IDE highlights properly quoted strings in red font color. Your strings were not highlighted so they were no strings at all because of the typographic quotes.

IMHO, all that VBA is bullshit anyway. Either learn how to use a spreadsheet or do those stupid things with Excel instead of wasting my time. I do not expect you to learn programming.

[bobsmith79]

@bobsmith79

Set rng = Range(“B23:B45”)

Hi
I tried the following but same error:

1st try - Set rng = Range“B23:B45”
2nd try - Set rng = Range “B23:B45”
3rd try - Set rng = Range““B23:B45””

My last option is to try version 7 maybe they made some changes and that’s why I can’t get it to work or maybe just go back to Excel!!!

[Zizi64]

@bobsmith79

or maybe just go back to Excel!!!

This is the best way if you want to use the VBA codes. The LibreOffice has its own API. (API = Application Programming Interface. It is basicly different than the VBA.)
And you can use the API functions and procedures from many programming languages: StarBasic, Python, Java, etc…
You MUST rewrite all of your macros (based in the API functions) - if you want work with the Macros of the LibreOffice efficiently.

[bobsmith79]

Hi Zizi64
Thank you for your advice. Do you know anyone who could help with converting my VBA code?
Thanks

[Villeroy]

@bobsmith79

Hi
I tried the following but same error:

1st try - Set rng = Range“B23:B45”
2nd try - Set rng = Range “B23:B45”
3rd try - Set rng = Range““B23:B45””

My last option is to try version 7 maybe they made some changes and that’s why I can’t get it to work or maybe just go back to Excel!!!

None of that will work in Excel. Even VBA requires a bare minimum of programming skills and syntactically correct code.

[sokol92]

@bobsmith79

Do you know anyone who could help with converting my VBA code?

@bobsmith79, have you read this message?

Help with converting VBA code to Calc code

I did the following. Opened your file in Calc ver. 7.2.2.2 Ubuntu 20.04 Menu Tools / Macros / Run Macro. Library: 10 plus down.ods / VBAProject / Module1. Macro name: MOVE10PLUSDOWN13. Press Run. The macro runs and the cells in column B have changed.

[JohnSUN]

@bobsmith79

anyone who could help with converting my VBA code?

Almost every one of those who communicated with you in this topic and in others.
But we were expecting a different question: where should I start converting my VBA macros?

Sub MOVE10PLUSDOWN()
Dim oSheets As Variant, oSheet As Variant
Dim oCellRangeByName As Variant, oDataArray As Variant
Dim i As Long, tmp As Variant
	oSheets = ThisComponent.getSheets()
	If oSheets.hasByName("SHEET 1") Then
		oSheet = oSheets.getByName("SHEET 1")
		oCellRangeByName = oSheet.getCellRangeByName("B23:B45")
		oDataArray = oCellRangeByName.getDataArray()
		tmp = oDataArray(UBound(oDataArray))(0)
		For i = UBound(oDataArray) To LBound(oDataArray)+1 Step -1
			If Trim(oDataArray(i)(0))="" Then
				oDataArray(i)(0) = Increment(oDataArray(i-1)(0))
			EndIf
		Next i
		oDataArray(0)(0) = Increment(tmp)
		oCellRangeByName.setDataArray(oDataArray)
	EndIf
End Sub

As you can see, there are not so many differences - for example, the Increment() function does not need to be converted.

[bobsmith79]

Hi John

Thank you so much for your help. I’m retired and I’m trying to learn coding so please bear with me. I’ll spend today looking at your code. I ran it and it comes up with a run time error here:

oDataArray(i)(0) = Increment(oDataArray(i-1)(0))

Maybe it’s something I’ve done. I’ll review.

Just to review expected result the code should move the sets of numbers (eg. 3-55 or 1-20) down one cell and increase the last number by one number. So the above example the result would be 3-56 and 1-21. When a set of numbers is in cell B45 it gets moved to cell B23 and the second number in the set gets increased by one number. The process goes round and round.

Thank you again. If you have any questions please ask.