[Solved] Macro Run-Time Error

[KeithOO]

Occasionally I have a Macro which will not run from a button, while working fine if I run it manually. I currently have such a Macro which toggles Protect Sheet View/Hide. This is frustrating and any help would be appreciated. When the error occurs, sh.unprotect ... or sh. protect ... is highlighted. Thanks.

Code: Select all

Sub ToolsSheetProtectionToggle(Optional sPWD)
if isMissing(sPWD) then sPWD=""
   sh = ThisComponent.CurrentController.getActiveSheet()
   if sh.isProtected() then
      sh.unprotect(sPWD)
   else
      sh.protect(sPWD)
   endif
End Sub

[Zizi64]

Plese upload your ODF type samle file together with the embedded macro and the button.

[KeithOO]

Made up sample doc. to show problem. Thanks.

[Zizi64]

Why not work?

1.: The Cell protection in your file is switched OFF by usage the Default Cell Style, because the Protection proprty of the Default Cell Style is switched off manually. The sheet protection will not active on cells what are not protected (on the cell-level)

2. The button CAN NOT pass a string type "password" parameter. But it always pass an oEvent object, when you click on it. But the passed parameter is not missed in your macro: there is a passed parameter - the oEvent object - and the macro will not work, because the passed object and the expected string type password have not same variable type. You can check the passed parameter with the XRAY tool. Just put it in the first line of the macro:

Code: Select all

Xray sPWD

It will show you: The passed parameter is not a string!

3. It is better to use the

Code: Select all

Option Explicit

switch in the first line of the Module. The interpreter will warn you if there is one or more undeclared variables. Always declare the variables in your macros.

[KeithOO]

Thanks, but unfortunately, my knowledge is insufficient to understand and take advantage of what you are saying and I don't wish to waste your time. Incidentally I don't know if it matters but the above code is not my work of course.
As stated on my other thread for today, I have downloaded XrayTool but regret, while programming and Fault Finding both interest me, I cannot spend hours at the moment studying. I shall have to work round the Error etc. Thanks as always for your kind help.

[Lupp]

The button will pass a parameter to the called routine. The parameter contains information about the mouse event (if any) and about the object (button in this case) in whose area (or for what, due to having the focus) the event occurred.
Though you are not interested in the evaluation of that information, you need to first formally accept it. Subsequently you may ignore it. If you don't count for that event, your assignment to the remaining parameter will spoil the Sub in case it's called from a control. If called directly - with no parameter sent - it will work as intended.
Fix:

Code: Select all

Sub ToolsSheetProtectionToggle(pEvent, Optional sPWD)
if isMissing(sPWD) then sPWD=""
   sh = ThisComponent.CurrentController.getActiveSheet()
   if sh.isProtected() then
      sh.unprotect(sPWD)
   else
      sh.protect(sPWD)
   endif
End Sub

By the way: In what use case of yours - and in what way - would the Sub actually get passed something for sPWD?
Do you actually need the respective optional parameter?

[KeithOO]

Thank you. That works via a button. Without a button it comes up 'wrong number of parameters!' which is fine and what I think you are explaining. I will compare your code with the previous code to try to understand the difference. I will then see if I can apply it to other items so that I can toggle with one button. The penny is dropping ... maybe. Thanks again.
Just noticed your additional questions. I haven't a clue as code was proved by someone on this forum. I do not have sufficient knowledge to write such code.

[Lupp]

The issue is simple in this case: The >Tools>Macros>Run proceeding (I virtually never use it.) checks for an applicaple set of parameters before it actually starts the Sub. Since the routine never actually passes a parameter, it expects Sub either without any parameter or with all the parameters declared 'Optional'. I obviously had removed that keyword inadvertently.

On the other hand a call to a Sub due to a button event or from an object contained in a toolbar always passes an event parameter, and the called Sub must be prepared to accept it - whether it evaluates it or not. (I told already.)

Anyway: You need to take seriously what Zizi64 told. If you actually want to continue on the way you started, you should be prepared to have to read a lot, and to spend very many hours. And you should be prepared to experience that you will fail with some projects again and again nonetheless. For most users not much interested in programming and internals of software this will be wasted time. There are a few things they can do based on recorded macros - and that is it. To rework a recorded macro slightly to better adapt it to an intended use case may also work sometimes for the expense of hard thinking. To know about the 'Optional' thing may be useful then.

