“BASIC runtime error

English
,
#1

Hello,

I tried the following test macro in libreoffice, but get an error:

Sub VR5()
    Dim arrayvalue(100) As Integer
    Worksheets("Sheet1").Range("a1:a600"). ClearContents
    valuenum = 0
    rownum = 1
    Do
        valuenum = valunum + 1
        arrayvalue(valuenum) = Worksheets("Sheet1").Cells(valuenum, 2).Value
    Loop Until Worksheets("Sheet1").Cells(valuenum + 1, 2).Value = ""
    Do
        randomnum = (Int(Rnd * valuenum)) + 1
        Worksheets("Sheet1").Cells(rownum, 1).Value = arrayvalue(randomnum)
        rownum = rownum + 1
    Loop Until rownum = 53
End Sub

“BASIC runtime error.
Sub-procedure or function procedure not defined”

#2

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

1 Like
Help with converting VBA code to Calc code
#8

If someone would teach you how to use a spreadsheet efficiently, you would not need all that VBA madness. You could use your spreadsheet with any of todays spreadsheet applications.

#3

If I were solving a similar problem for Calc, I would write it down a little differently:

Sub copyShuffledDeck
Dim oSheets As Variant, oSheet As Variant 
Dim oColumns As Variant, oColumnA As Variant, oColumnB As Variant
Dim oFirstEmptyCell As Variant, nEndRow As Long
Dim oCellRangeByPosition As Variant, oDataArray As Variant, oSwapElement As Variant
Dim nextRnd As Long, i As Long
	oSheets = ThisComponent.getSheets()
	oSheet = oSheets.getByName("Sheet1")

	oColumns = oSheet.getColumns()
	oColumnA = oColumns.getByIndex(0)
	oColumnB = oColumns.getByIndex(1)
	
	oFirstEmptyCell = oColumnB.queryEmptyCells().getByIndex(0)
	nEndRow = oFirstEmptyCell.getRangeAddress().StartRow-1
	oCellRangeByPosition = oColumnB.getCellRangeByPosition(0, 0, 0, nEndRow)
	oDataArray = oCellRangeByPosition.getDataArray()

	Randomize
	For i = LBound(oDataArray) To UBound(oDataArray)
	  nextRnd = Int((nEndRow + 1) * Rnd) 
	  oSwapElement = oDataArray(nextRnd)(0)
	  oDataArray(nextRnd)(0) = oDataArray(i)(0)
	  oDataArray(i)(0) = oSwapElement
	Next i
	
	oColumnA.clearContents(1023)
	oColumnA.getCellRangeByPosition(0, 0, 0, nEndRow).setDataArray(oDataArray)
End Sub

Pay attention, I do not use the words Worksheets(), Range() or Cells() in the code - they are alien to Calc, Basic will partially (!) understand these terms if you is specifically pointed out to this “be like a VBA”. But I do not recommend doing this.
CardDeck.ods (12.1 KB)

1 Like
#4

Thank you so much for your help! I’m very new at this

#5

Fair point but it can be more complicated using *.xlsx files in both excel and calc. Sometimes just use the Duck test with whatever conventions you’re comfortable with.

#6

Just use the right tool, don’t replace it with a similar one (screwdriver for working with screws, hammer for nails, Calc and Basic for ODF spreadsheets, Excel and VBA for workbooks, etc.) This will avoid injury (physical and mental), save time and energy.

#7

I’m not naive enough to think I could just send anyone ods files.

#9

Thew native editor softwares of the ODF file formats (the Apache openOffice and the LibreOffice) are cost-free for everyone. Everyone can install one of them for free. The Excel is not a free software.

When you open an OOXML document (.docx, .xlsx) in the AOO or LO, the free office suites must convert the document at the open, and at the save procedure, because the AOO/LO will work in the ODF format, with the ODF properties: For example you can use the Page styles in a DOCX document. The MSO can not handle the Page Styles. They will be eliminated when you save a document into OOXML file formats.
Every conversion is has a possibility to lose some properties or even some content.
Note: there is not (never was and never will be) 100% compatibility between the different file formats.
NEVER store your important document in a foreign file format! Always use the native file formats of the editor software!
The worst case is, when you edit your important documents with different, and incompatible softwares.
Always use the INTERNATIONAL STANDARD Open Document Formats (ODF file formats: .odt, .ods) when you use the LibreOffice

#10

No, I assure you very few can install in a corporate environment.
.
Your warning is well made. I understand only a subset of features are supported on both platforms. Most business documents are fairly simple and although I’ve experienced problems many years ago (maybe OO) I’ve been able to use the documents on the platforms interchangeably for many years since. Obviously not complicated documents, and things like VBA macros will only work in excel. (Maybe we’ll get xlwings for LO one day.)
.
My point is purely based on market share, LO users need to be aware of MS-Office but MS-Office users don’t need to be aware of LO (unfortunately). I agree with the idea MS have no interest in fully supporting ODF.

That’s the conventional wisdom. I’m of the view to always use the alternate format to see what is not common to both platforms. Worst case, uncompress document and compare files to see the differences. I’d be happy to have an option to disable features that are not common.

#11

It is not a problem in my small Ltd. Our users can not use efficiently the expensive MSO, and our users cannot use efficiently the free LO. Then it is better the second case. :wink:

I wrote and manage all of our macros. All of our tasks are achievable by usage of the API of the LO.
And most of our partners can open the ODF file formats (We are using the 1.2 Extended version of the international ODF standard.)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.