[Solved] Calc looping macro error
Posted: 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.
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.