MS Excel file macros cause infinite loop of errors

users
#1

Trying to determine where
      best to request help.A file sent to me includes VB macros created in MS Excel.The
      macro handler in LibreOffice (both 3.6 and 4.0) end up in an
      exception loop which cannot be stopped unless I kill the process
      from the system command line.Is there a specific way to address the Subject line or a different
      forum/mailing list I should use for requesting help?I wanted to make sure I send to the correct place before
      cluttering up folks' Inbox with a hefty file.

#2

Hi :slight_smile:
This is the correct place. There are a few different ways of viewing the
emails sent to the list. Nabble is the method that has the prettiest gui.

Normals attachments to the list get stripped of to prevent exactly the
problem you are worrying about there. However, you can upload files to
Nabble by clicking the "More" button just above where you type your message.
The top-option there is to "upload file". That then puts a link to the
upload in the message in a way that looks a lot like an html link. So that
everyone gets the link whichever method they use to view emails to the list.
People can then choose to click on that link or not.

When you open the file that contains a macro you should be given a choice of
ignoring the macro or allowing it. Macros can be pretty nasty things and
can easily be corrupted by clever malware so it's best to avoid using them.
LibreOffice offers a choice of languages to write macros in and even the one
closest to MS Office's tends to be a lot safer.

Regards from
Tom :slight_smile:

#3

Todd

Either upload the file to Nabble or post the file some and provide a link.

I am not expert on macros but infinite loops are caused by a serious logic error - not providing a loop exit condition that tests correctly. I would expect the problem to in a WHILE/DO loop where there most be an exit condition that terminates the loop.

For example

i = 10
While i > 1 do
     loop body
     i = i + 1

This above will always test TRUE and never exit. I have explicitly shown i incrementing.

But the following will exit because the loop condition will eventually evaluate to false:

i = 10
While i < 1000 do
     loop body
     i = i + 1

Another error is for the above examples is to forget to increment i

#4

There's also the ever-popular testing for an exact floating-point value
[;<).

The OP mentions there being an *exception* loop, so it sounds like LO is
throwing a script error exception message and somehow there is no way out of
it.

It looks like two things are needed:

1. The complete, exact exception message about what is wrong.

2. Opening the document with macros disabled (if that is possible when
conversion from .xls is happening) and extracting the resulting script.

(An alternative is to open the document in Excel, which should allow macro
disabling, and extract the original VBA there.)

Speculating further, it may well be that the VBA has a dependency that can't
be resolved on the users system from LO. Either way, the script
error/failure behavior of LO needs to be looked into.

- Dennis

#5

Originally I posted under my todd.moore@dynamicsystemsinc.com
account.Unfortunately, I need the macros enabled. The spreadsheet is
developed by another company and includes features/functions that we must
use to properly submit requests.The behavior I observe is that after
changing security settings to allow the macros to run, I receive a macro
execution error that pops up on the screen. Clicking on the equivalent of
"ok", "ignore", "cancel" just displays another error message. This
continues for what seems about 30 clicks. Then the boxes no longer appear.
Trying to do anything in the UI, even closing the file, starts the error
message deluge again.I noticed the issue in v3.x.x and hoped that with
v4.0.0 there may have been changes that would cope with the macros in this
file. Unfortunately, the problems persist.Attaching file (1MB zip file
containing a single MS Excel ) that causes this issue to occur.
File_with_Bad_Macro.zip
<http://nabble.documentfoundation.org/file/n4036499/File_with_Bad_Macro.zip>

#6

Hi :slight_smile:
That went really wrong for me when i tried with 4.0.0 but opened fine in 'OpenOffice.org 3.2'.  Hmmm the OOo 3.2 says it was made by RedHat based on OOo from Sun so that probably really means it was Go-oo rather than OOo and Go-oo merged into LibreOffice really early on.  I'm currently on a really ancient version of Fedora rather than my usual Ubuntu.  RedHat use Fedora to test out new ideas without risking the stability of their main OS. It's quite good fun to use sometimes :)  Many people find it stable enough to run as their main OS.  Anyway, point is that the file opens in 3.2 and that probably means LibreOffice 3.2
Regards from
Tom :slight_smile:

#7

I found v3.3 in the OO archives download area. It opens the file, but none
of the macros work. Bummer.

With this particular file, I run into problems even with MS Office 2003 and
we're loathe to invest the money into upgrading licenses because of a single
file. Unfortunately, it's looking more and more likely that will be the
route we'll end up travelling.

Thanks for the assist. Maybe developers can reference this file when they
next revisit the macro engine.

#8

Hi :slight_smile:
Is it a file that everyone needs access to at their own desks?  Is it possible to just have 1 desktop with MSO on it and then people update the file from there?

