Basic syntax error spreadsheet import

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
rene
Posts: 68
Joined: Wed Jan 23, 2008 12:50 pm

Basic syntax error spreadsheet import

Post by rene »

I have a spreadsheet from this author converted to ods format. However every time I open I am prompted many times about an error with the syntax:"statement block still open end sub missing".

I navigate to the macro function in the menu bar and activate the basic editor. Below is an extract:

Code: Select all

Rem Attribute VBA_ModuleType=VBADocumentModule
Option VBASupport 1

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Range("dTad").GoalSeek Goal:=0, ChangingCell:=Range("Tad")
    If Range("XC").Value = 0 Then
        If Range("iCO").Value > 0 Then Range("iCO").Value = 0 Else
        If Range("iCxHy").Value > 0 Then Range("iCxHy").Value = 0 Else
        If Range("iHCN").Value > 0 Then Range("iHCN").Value = 0 Else
        If Range("iTar").Value > 0 Then Range("iTar").Value = 0 Else
'
        If Range("ecCOin").Value > 0 Then Range("ecCOin").Value = 0 Else
        If Range("ecCxHyin").Value > 0 Then Range("ecCxHyin").Value = 0 Else
        If Range("ecHCNin").Value > 0 Then Range("ecHCNin").Value = 0 Else
        If Range("ecTarin").Value > 0 Then Range("ecTarin").Value = 0 Else
    	If Range("XH").Value = 0 Then
        If Range("iCxHy").Value > 0 Then Range("iCxHy").Value = 0 Else
        If Range("iNH3").Value > 0 Then Range("iNH3").Value = 0 Else
        If Range("iHCN").Value > 0 Then Range("iHCN").Value = 0 Else
        If Range("iTar").Value > 0 Then Range("iTar").Value = 0 Else
        If Range("iH2inp").Value > 0 Then Range("iH2inp").Value = 0 Else
'
        If Range("ecCxHyin").Value > 0 Then Range("ecCxHyin").Value = 0 Else
        If Range("ecNH3in").Value > 0 Then Range("ecNH3in").Value = 0 Else
        If Range("ecHCNin").Value > 0 Then Range("ecHCNin").Value = 0 Else
        If Range("ecTarin").Value > 0 Then Range("ecTarin").Value = 0 Else
        If Range("ecH2in").Value > 0 Then Range("ecH2in").Value = 0 Else
    
    End If
    If Range("Fuel_type").Value = 2 Then
        Range("XH2OW").Value = 0
        Range("Xash").Value = 0
    End If

    mFW = Range("mFW").Value
    If mFW < 9999 Then
    ActiveSheet.Protect DrawingObjects:=False, Contents:=False, Scenarios:=False
    Range( _
        "E63,D71:D75,E71:E74,D54:D58,C49:D49,D44:D48,C44,C40:D40,C12:D12,I69:J69,H102:I105,H120:I120,H123:J126,H141:J141" _
        ).NumberFormat = "0.000"
    Range("J102:J105,J120").NumberFormat = "0"
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    ElseIf mFW < 99999 Then
    ActiveSheet.Protect DrawingObjects:=False, Contents:=False, Scenarios:=False
    Range( _
        "E63,D71:D75,E71:E74,D54:D58,C49:D49,D44:D48,C44,C40:D40,C12:D12,I69:J69,H102:I105,H120:I120,H123:J126,H141:J141" _
        ).NumberFormat = "0.00"
    Range("J102:J105,J120").NumberFormat = "0.000E+00"
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    ElseIf mFW < 999999 Then
    ActiveSheet.Protect DrawingObjects:=False, Contents:=False, Scenarios:=False
    Range( _
        "E63,D71:D75,E71:E74,D54:D58,C49:D49,D44:D48,C44,C40:D40,C12:D12,I69:J69,H102:I105,H120:I120,H123:J126,H141:J141" _
        ).NumberFormat = "0.0"
    Range("J102:J105,J120").NumberFormat = "0.000E+00"
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Else
    ActiveSheet.Protect DrawingObjects:=False, Contents:=False, Scenarios:=False
    Range( _
        "E63,D71:D75,E71:E74,D54:D58,C49:D49,D44:D48,C44,C40:D40,C12:D12,I69:J69,H102:I105,H120:I120,H123:J126,H141:J141" _
        ).NumberFormat = "0"
    Range("J102:J105,J120").NumberFormat = "0.000E+00"
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End If
What needs to be corrected?

