Page 1 of 1
[Solved] Macro fails on reload of spreadsheet
Posted: Sat Apr 19, 2014 11:15 pm
by remmons
I ported a VBA macro from Excel, got it to work, and saved the spreadsheet as an OpenOffice ODS file. When I re-opened the spreadsheet, the macro gave this error:
BASIC runtime error
Sub-procedure or function procedure not defined.
on this line:
vps = Range("f6")
I see nothing wrong with the line. Here is the weird thing: If I insert a <CarrigeReturn> before the line, and then remove it, the macro runs correctly with no problem. However, if I save the spreadsheed, and re-load it, the macro fails again. If I repeat the insert and remove of the <CarriageReturn> (or any other character), the macro works again.
What could cause this weird problem? How can I get the macro to work?
Re: Macro fails on reload of spreadsheet
Posted: Sun Apr 20, 2014 2:34 am
by Charlie Young
remmons wrote:
vps = Range("f6")
Are you trying to use some VBA compatibilty mode? Range() is not an OO Object, In OO Basic it would look something like
Where Sheet is a variable referring to some sheet in the document, maybe
Code: Select all
Sheet = ThisComponent.getSheets().getByName("Sheet1")
Re: Macro fails on reload of spreadsheet
Posted: Sun Apr 20, 2014 6:13 am
by remmons
Thanks for the info. No as far as I know I am not using any compatibility mode.
I just loaded the Excel spreadsheet into Calc, and it REMed out all my macros. I just removed the REMs, expecting to debug any statements that did not work. To my surprise, I hardly had to change anything, and the code worked. Range("cell") may not be a Calc object, but I use it all over the place in Excel, and Calc seems to understand it with no problem. The macro does a fairly complicated Newton-Raphson cut and try loop to calculate a rate of return on investment, and gets the same answer as I got in Excel.
Maybe I'll try changing all the Range("cell") statements as you suggested.
Re: Macro fails on reload of spreadsheet
Posted: Sun Apr 20, 2014 2:41 pm
by Charlie Young
remmons wrote:When I re-opened the spreadsheet, the macro gave this error:
BASIC runtime error
Sub-procedure or function procedure not defined.
on this line:
vps = Range("f6")
remmons wrote:Range("cell") may not be a Calc object, but I use it all over the place in Excel, and Calc seems to understand it with no problem. The macro does a fairly complicated Newton-Raphson cut and try loop to calculate a rate of return on investment, and gets the same answer as I got in Excel.
This would seem to be a contradiction.
Without seeing more of your code, it is difficult to diagnose, so I can only guess why part of it works. In a mathematical calculation like Newton-Raphson, the formulation of the mathematical expressions and the loop structures should be identical in Excel and Calc.
We've dealt with Newton-Raphson calculations in this forum before, and I have my old numerical analysis textbook close by the computer.
Re: Macro fails on reload of spreadsheet
Posted: Sun Apr 20, 2014 10:41 pm
by remmons
It is not that *part* of it works. All the code works sometimes. But, when I first load the sheet, it crashes. I edit the code by putting in a <CarriageReturn> and then deleting it. Then, all the code works again although there has been no actual change to the code. The act of editing the code causes it to re-compile or something behind the scenes, and that causes it to work.
I was hoping that get lucky, and the code would just work or require minimal troubleshooting. Apparently, that is not the case.
At this point, I plan to remove all the code, and put it back in line by line to isolate whatever is causing this weird problem. It is really very few lines of code anyhow.
Re: Macro fails on reload of spreadsheet
Posted: Mon Apr 21, 2014 3:18 am
by RusselB
In a way it does require minimal troubleshooting.. you have to add, then remove, a <CarriageReturn> .. to my mind, that is minimal... the part that isn't, is the fact that you have to do it everytime you open your sheet.. which, if you open it frequently, would be a pain in more places than one.
Re: Macro fails on reload of spreadsheet
Posted: Mon Apr 21, 2014 1:33 pm
by Charlie Young
How does it go if you add this to the top of your Basic module (in the .ods, before any executable code)?
Edit:
I have managed to reproduce the weird behavior with a simple case. In Excel, I created a file (.xls) containing the sub
Code: Select all
Sub RangeBlurt()
MsgBox (Range("A1").Value)
End Sub
Then opened the .xls in Calc, removed the Rems, and the macro worked. I then saved as .ods, reopened it, and yes, it behaved as described, right down to the insert/delete of a carriage return.
Now, I also find that the Option VBASupport 1 fixes the problem. It would seem that Calc, on editing the module, detects the Range(), and automatically goes into the VBASupport mode, but that's just a guess. I don't know how thorough the VBASupport is, that is how much beyond just interpreting Range() it can handle.
I would, in any case, try to translate the macro into the OpenOffice API if I were going to use it in Calc regularly. If, as you say, it isn't that long, it shouldn't be that hard to do, especially if most of the VBA specific stuff is not much more than a few Range() things. Note that the use of Range(Cell) here refers to the active sheet, and in AOO, thats
Code: Select all
ThisComponent.CurrentController.ActiveSheet
So, as I mentioned above, you could do (I prefer using methods to properties generally)
Code: Select all
ThisComponent.getCurrentController().getActiveSheet().getCellRangeByName(cellname)
Re: Macro fails on reload of spreadsheet
Posted: Mon Apr 21, 2014 5:54 pm
by remmons
Thanks for the help. I added the
Option VBASupport 1
and it seems to have fixed the problem.
As far as using Calc regularly, that is what I am investigating. I am getting tired of paying Microsoft prices, and getting locked into their proprietary OS and Office Suite.