Another question: Why? I cannot think of a real use case where the toggling sheet protection via a button click is of much use. You would need to have a copy of that button on every sheet. And concerning "your other question" that's even more an important issue.
(I personally never hide headers. What good for? It's MS thinking at best, imo.)

Finally I attach a demo covering both your recent questions (also viewtopic.php?f=9&t=100555) and containing a custom toolbar saved with the document. Concerning your questions this way to call the Sub should be preferrable.
(This should not be continued in the same style. You have to make a decision, and you will find partners for one or another discussion here, but not private teachers spending a few hours to teach you every other day.)

[RoryOF]

Thanks, but unfortunately, my knowledge is insufficient to understand and take advantage of what you are saying and I don't wish to waste your time. Incidentally I don't know if it matters but the above code is not my work of course.
As stated on my other thread for today, I have downloaded XrayTool but regret, while programming and Fault Finding both interest me, I cannot spend hours at the moment studying. I shall have to work round the Error etc. Thanks as always for your kind help.

For your information (when you have the time for it): many macros can be written with the information in the OO BASIC manual, at

https://wiki.openoffice.org/w/images/c/ ... o3.2.0.pdf

or more recent, at

https://wiki.openoffice.org/wiki/Docume ... ASIC_Guide

The 3.2 PDF guide is sufficiently up to date to be satisfactory for initial (and indeed, most) steps in programming OO.

More detailed information on macros can be downloaded from Andrew Pitonyak's site at

http://www.pitonyak.org/oo.php

I doubt you will need any of that until you have much time on your hands!

Edit: It is also worth remarking that careful use of a search engine will often produce macro code for OO and LibO which would need minimal informed alteration for many applications.

[Villeroy]

It is my fault I made in the OP's previous topic. I wanted to make this happen with an optional password and forgot about the possibility that the passed argument could be an object when called from an UI element. I added a better argument check which also considers that the macro may receive a string starting with "vnd.sun.star.script:" when triggered by a text hyperlink.

Code: Select all

Sub ToolsSheetProtectionToggle(Optional x)
if Vartype(x) = 8 AND Left(x,20) <> "vnd.sun.star.script:" then 
  sPWD= x
else
  sPWD = ""
endif
sh = ThisComponent.CurrentController.getActiveSheet()
if sh.isProtected() then
   sh.unprotect(sPWD)
else
   sh.protect(sPWD)
endif
End Sub

This way you can use the same macro with a password "123456"

Code: Select all

Sub ToggleWithPassword()
  ToolsSheetProtectionToggle "123456"
End Sub

ToolsSheetProtectionToggle detects that the passed argument "123456" is a string (vartype 8) and toggles the protection status using this string as a password.

[Villeroy]

Or simply:

Code: Select all

Sub ToolsSheetProtectionToggle()
sPWD = ""
sh = ThisComponent.CurrentController.getActiveSheet()
if sh.isProtected() then
   sh.unprotect(sPWD)
else
   sh.protect(sPWD)
endif
End sub

and fill in a password when needed. For different passwords you need different versions of this code.

[KeithOO]

I recently created threads relating to Macros Errors. As usual, brilliant contributors gave help and consequently my document and my knowledge have improved considerably.
However, most suggestions left me floundering as I am not a programmer and only used Macros for the first time, a few weeks ago. I respect this Forum and consequently, yesterday morning, I posted that I would manage with what I had as I didn't wish to waste anyone's time.
My queries relate to a document I have developed without charge for a friend and consequently there is a limit to the amount of time I can spend to teach myself.
I would just like to thank everyone for their help and suggested reading matter (particularly the post by RoryOF) which I will get around to looking at.

[Lupp]

I would just like to thank everyone for their help and ...

Wouldn't you want to also express your respect by opening the example I attached to my post under the following link?
viewtopic.php?f=20&t=100557#p484036
It contains code, buttons and a custom toolbar as well, and should demonstrate how a somehow mature solution should look. The problem with improper parameter lists is also addressed (and solved) there. It offers a usable solution concerning your other two recent questions by its examples.

[KeithOO]

Yes indeed, I do plan to look through all the recent threads and, if possible, 'view/grab' all of the examples and reading material provided. I have had to revert to my own work and therefore could not do it immediately but should complete next week. Best wishes and thanks again.

[Lupp]

Dont' worry. I simply was confused a bit by the fact that the example file not yet was downloded at all.
Sorry if I hassled you.

[KeithOO]

No problem. May be a good thing that you asked as I've downloaded very little and other contributors may have wondered.