Ask Your Question
0

Basic Calc Macros Not Loading [closed]

asked 2013-09-17 15:34:27 +0200

KC gravatar image

updated 2015-11-09 04:08:34 +0200

Alex Kemp gravatar image

LibreOffice Version: 4.1.1.2 Java: 1.70_40

The problem: I have a collection of functions written in VBA as a module in a VBA spreadsheet. The functions import and work properly in Calc. However, once I save the spreadsheet as .ods and load it, all the functions display #NAME? in the spreadsheet. Forcing manual recalculation does not correct the problem. The only way I can get the functions to work is by doing the following:

Tools > Macros >Organize Macros> LibreOffice Basic, pick the spreadsheet, then the module and a function, and attempt to run it. This produces an "Argument not optional" error. I close out of macros, open the function in the spreadsheet, and change a variable, then change it back. The function then calculates, but forcing a manual recalculation still does not work. The only way to make the functions work is to copy a working function to all instances and faking a variable change in the others.

Saving the spreadsheet at this point does not preserve functionality. Loading the spreadsheet again presents the same problem.

Things I've tried: 1. Changed the Macro security settings from high to medium and low. 2. Change the Calc options to recalculate Excel 2007 and higher and ODS spreadsheets on loading.

None resolve the problem.

Am I missing a step, or is this a bug?

Thanks in advance.

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-11-09 04:08:48.690075

1 Answer

Sort by » oldest newest most voted
0

answered 2013-09-18 03:20:09 +0200

KC gravatar image

Note: Not an answer, but more information.

It seems to be related either to the VBA option, or because it existed as a separate project than Standard. I set up a test spreadsheet, added a Basic macro from scratch, and it loaded perfectly. These macros work just fine without the VBA support option, so copied them over to a new module under standard, removed the VBA Project, and the functions load when the spreadsheet opens.

Important: This may not work for everyone, as some VBA code may need VBA support. It just happened to work in this instance.

I should have pasted the code as-in in a new module, complete with the VBA support flag to see if this was the problem or if it was because the code was in a separate project.

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2013-09-17 15:34:27 +0200

Seen: 1,199 times

Last updated: Sep 18 '13