VBA Module works with excel upload but doesn't work with Libreoffice Calc

English
#1

The following module works in Excel and it works in the Excel Spreadsheet uploaded into LibreOffice. It does not work when copied to a Spreadsheet created in LibreOffice Calc.
I get an error message “BASIC runtime error.
‘35’
Sub-procedure or function procedure not defined.
Additional information: Sheets”.
What needs changed to make this work?

Rem Attribute VBA_ModuleType=VBAModule
Option VBASupport 1
Sub Macro1()
'
' Macro1 Macro
'

'
    Dim I As Integer   ' Loop Counter
    Dim X As Integer   ' Number of times to Loop
    
    Sheets("Home").Select
    Range("E4").Select
    X = ActiveCell.Value
'    MsgBox (X)
    
    
For I = 1 To X
    
    Range("I20").Select
'    ActiveCell.FormulaR1C1 = "1"

    ActiveCell.Value = I
    ActiveSheet.Calculate
    Range("B17:G17").Select
    Selection.Copy
    Sheets(ComboResults).Select
        If I = 1 Then Range("C2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'    Range("C3").Select
    Selection.Offset(1, 0).Select
    Sheets(Home).Select
    
Next I

End Sub
#2

Help will be more effective if you upload the problematic file.
What do the ComboResults and Home variables mean in your macro?

#3

“ComboResults” and “Home” are Sheets in the workbook. As you can see my background is in Excel. I am trying to do this simple task in LibreOffice. I have written the sample program in Excel VBA and it works. I have uploaded it into Libreoffice Calc and it works. When I create the same workbook in LibreOffice Calc and copy the macro to that module it doesn’t work. I get the 35 error at the line Sheets(“Home”).Select.

Attached is the uploaded VBA File that works.

excel vba macro to automate selecting samples.ods (14.0 KB)

This Is the LibreOffice Workbook that doesn’t work.

LibreOffice macro to automate selecting samples.ods (11.3 KB)

Thanks for helping me to understand why this doesn’t work.

#4

There are no macros in LibreOffice macro to automate selecting samples.ods.
Note that ComboResults and Home (2 times) are enclosed in double quotes in the VBA macro. In your start macro - no.

#5

I have corrected that in the uploads.

#6

Then what is the question? On my system, your macro works without errors (see attached file).

LibreOffice macro to automate selecting samples 2.ods (15.0 KB)

1 Like
#7

On my system I get the error “35” message at the line, Sheets(“Home”).Select. What is it looking for? What doesn’t it like about this line?

#8

My real question is why does this work when the spreadsheets and macro are uploaded from Excel but when created in LibreOffice it doesn’t work.

#9

There are no macros today in your attached file " LibreOffice macro to automate selecting samples.ods "

#10

I have solved the problem. I downloaded and installed the latest version of Libreoffice. Then I uploaded my Excel File. The Excel file worked as it did originally. Then I copied the VBA macro to a Libreoffice Spreadsheet. It works!

The version of Libreoffice that I had been using must have been corrupted keeping it from working with VBA Support.

Thanks for your Help

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