If the other organisation is not Oracle then they might well be willing to re-write the code or have it re-written or allow it to be re-written "for improved security" and to allow it to be used with Free Software.  A lot of people start off with both MS Office and LibreOffice/OpenOffice on their machines at the beginning of a migration or just to be able to deal with a wider variety of clients.  Perhaps the organisation that needs this spreadsheet filled in might be willing to install LibreOffice on their machines rather than forcing you to buy a lot of licenses for something you wouldn't otherwise need.

It seems you have some leverage there.  There are probably people on this list that wouldn't mind being paid a little to translate the macros and at far less than the cost of a lot of MSO licenses.  I'm no good with macros btw, jic you were wondering.   
Regards from
Tom :slight_smile:

#9

I found v3.3 in the OO archives download area. It opens the file, but none
of the macros work. Bummer.

With this particular file, I run into problems even with MS Office 2003 and
we're loathe to invest the money into upgrading licenses because of a single
file. Unfortunately, it's looking more and more likely that will be the
route we'll end up travelling.

It would not run correctly on MSO 2003? To me this implies is/are bug(s) in the macro code. And if it is buggy code upgrading your license will not help. Since the file is an *.xls file this implies the macro was intended to run on Excel 2003 and possibly even 2000 as well as later versions.

Thanks for the assist. Maybe developers can reference this file when they
next revisit the macro engine.

--
View this message in context: http://nabble.documentfoundation.org/MS-Excel-file-macros-cause-infinite-loop-of-errors-tp4036471p4036526.html
Sent from the Users mailing list archive at Nabble.com.

I was able to look at the VBA code for the macro in LO 4.0 so if one know the correct Basic dialect it could be (theoretically) converted but I have no idea how easy or difficult this would be. Hopefully, one the macro gurus can take a look at the code. On some of the code I did get a syntax error but I am not sure if the error is generic (occurs in VBA and LO Basic) or only specific to LO Basic.

I did not notice any documentation or comments that explained the code in the section I was looking at (bangs head on desk). Also, I did not see any copyright notice or license in the modules I looked at.

#10

LO is not macro compatible with MS Office products. If it is a VBA macro meant for use with Excel, the macro is not expected to work with LO. In some trivial cases, it may just happen to work, but that is the exception (in my experience), not the rule.

#11

Amazed that a private company receives a proprietary file from another
private company and asks the libre software community to solve a
proprietary problem.

LO is not an m$ clone. A business that can't afford to pay m$
licences, most probably shouldn't be in business.

How does odf benefit from LO being used to solve your problem?

Have you contacted the source of the document and asked them to
re-format the document to ods?

What did they say? :wink:

Have you contacted m$ and asked them to make their software easier to
use with non-m$ products? Did you get a reply ? :wink:

How much company time have you wasted, compared to the cost of buying
a legitimate copy of m$? Is your MD aware?

#12

e-letter

The issue is macro conversion which is tricky at best. As budgets tighten many are looking for less expensive or free options for software and these issues are important to address in a polite manner not snide comments about the validity of their license or how they should use MSO. MS does not by their choice have versions of MSO for all OSes so depending on your OS you may not even have an option to use MSO.

In previous post, it was noted that the macro may be buggy because it apparently does not always run correctly in Excel (2003). So there may have two independent problems: a buggy macro and macro conversion/interoperability.

Macros are the hardest area to convert because of differences in the APIs between MSO and LO.

#13

Hi :slight_smile:
+1
to all of Jay's various points there.  I totally agree.

The "another private company", the one that sent the offending spreadsheet, appears to be Oracle.  So,
1.  it's extremely unlikely that they will re-write the macros for LibreOffice or even for OpenOffice (same macro languages) 
2.  that fact the macros seem horribly broken is not a surprise either and they probably wont fix them
3.  If we could fix them and send to the original poster so that he could send the results to the "another company" in an ODF document then that would be a bit of a triumph for all of us.

Yes, it would be nice if someone here got paid for the work but that is unlikely to happen.  Ideally i would like someone here to get paid perhaps somewhat less than the cost of new MSO licenses for the op's machines.  Perhaps Andrew Pitonyak, Alex, Jay, Dan(?) or someone else that knows macro coding well enough to get it done quite quickly.

At the moment the company that approached us doesn't have any versions of MSO (apart fromt he old 2003 lurking somewhere) and it's only being forced into buying MSO by this 1 document from Oracle!!  It's outrageous that Oracle would do such a thing but again, no surprise!  So, even if no-one got paid it would still be a good result for us.

Regards from
Tom :slight_smile:

#14

My original objective in submitting to the forum was to determine if an LO/OO "bug" or "limitation" was being exposed by the macros found in the sample file rather than having folks in the development team or community expend the effort to fix the offending file. To me, the issue is now closed.