OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

a macro with On Error goto ... works not correctly (in calc)

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
Stephan
Guest





PostPosted: Mon Jun 14, 2004 10:17 am    Post subject: a macro with On Error goto ... works not correctly (in calc) Reply with quote

I write the following macro (for working in OpenOffice calc):

Code:
sub eingabe
text1 = "Die Auswahl enthälte mehrere Spalten!" + chr(13) + "Geben Sie den Buchstaben der Spalte" + chr(13) + "mit dem Abfragekriterium ein"
text2 = "ABFRAGEBEREICH"
Do
   Fehler = 0
   sZielSpalte = InputBox(text1,text2 , "A")
   if sZielspalte <> left(sZielspalte,2) then goto bearbeiten
   On Error goto bearbeiten
   oColumn = StarDesktop.CurrentComponent.Sheets(0).getColumns().GetByName(sZielspalte)
   Msgbox "Die gewählte Spalte ist: " & UCase(sZielspalte)
   'msgbox oColumn.getRangeAddress().StartColumn 'Nummer
   Exit Do 'fertig
   bearbeiten:
   if sZielSpalte = "" Then
      Exit Do 'Abbruch
   Else   
      iFehler = 1
      text1 = "Ihre Eingabe ist fehlerhaft" & CHR(13) & "bitte geben sie einen korrekten Wert für die Spalte ein"
   End if   
loop While iFehler <> 0

'...

end sub


in the Inputbox:

Code:
sZielSpalte = InputBox(text1,text2 , "A")


can the user of my macro input a value for the name of a column. Because the input can be incorrect, and i will (i must) check the Value of the input (i mean the value of sZielSpalte).
My idea was:
When i write this:

Code:
oColumn = StarDesktop.CurrentComponent.Sheets(0).getColumns().GetByName(sZielspalte)



i must get a error message when the value of sZielspalte is incorrect. (the value is incorrect when it is not in the range from "A" to "IV" ; 255 Columns in a sheet (i nead only to check that the column exists))

But my macro works not correctly.
When i start the macro and i first input in the Inputbox "##" ,[without ""] (i will to provoke a error with this input) then the InputBox is displayed for the second time (thats correct), but now i input "##" again. Now the macro stops without a error message (the line "oColumn = StarDesktop.CurrentComponent.Sheets(0).getColumns().GetByName(sZielspalte)" is marked in the editor of OpenOffice).
I dont understand why the macro works not correctly.

notice:
I now another way to solve the problem, but i will understand why this macro works not correctly.


Stephan
Back to top
Cybb20
Super User
Super User


Joined: 02 Mar 2004
Posts: 1569
Location: Frankfurt, Germany

PostPosted: Mon Jun 14, 2004 1:34 pm    Post subject: Reply with quote

The reason is a technical one:
The Basic runtime can handle or pass an error only once, I think.
So I would suggest that you rewrite your macro a little bit, I will take some time in the next days (when I get some) and think about how your example could be achieved without error handling.

And as a note:
Quote:

The statement must be inserted at the start of a procedure (in a local error-handling routine) or at the start of a module.

That's what the Online Help says to On Error...

And again I might say it's nice to have another german here Smile.
Viel Spass hier.

Christian
_________________
- Knowledge is Power -


Last edited by Cybb20 on Mon Jun 14, 2004 5:52 pm; edited 1 time in total
Back to top
View user's profile Send private message Send e-mail
Guest






PostPosted: Mon Jun 14, 2004 4:14 pm    Post subject: Reply with quote

@Christian

This macro shows another way (only for columns A to G):

Code:
sub eingabe
text1 = "Die Auswahl enthälte mehrere Spalten!" + chr(13) + "Geben Sie den Buchstaben der Spalte" + chr(13) + "mit dem Abfragekriterium ein"
text2 = "ABFRAGEBEREICH NICHT EINDEUTIG!!!"
Do
   Fehler = 0
   sZielSpalte = InputBox(text1,text2 , "A")
   text2 = "Fehlerhafte Eingabe, bitte korrigieren"
   if len(sZielSpalte) <> 1 then Fehler = 1
   if sZielspalte = "" then
      MsgBox "Benutzerabbruch"
      Exit Do
   End if
   iZielspalte = ASC(UCase(sZielSpalte)) - 65
   if iZielspalte < 0 Or iZielspalte > 6 then Fehler = Fehler + 2
   Select case Fehler
      case 1
      text1 = "Bitte nur einen Buchstaben"
      case 2
      text1 = "Bitte eine Spalte von A bis G"
      case 3
      text1 = "Bitte eine Spalte von A bis G und bitte nur einen Buchstaben"
   End Select
Loop While Fehler <> 0
'an dieser Stelle Stelle steht nun der geprüfte Ausdruck für iZielspalte zur Verfügung
'oder der Benutzer hatte abgebrochen
end sub


and that is the equal for all columns of a calc-sheet (not from me, but from a partner in discussion with i have to discuss the problem in another newsgroup):

Code:
sub Eingabe
'dim sZielspalte as Integer
text1 = "Die Auswahl enthälte mehrere Spalten!" + chr(13) + "Geben Sie den Buchstaben der Spalte" + chr(13) + "mit dem Abfragekriterium ein"
text2 = "ABFRAGEBEREICH"
Do
   iFehler = 0
   sZielSpalte = InputBox(text1,text2 , "A")
   
   if sZielspalte = "" then REM Benutzerabbruch
      Exit Do
   end if
   iZeichen1 = ASC(UCase(Mid(sZielSpalte,1,1))) REM ASCII Code 1. Zeichen
   
   select case len(sZielSpalte) REM Anzahl der eingegebenen Zeichen
      case 1
         if iZeichen1 < 65 or iZeichen > 90 then REM Gültigkeitbereich 1. Zeichen
            iFehler = 2
         else
            iZielSpalte = iZeichen1 - 65
            iFehler = 0
         end if
      case 2
         iZeichen2 = ASC(UCase(Mid(sZielSpalte,2,1))) REM ASCII Code 2. Zeichen
         if iZeichen1 < 65 or iZeichen > 90 or iZeichen2 < 65 or iZeichen2 > 90 then
            iFehler = 2
         else
            iZielSpalte = (iZeichen1 - 65)*26 + iZeichen2 - 39
            msgbox iZielspalte 
            iFehler = 0
         end if
      case else
         iFehler = 1 REM Eingabe 0 oder > 2 Zeichen
   end select
   
   if iZielSpalte < 0 or iZielSpalte > 255 then REM Eingabe nicht innerhalb SpaltenBereich 'A >> IV'
      iFehler = 2
   end if
   
   Select case iFehler
   case 1
      msgbox "Eingabe bis 2 Buchstaben / Maximal 'IV': " + sZielSpalte
   case 2
      msgbox "Ungültige Eingabe: " + sZielSpalte
   End Select

Loop While iFehler <> 0

msgbox "Gratuliere, Du kannst eine Spalte identifizieren :" + sZielSpalte + ": = Spalte#: " + iZielSpalte +CHR(13)+"Oder Du hast einfach die Taste 'Abbruch' gedrückt"+chr(13)+"Oder einen LeerString eingegeben"

end sub


This two macros works correctly. In my opinion the code of this two macros is not so smart (in german i would say "elegant"), because it is necessary to disassemble the Value of the InputBox (sZielSpalte). What i mean is i must do this:

Code:
iZeichen1 = ASC(UCase(Mid(sZielSpalte,1,1))) '(*)
iZeichen2 = ASC(UCase(Mid(sZielSpalte,2,1)))


(*)this code is not so good ...Left(sZielspalte,1) is better, but this is not a real problem

an then i must check separately the value of iZeichen1 and iZeichen2 . Thats a lot of code and a long way.


And i would like to write a short smart code for this, thats my problem.


Quote:
And again I might say it's nice to have another german here .
Viel Spass hier.


yes, thats right. Sorry thats my english is not so good.


Stephan
Back to top
B Marcelly
Super User
Super User


