[Solved] Calc looping macro error

Creating a macro - Writing a Script - Using the API

[Solved] Calc looping macro error

Postby Chair » Thu Aug 03, 2017 6:08 pm

Hello all,

Sorry if this is a jumbled mess. I have been working on this for awhile and my brain is turning to mush.

I have a sheet of data that has 2 columns. The first column repeats the numbers 240-398 back to back for 431,208 rows. The second column contains signal data (all unique numerical data).

I have a macro that strips out all but the first set of 240-398 (159 rows) in my first column and cuts all but the first 159 rows in the second column and pastes the cut rows in the third (unoccupied) column. This macro is then followed by a looped macro that will run 2711 times. This macro goes to the 160th row in the 3rd column and cuts all of the remaining data and pastes it into the 4th row and so on and so forth.

The second macro runs fine until its 422 loop. Then I get a "BASIC runtime error." The error reads: "An exception occurred, Type: com.cun.ctar.uno.RuntimeException, Message: [msci_uno bridge error] UNO type of C++ exception unknown: "std.bad_alloc", RTI-name=".?AVbad_alloc@std@@"!."


As far as I can see there is nothing different about this column. And the 422 columns it created look flawless. I have no idea why it stops at this point.


Here is the Second macro:


sub Second


rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(1) as new com.sun.star.beans.PropertyValue
args1(0).Name = "By"
args1(0).Value = 1
args1(1).Name = "Sel"
args1(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoLeft", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(1) as new com.sun.star.beans.PropertyValue
args2(0).Name = "By"
args2(0).Value = 1
args2(1).Name = "Sel"
args2(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDownToEndOfData", "", 0, args2())

rem ----------------------------------------------------------------------
dim args3(1) as new com.sun.star.beans.PropertyValue
args3(0).Name = "By"
args3(0).Value = 1
args3(1).Name = "Sel"
args3(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args3())

rem ----------------------------------------------------------------------
dim args4(1) as new com.sun.star.beans.PropertyValue
args4(0).Name = "By"
args4(0).Value = 1
args4(1).Name = "Sel"
args4(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, args4())

rem ----------------------------------------------------------------------
dim args5(1) as new com.sun.star.beans.PropertyValue
args5(0).Name = "By"
args5(0).Value = 1
args5(1).Name = "Sel"
args5(1).Value = true

dispatcher.executeDispatch(document, ".uno:GoDownToEndOfData", "", 0, args5())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Cut", "", 0, Array())

rem ----------------------------------------------------------------------
dim args7(1) as new com.sun.star.beans.PropertyValue
args7(0).Name = "By"
args7(0).Value = 1
args7(1).Name = "Sel"
args7(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, args7())

rem ----------------------------------------------------------------------
dim args8(1) as new com.sun.star.beans.PropertyValue
args8(0).Name = "By"
args8(0).Value = 1
args8(1).Name = "Sel"
args8(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoUpToStartOfData", "", 0, args8())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())


end sub




And here is how I run it so it will be looped:


Sub RunSecond()
Dim i As Long, n As Long

n = 2711
for i = 1 to n
Second()
next i
End Sub



Thank you so much for your time and any input.
Last edited by Chair on Fri Aug 04, 2017 4:12 pm, edited 1 time in total.
OpenOffice v. 4.1.1
Windows 10
Chair
 
Posts: 2
Joined: Thu Aug 03, 2017 5:40 pm

Re: Calc looping macro error

Postby Zizi64 » Thu Aug 03, 2017 8:17 pm

Please upload your real ODF type sample document here, and give us more details...
- Where the cursor is located, when you launched the macro?
- Where is (which cell, which column and row) is located the last non empty cell (the "EndOfData")?
Tibor Kovacs, Hungary; LO4.4.7, LO5.3.7 on Win7x64Prof.
PortableApps, WinPenPack: LO3.3.0-LO5.4.2 and AOO4.1.3
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 6079
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Calc looping macro error

Postby MrProgrammer » Thu Aug 03, 2017 11:04 pm

Hi, and welcome to the forum.

Chair wrote:… and pastes it into the 4th row …
Do you mean 4th column?

Chair wrote:I have a macro that strips out all but the first set of 240-398 (159 rows) in my first column and cuts all but the first 159 rows in the second column and pastes the cut rows in the third (unoccupied) column. This macro is then followed by a looped macro that will run 2711 times. This macro goes to the 160th row in the 3rd column and cuts all of the remaining data and pastes it into the 4th row and so on and so forth.
XY Problem
What is your goal? Is it to turn 431,208 rows × 1 column of values into 159 rows × 2712 columns of values?

Calc supports 1024 columns only.
Mr. Programmer
OpenOffice 3.2.0 Build 9483 on Mac OS X 10.9.5.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 3124
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Calc looping macro error

Postby Chair » Fri Aug 04, 2017 4:12 pm

Thank you for the fast response and sorry that I did not reply sooner. I have been working on the issue and have found a way around it that is sufficient for now. I have made made the loop count smaller in the sub then looped the entire sub inside of another sub with a smaller loop count to repeat the macro without it jamming.

Doing it this way it has successfully executed this macro.


Thank you for your time.
OpenOffice v. 4.1.1
Windows 10
Chair
 
Posts: 2
Joined: Thu Aug 03, 2017 5:40 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 6 guests