| View previous topic :: View next topic |
| Author |
Message |
Stephan
Guest
|
 Posted: Mon Jun 14, 2004 10:17 am Post subject: a macro with On Error goto ... works not correctly (in calc) |
 |
|
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
Joined: 02 Mar 2004
Posts: 1569
Location: Frankfurt, Germany
|
| Posted: Mon Jun 14, 2004 1:34 pm Post subject: |
|
|
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  .
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 |
|
|
Guest
|
| Posted: Mon Jun 14, 2004 4:14 pm Post subject: |
|
|
@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
Joined: 12 May 2004
Posts: 1414
Location: France
|
| Posted: Tue Jun 15, 2004 11:51 pm Post subject: |
|
|
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 |
|
|
Guest
|
| Posted: Wed Jun 16, 2004 3:23 am Post subject: |
|
|
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:
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
Joined: 02 Mar 2004
Posts: 1569
Location: Frankfurt, Germany
|
| Posted: Wed Jun 16, 2004 5:51 am Post subject: |
|
|
If On Error Resume <Label> would work, I would be really surprised!
_________________
- Knowledge is Power - |
|
| Back to top |
|
|
Guest
|
| Posted: Wed Jun 16, 2004 6:35 am Post subject: |
|
|
@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
Joined: 02 Mar 2004
Posts: 1569
Location: Frankfurt, Germany
|
| Posted: Wed Jun 16, 2004 6:40 am Post subject: |
|
|
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 |
|
|
B Marcelly
Super User
Joined: 12 May 2004
Posts: 1414
Location: France
|
| Posted: Wed Jun 16, 2004 7:47 am Post subject: |
|
|
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 |
|
|
pitonyak
Administrator
Joined: 09 Mar 2004
Posts: 3623
Location: Columbus, Ohio, USA
|
| Posted: Wed Jun 16, 2004 8:09 am Post subject: |
|
|
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! 
Good to see you here!
_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php |
|
| Back to top |
|
|
Guest
|
| Posted: Wed Jun 16, 2004 9:22 am Post subject: |
|
|
@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
Joined: 12 May 2004
Posts: 1414
Location: France
|
| Posted: Wed Jun 16, 2004 12:02 pm Post subject: |
|
|
@ Andrew,
Yes, I feel much younger now that I am labeled as newbie...
Bye |
|
| Back to top |
|
|
|
|
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
|