Joined: 12 May 2004
Posts: 1453
Location: France

PostPosted: Tue Jun 15, 2004 11:51 pm    Post subject: Reply with quote

Hallo Stephan,
The problem in your first macro is your error treatment.
An error treatment must finish with a Resume instruction. There are 3 variants of it:
Code:
Resume Next  ' continue after the instruction in error
Resume LabelX  ' continue at label LabelX
Resume  ' retry the instruction in error


The Resume instruction tells Basic to reset its internal error status. If you don't do that, at the next error or at the next On Error Goto xxx , Basic will stop. This is for security reasons.

Bernard
Back to top
View user's profile Send private message Visit poster's website
Guest






PostPosted: Wed Jun 16, 2004 3:23 am    Post subject: Reply with quote

Hello Bernard,

Quote:
An error treatment must finish with a Resume instruction. There are 3 variants of it:
Code:
Resume Next ' continue after the instruction in error
Resume LabelX ' continue at label LabelX
Resume ' retry the instruction in error


Thanks for your help.

OK, i understand "Resume Next", this code works correct:

Code:
sub eingabe
text1 = "Die Auswahl enthälte mehrere Spalten!" + chr(13) + "Geben Sie den Buchstaben der Spalte" + chr(13) + "mit dem Abfragekriterium ein"
text2 = "ABFRAGEBEREICH"
Do
   Fehler = 0
   sZielSpalte = InputBox(text1,text2 , "A")
   if sZielspalte <> left(sZielspalte,2) then goto bearbeiten
   On Error Resume Next 
   oColumn = StarDesktop.CurrentComponent.Sheets(0).getColumns().GetByName(sZielspalte)
   if oColumn <> "" Then
      Msgbox "Die gewählte Spalte ist: " & UCase(sZielspalte)
      'msgbox oColumn.getRangeAddress().StartColumn 'Nummer
      Exit Do 'fertig
   end if
   bearbeiten:
   if sZielSpalte = "" Then
      Exit Do 'Abbruch
   Else   
      iFehler = 1
      text1 = "Ihre Eingabe ist fehlerhaft" & CHR(13) & "bitte geben sie einen korrekten Wert für die Spalte ein"
   End if   
loop While iFehler <> 0

'...

end sub


But i dont understand: "Resume LabelX" and "Resume". When i write:

Code:
On Error Resume bearbeiten 'bearbeiten is my Label

or:
Code:
Resume bearbeiten


than i get a error message from OpenOffice (syntax error).

Please, I can get an example for "Resume LabelX" and "Resume" ?



Stephan
Back to top
Cybb20
Super User
Super User


Joined: 02 Mar 2004
Posts: 1569
Location: Frankfurt, Germany

PostPosted: Wed Jun 16, 2004 5:51 am    Post subject: Reply with quote

If On Error Resume <Label> would work, I would be really surprised!
_________________
- Knowledge is Power -
Back to top
View user's profile Send private message Send e-mail
Guest






PostPosted: Wed Jun 16, 2004 6:35 am    Post subject: Reply with quote

@Cybb20

Whe you see this macro:

sub eingabe
text1 = "Die Auswahl enthälte mehrere Spalten!" + chr(13) + "Geben Sie den Buchstaben der Spalte" + chr(13) + "mit dem Abfragekriterium ein"
text2 = "ABFRAGEBEREICH"
Do
Fehler = 0
sZielSpalte = InputBox(text1,text2 , "A")
if sZielspalte <> left(sZielspalte,2) then goto bearbeiten
On Error Resume bearbeiten
oColumn = StarDesktop.CurrentComponent.Sheets(0).getColumns().GetByName(sZielspalte)
if oColumn <> "" Then
Msgbox "Die gewählte Spalte ist: " & UCase(sZielspalte)
'msgbox oColumn.getRangeAddress().StartColumn 'Nummer
Exit Do 'fertig
end if
bearbeiten:
if sZielSpalte = "" Then
Exit Do 'Abbruch
Else
iFehler = 1
text1 = "Ihre Eingabe ist fehlerhaft" & CHR(13) & "bitte geben sie einen korrekten Wert für die Spalte ein"
End if
loop While iFehler <> 0