I want to replicate a scalc version.
ooo 321; gnome 220; ibm java jre 160; gnu linux mandrake 2008
ooo 2319238; postgresql 826; gnome 220; ibm java jre 150; gnu linux mandriva 2007
openoffice<at>conference.jabber.org
User avatar
Hagar Delest
Moderator
Posts: 32627
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Basic syntax error spreadsheet import

Post by Hagar Delest »

VBA is not compatible with OOo.

See that thread for further information: [Solved] OO 2.4 Calc Compatibility problem with MS Excel.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
rene
Posts: 68
Joined: Wed Jan 23, 2008 12:50 pm

Re: Basic syntax error spreadsheet import

Post by rene »

Is it possible to remove all the vba code?
ooo 321; gnome 220; ibm java jre 160; gnu linux mandrake 2008
ooo 2319238; postgresql 826; gnome 220; ibm java jre 150; gnu linux mandriva 2007
openoffice<at>conference.jabber.org
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Basic syntax error spreadsheet import

Post by Villeroy »

rene wrote:Is it possible to remove all the vba code?
I would leave them in the document if it is an Excel file.
To remove from ods call menu:Tools>Macros>Organize>Basic...
[Organizer...]
Go to tab "Libraries", pick the document from "Location" and remove all the libraries (fails with library "Standard")
Go to tab "Modules" and remove all the modules from library "Standard" in your document
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
rene
Posts: 68
Joined: Wed Jan 23, 2008 12:50 pm

Re: Basic syntax error spreadsheet import

Post by rene »

In libraries, after selecting the document, nothing available to be deleted.
After deleting in modules, close and re-open file but error dialogue windows continue
ooo 321; gnome 220; ibm java jre 160; gnu linux mandrake 2008
ooo 2319238; postgresql 826; gnome 220; ibm java jre 150; gnu linux mandriva 2007
openoffice<at>conference.jabber.org
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Basic syntax error spreadsheet import

Post by Villeroy »

But now it should be another error message about macros that you deleted and can not be found anymore.
Remove the events in menu:Tools>Customize... tab:"Events". There might be some event(s) that triggers a specific macro call every time you open the document.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
rene
Posts: 68
Joined: Wed Jan 23, 2008 12:50 pm

Re: Basic syntax error spreadsheet import

Post by rene »

No, I see a new error message, that a sub procedure/function not defined
ooo 321; gnome 220; ibm java jre 160; gnu linux mandrake 2008
ooo 2319238; postgresql 826; gnome 220; ibm java jre 150; gnu linux mandriva 2007
openoffice<at>conference.jabber.org
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Basic syntax error spreadsheet import

Post by Villeroy »

rene wrote:No, I see a new error message, that a sub procedure/function not defined
Not defined, not there, can not be found,... basically deleted. Yes, fine. Get the calling event and remove that too.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
rene
Posts: 68
Joined: Wed Jan 23, 2008 12:50 pm

Re: Basic syntax error spreadsheet import

Post by rene »

What is calling event?
ooo 321; gnome 220; ibm java jre 160; gnu linux mandrake 2008
ooo 2319238; postgresql 826; gnome 220; ibm java jre 150; gnu linux mandriva 2007
openoffice<at>conference.jabber.org
User avatar
Hagar Delest
Moderator
Posts: 32627
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Basic syntax error spreadsheet import

Post by Hagar Delest »

Open a document is an event, as Close a document, ...
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Basic syntax error spreadsheet import

Post by Villeroy »

Villeroy wrote:Remove the events in menu:Tools>Customize... tab:"Events". There might be some event(s) that triggers a specific macro call every time you open the document.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Hagar Delest
Moderator
Posts: 32627
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Basic syntax error spreadsheet import

Post by Hagar Delest »

I'm wondering if rene is not using a non English version; so some terms may be difficult to translate in his language.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Basic syntax error spreadsheet import

Post by Villeroy »

Hagar de l'Est wrote:I'm wondering if rene is not using a non English version; so some terms may be difficult to translate in his language.
"Tools" [German: "Extras"] is between menus "Format" and "Data"
"Customize" [German: "Anpassen"] is within Tools between "XML Filter" and "Options". It calls a dialog.
"Events" [German: "Ereignisse"] is the last tab on that dialog.
It has a list of events mapped to assigned actions as well as a button "Remove" [German "Entfernen"]
Select an entry with an assigned action and click "Remove".
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply