How can I find my macro code for Calc?

RussellEliotDale · February 27, 2019, 10:35am

Hi. All of a sudden yesterday all my macros disappeared for Calc. Every cell in my spreadsheets that referenced a macro that I had written showed up with the string #NAME? in it.

Fortunately, I keep a backup on a different machine. I can see the Macros functioning in the spreadsheets that reference them on the backup machine.

So, I thought I would copy-and-paste the code from the Macros on that machine into the Visual Basic editor on the machine on which the macros disappeared. But, when I try to look at the macros on the backup machine, the code is
nowhere to be found. It must be there or the macros wouldn’t be running and giving the correct results.

So, how do I find them?

Please help. Also, can someone suggest why in the world my macros would have suddenly disappeared in the first place? Is this related to the phenomenon whereby my dictionary suddenly disappears every once in a while in the wordprocessor?

Thank you ahead of time for any help!

Yours,
Russell

peterwt · February 28, 2019, 4:45pm

I assume you have installed LO on your backup machine as you say you can get the Calc Macros to run on it.
If you run LO on that machine and go to Tools > Macros >Organise Macros >LibreOffice Basic and look at the left hand pane then your macros will be in My Macros. if you expand My Macros and Standard your macros should be in Modules in Standard.
How did you backup the macros to your back machine? You could also have stored the Macros in the Calc file by creating a Module in the Calc document.

EDITED 01/03/2019

If you just copied the Calc files to your backup machine then if the Basic code was stored in My Macros the code would not be on your backup machine. You must have stored the code in a module within the Calc files for the code to work when you use the calc file on your backup machine.

Open one of the Calc files that work on your backup machine and go to Tools > Macros >Organise Macros >LibreOffice Basic. At the bottom of the left hand pane the name of the Calc file will be shown, If you expand this there will be a module containing your Basic code.

If you copy one of the Calc files from your backup machine to the other machine the code should be there and work.

EDITED 29/03/2019

What is happening is strange. Did you create the spreadsheet and the macros yourself or get them from someone else? I will assume that you created them and you can’t remember how or where you saved the macros. I also assume that the macros are user defined functions.

There are two ways to save macros in LO.

One is to save them on the machine you are using in MyMacros. In this case they are stored on that machine in your profile in the basic directory.

Two is to save the macros in a module created in the spreadsheet file

RussellEliotDale · February 28, 2019, 7:03pm

Thank you Peter for your response. Let me answer your question how did I backup first. I backed up simply by copying the calc spreadsheet to my other computer. I have a script that does the copy/backup for me.

Going to Tools>Macros>Organize Macros>LibreOffice Basic doesn’t show anything. I looked in My Macros>Standard under which Module1 is found and there is absolutely nothing in there except for an utterly empty Sub Main…End Sub structure. Similarly, in the left panel, there is an item called Untitled2 underwhich there is something called Standard, but there is nothing in there but another Sub Main…End Sub structure.

I can’t figure out why all the formulas are running if none of the Macros are here. Where are they? Is there a place on the file system where these Macros are kept in a text file? I am running Libre Office on an Ubuntu 18.04.1 LTS system.

Yours,
Russell
.

peterwt · March 1, 2019, 12:45pm

@RussellEliotDale see my edited post.

RussellEliotDale · March 5, 2019, 10:41pm

Thank you again Peter for trying to help me. But, when I to to Tools>Macros>Organize Macros>LibreOffice Basic and look at the left pane at the bottom, there is an item called Untitled1. When I open this up, there is an item in there called Standard. When I try to open this there is nothing. It doesn’t even open. I have tried to open this in every way I could think of, but there just seems nothing doing. It just doesn’t open or give me any information.

Also, I tried copying one of the files from my backup machine to my regular machine and although the macros are definitely there on the backup machine and running, they are not there when I transfer the file to my regular machine.

peterwt · March 29, 2019, 2:19pm

See my edited post 29/03/2019

RussellEliotDale · March 29, 2019, 3:18pm

I wrote the macros myself & I saved them in a module in the spreadsheet. I used these macros for years. I wrote them originally in OpenOffice. I think it is probable that OO on my Windows machine did an update and wiped out my macro files, wherever that was stored (again, I saved the macros in a module; I let OO take care of the actual “file” side of that). Either that or Windows did an upgrade of some sort and wiped out the macro file for some reason. But, my guess is that it was OO itself that did this. My guess is also that my spell-check dictionary going missing a year or so ago is the same sort of incompetent programming by whomever is writing the upgrading scripts: my hypothesis is that for certain upgrades, the upgrade script simply deletes the user’s data, like the spell-check dictionary and the macros. It really makes me mad. I had to rewrite ALL of the macros. This time I saved them in a separate file as well as in an OO Basic module.