Macro mysteriously stopped working

English
#1

I have two calc macros with similar code structure that are now both generating “Basic runtime error Variable not defined”. I’m wondering whether this might have happened after a LO update? I can’t think of any other reason, since nothing as far as I can recall has changed. I don’t remember whether my LO has updated recently (or whether it does so automatically).

Here’s the code (the error occurs on the last line shown):

Dim oDoc As Object
Dim sUrl As String
Dim oTargetDoc As Object

oTargetDoc= ThisComponent Rem TargetDoc is the spreadsheet you are working from

Dim Prop(0) as New com.sun.star.beans.PropertyValue

Prop(0).name=“FilterName”
Prop(0).value=“MS Excel 97”
sUrl = convertToURL(“c:\Backup\oc\invts\portfolio.xls”)

if fileExists(sUrl) then
oDoc = stardesktop.LoadComponentFromURL(sUrl, “_blank”,0, Prop())
else
msgbox “Not found”
end if

Rem copy the relevant data up to row 100 and paste it into target doc
oSheet = oDoc.Sheets.getByName(“trades”) Rem oDoc is portfolio.xls

Anybody had a similar experience/ guess what might be happening here?

#2

There is no sheet with name »trades« inside …portfolio.xls ?!

#3

Thanks for the suggestion, but yes, there is a sheet with that name. The same thing happened in a very similar macro, also where the sheet name is valid. As I mentioned, the macro has not changed since I last used it successfully, and nor has the structure of the spreadsheet on which it works.

#4

Missing Dim oSheet As Object

#5

Thanks. Yes, I had tried that. It seems there are several instances of undefined variables, although I still haven’t managed to fix the whole subroutine. What I don’t understand is what changed? I’ve used this macro probably two or three times a week for a couple of years, and this suddenly started happening. Weird.
I looked at the program files and found an update log for 10/23/25. I suspect that is where the change happened. I’m wondering if I should file a bug report?

#6

If there is Option Explicit in the beginning of the Basic module where your function is defined, then the error is completely correct. If you added that line, than that’s it. If it was there before, and it still worked with that line, then it was a bug fixed in a recent version. If there is no Option Explicit, then it is a bug.

Note that you never mentioned the version of LibreOffice you are using.

2 Likes
#7

I didn’t change anything. So it’s almost certainly because of the 10/23/25 update. Thanks for confirming that. No doubt many of my other macros will now no longer work. Can you tell me, is it possible to revert to the earlier LO version?

#8

Version 25.2.6.2(X86_64)

#9

There’s no secret here.
Your module contains the

Option Explicit

statement.
This forces the programmer to explicitly declare all variables used in this module, significantly reducing the likelihood of accidental errors in the code.
In your macro, the oSheet variable is undeclared, as indicated by a runtime error.
In previous versions of LO, this error was sometimes not caught during code execution, leading to difficult-to-find errors.
Errors related to undeclared variables are easy to find. For example, we have an error in the line:

oSheet = oDoc.Sheets.getByName("trades")

We check whether we’ve declared the oSheet variable. If so, we check whether we’ve declared the oDoc variable, and so on.

2 Likes
#10

That fixed it. I had used ChatGPT to try and do some scraping code and its code had the Option Explicit. That was what was messing up my historical macros. Thanks.

1 Like

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