runMacro 'On Local Error GOTO NextIteration' not working

English
, ,
#1

mike@RPI4b3:~> uname -a
Linux MikesPI 6.1.0-rpi7-rpi-v8 #1 SMP PREEMPT Debian 1:6.1.63-1+rpt1 (2023-11-24) aarch64 GNU/Linux

LO Calc:
Version: 7.4.7.2 / LibreOffice Community
Build ID: 40(Build:2)
CPU threads: 4; OS: Linux 6.1; UI render: default; VCL: x11
Locale: en-US (C); UI: en-US
Raspbian package version: 4:7.4.7-1+rpi1+deb12u5
Calc: threaded

A quick little menu takes an array of strings that are function names
passes that string to JohnSun’s runMacro().
In the code “oModuleNames = oLib.getElementNames()” returns this array: ( explorations, See, play, sheet3ex, listBoxEx, Inspect, chart, rangeWalk ).
The first time through the loop when macroName =
“vnd.sun.star.script:explore.explorations.lineChartDo?language=Basic&location=document”
“oScript = oScriptProvider.getScript(macroName)”
throws an error exectuion jumps to NextIteration: and tries again.
The second time through macroName =
“vnd.sun.star.script:explore.See.lineChartDo?language=Basic&location=document” and rather than jumping to
NextIteration: execution stops with this error message.
“BASIC runtime error.
An exception occurred
Type: com.sun.star.script.provider.ScriptFrameworkErrorException
Message: The following Basic script could not be found:
library: ‘explore’
module: ‘See’
method: ‘lineChartDo’
location: ‘document’
at ./scripting/source/basprov/basprov.cxx:365.”
This menu using runMacro() did work until I shuffled things around a bit. Trying to get organized I put runMacro() in a separate module along with rangeWalk() which uses it, put “lineChartDo” in a “charts” module with some other chart routines. Having routines writing to different sheets I collected some of them into their own module but they are all in the “explore” library under “explorations.ods”.
runMacro() can be found here:

I can post the menu code if anyone is interested.
At the moment I’ve not a clue why the “On Local Error GOTO” fails so any pointers would be appreciated.

Happy Friday the 13th and be well.
Thanks,
Mike

#2

As an addendum, all the routines in the menu, other than lineChartDo, are in the ‘explorations’ module and since that is the first module checked they all run just fine from the menu. lineChartDo which is failing is the only one forcing to check other modules and it’s in ‘chart’ module.
Just a little more info. I created another menu for the sheet3 routines where the first routine ‘sheetClear’ comes from the ‘explorations’ module and and all the rest are in the ‘sheet3Ex’ module. trying to execute any of the routines from the ‘sheet3Ex’ module die when “oScript = oScriptProvider.getScript(macroName)” fails to find the script in the ‘See’ module. The first error when the routine wasn’t found in the ‘explorations’ module was caught by the ‘On Local Error’ but the second wasn’t. Initially that ‘On Local Error’ statement was outside the for/next loop but putting inside the loop didn’t help.
I’m stumped folks.
Suggestions?
Be well,
Mike

#3

Plese upload your ODF type sample file (with the embedded macro code) here.

#4

Asuming you meant ODS, it’s bedtime here and will take a fair amount of time to copy then strip out all but enough to show what I’m seeing.
I’m not adverse to the task but hope you will be satisfied if I do it manana.
Mike

#5

ODF: Open Document Format

.ods: opendocument spreadsheet
.ots: opentemplate spreadsheet
and
.odf: opendocument formula

#6

Here is a stripped down spreadsheet with buttons that will call functions that work and buttons for menus on sheets 1 & 3 to call the same functions that don’t work from the menu.
Mike
explorationsMini.ods (45.5 KB)

#7
  1. Open the file.
  2. On Sheet1, press the Menu button.

An error message appears: CellRC function not found.
There really is no function with that name.

1 Like
#8

Sorry, that and other routines are in ‘My Macros & Dialogs’ so it ran fine here.
I copied all those into a new module ‘utils’ and hope it now runs for you.
I’m not sure how to test it here without removing those routines from ‘My Macros & Dialogs’ which would break other stuff here.

I appreciate you trying to help.
Mike
explorationsMini.ods (57.1 KB)

#9

Use safe mode (in Help menu), and there, you have a temporary clean profile to play with.

#11

OK Mike, I went to safe mode now my macros won’t run, not allowed even though I set Tools/Options/Macro Security to low.
Still menus don’t execute.
Where do I go from here?

#12

First of all, you go to my answer below :slight_smile:

But if your local task is to learn how to run macros in safe mode: you just enable macros (setting the security level to a lower value), and reload the document (which is hinted in the macro security dialog, when you change the level, in LibreOffice versions newer than your ancient 7.4).

#10

On Error GoTo statement establishes an error handler. But entering that handler does not end the “in error” state of your current program. And when already in error, your local handlers will not be used; the handling will be passed to parent. You need to use Resume statement to clear it. Or another method to end the “in error” state.

So the method using Resume (hackish and poor IMO) could look like

	For i = LBound(oModuleNames) To UBound(oModuleNames)
		On Local Error GOTO NextIteration
		macroName = prefix & libName & "." & oModuleNames(i) & "." & funcName & sufix
		oScript = oScriptProvider.getScript(macroName)
		runMacro = oScript.invoke(inpParams, aOutParamIndex, aOutParam)
		Exit Function
NextIteration:
    resume DoneError ' here we clear the error state, and continue to the `Next` statement
DoneError:
	Next i

But my preference would be to split it like this:

Function getMyScript(macroName, scriptProvider) As Object
  On Local Error GoTo handler
  getMyScript = scriptProvider.getScript(macroName)
  Exit Function
handler:
  getMyScript = Nothing ' not exactly required :-)
End Function

...

	For Each n In oModuleNames
		macroName = prefix & libName & "." & n & "." & funcName & sufix
		oScript = getMyScript(macroName, oScriptProvider)
		If Not (oScript Is Nothing) Then
			runMacro = oScript.invoke(inpParams, aOutParamIndex, aOutParam)
			Exit Function
		End If
	Next n

which offloads handling errors to an external function, and keeps your loop clean from error state handling at all.

2 Likes
#13

Thanks Mike.
I’m uploading a working copy that includes your cluge fix and will take a further look at the split solution after I run a couple of errands.
You’re really helpful, this is not the first time you’ve taught me a lesson
Thanks again,
Mike McClain
explorationsMini.ods (47.6 KB)

Here is a version with your error handling split out of runMacro() and it works just fine making the runMacro() code look much cleaner.
Sorry it took so long, I got sidetracked when oCell.FormulaLocal = “=R[-4]C” didn’t work putting “=r[-4]c” in the cells as if it was a call to cell.formula = . This was in safe mode, it works as expected in normal mode. Hopefully it got fixed in your later version of LO.

Thanks agan for your help,
Mike
explorationsMini.ods (47.8 KB)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.