'...

end sub


Then the Situation is:
When i run this macro, the macro stop with the errormessage (in german):

BASIC-Syntaxfehler.
Erwartet: Next.

and the word 'Resume' is marked in the editor.

Version of OpenOffice is: OpenOffice 1.1.1 (german)


Stephan
Back to top
Cybb20
Super User
Super User


Joined: 02 Mar 2004
Posts: 1569
Location: Frankfurt, Germany

PostPosted: Wed Jun 16, 2004 6:40 am    Post subject: Reply with quote

Yes that's what I expect to happen, look into the OnlineHelp there is only Resume Next and Next is a must when there is a "Resume" after On Error.
But maybe B Marceilly got it working somehow.
_________________
- Knowledge is Power -
Back to top
View user's profile Send private message Send e-mail
B Marcelly
Super User
Super User


Joined: 12 May 2004
Posts: 1453
Location: France

PostPosted: Wed Jun 16, 2004 7:47 am    Post subject: Reply with quote

Well,
Don't confuse
On Error Resume LabelX
which does not exist, and this instruction :
Resume LabelX

Here is a simpler example, similar to your problem. It displays the current directory of a given disk.

Code:
Sub ResumeLabelExample
Dim diskName As String, CurrentDirectory As String

Do ' loop until the user cancels InputBox
  diskName = InputBox("Name of disk (A, B, C...) ?")
  if Len(diskName) = 0 then Exit Do
  On Error GoTo errDisk ' activate my error handler
  CurrentDirectory = CurDir(diskName)
  MsgBox CurrentDirectory
  MsgBox "Attributes : " & GetAttr(CurrentDirectory)
  On Error GoTo 0 ' inactivate my error handler
  ' - - other instructions unrelated to disk handling - -
  Exit Do ' finished
 Label1: ' an error occurred, ask again
  On Error GoTo 0
Loop
Exit Sub

errDisk:
  MsgBox("Inexistent or inaccessible disk", 16)
  Resume Label1
End Sub


Hope it's more clear. You can find other examples in the libraries in sOffice, e.g. library Euro, module AutoPilotRun.

Concerning the instruction
Resume
It's difficult to give an example. You must ensure in the error handler that the reason of the error has disappeared. Then you can execute Resume. If you make any mistake you will loop endlessly.
You can try this, at your risk, with my example : suppress Label1 in the line
Resume Label1
and execute step by step. Click on the Stop button when you are tired of looping.
A funny thing is that if you add a comment in the line Resume, Basic will complain of syntax error.

About the online help, it's not of much help. It's incomplete and the example they give works only because the error handler ends the execution.
Bernard
Back to top
View user's profile Send private message Visit poster's website
pitonyak
Administrator
Administrator


Joined: 09 Mar 2004
Posts: 3655
Location: Columbus, Ohio, USA

PostPosted: Wed Jun 16, 2004 8:09 am    Post subject: Reply with quote

Hello B Marcelly

I am sorry, but I just had to interject how amusing I find it to see you labled as a "new" type user. You are very clearly mentioned in my book as brillitant! Very Happy

Good to see you here!
_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php
Back to top
View user's profile Send private message Send e-mail Visit poster's website AIM Address
Guest






PostPosted: Wed Jun 16, 2004 9:22 am    Post subject: Reply with quote

@Bernard

Quote:
Hope it's more clear.


yes, thank you.

Quote:
You can find other examples in the libraries in sOffice, e.g. library Euro, module AutoPilotRun.


oh yes, i see...


Thanks to all.

Stephan
Back to top
B Marcelly
Super User
Super User


Joined: 12 May 2004
Posts: 1453
Location: France

PostPosted: Wed Jun 16, 2004 12:02 pm    Post subject: Reply with quote

@ Andrew,
Yes, I feel much younger now that I am labeled as newbie...

Bye
Back to top
View user's profile Send private message Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group