Basic syntax error spreadsheet import

Creating a macro - Writing a Script - Using the API

Basic syntax error spreadsheet import

Postby rene » Tue Aug 12, 2008 1:49 pm

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   Expand viewCollapse view
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
rene
 
Posts: 68
Joined: Wed Jan 23, 2008 12:50 pm

Re: Basic syntax error spreadsheet import

Postby Hagar Delest » Tue Aug 12, 2008 2:26 pm

VBA is not compatible with OOo.

See that thread for further information: [Solved] OO 2.4 Calc Compatibility problem with MS Excel.
AOO 4.1.6 on Xubuntu 19.04 and 4.1.5 on Windows 7 (with winPenPack port).
User avatar
Hagar Delest
Moderator
 
Posts: 28543
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Basic syntax error spreadsheet import

Postby rene » Tue Aug 12, 2008 2:42 pm

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
rene
 
Posts: 68
Joined: Wed Jan 23, 2008 12:50 pm

Re: Basic syntax error spreadsheet import

Postby Villeroy » Tue Aug 12, 2008 3:04 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27114
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Basic syntax error spreadsheet import

Postby rene » Tue Aug 12, 2008 3:14 pm

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
rene
 
Posts: 68
Joined: Wed Jan 23, 2008 12:50 pm

Re: Basic syntax error spreadsheet import

Postby Villeroy » Tue Aug 12, 2008 3:33 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27114
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Basic syntax error spreadsheet import

Postby rene » Tue Aug 12, 2008 3:45 pm

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
rene
 
Posts: 68
Joined: Wed Jan 23, 2008 12:50 pm

Re: Basic syntax error spreadsheet import

Postby Villeroy » Tue Aug 12, 2008 3:56 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27114
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Basic syntax error spreadsheet import

Postby rene » Tue Aug 12, 2008 9:44 pm

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
rene
 
Posts: 68
Joined: Wed Jan 23, 2008 12:50 pm

Re: Basic syntax error spreadsheet import

Postby Hagar Delest » Tue Aug 12, 2008 9:51 pm

Open a document is an event, as Close a document, ...
AOO 4.1.6 on Xubuntu 19.04 and 4.1.5 on Windows 7 (with winPenPack port).
User avatar
Hagar Delest
Moderator
 
Posts: 28543
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Basic syntax error spreadsheet import

Postby Villeroy » Tue Aug 12, 2008 10:57 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27114
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Basic syntax error spreadsheet import

Postby Hagar Delest » Tue Aug 12, 2008 11:10 pm

I'm wondering if rene is not using a non English version; so some terms may be difficult to translate in his language.
AOO 4.1.6 on Xubuntu 19.04 and 4.1.5 on Windows 7 (with winPenPack port).
User avatar
Hagar Delest
Moderator
 
Posts: 28543
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Basic syntax error spreadsheet import

Postby Villeroy » Tue Aug 12, 2008 11:28 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27114
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 5 guests