Macro procedure too large.... how can I shorten?

[sfried]

My spreadsheet has a specific macro embedded in it that I want to run many times (the command sets several items in the current row on spreadsheet, and then jumps to next row).
I set up my own macro to run the command many times in succession (for many rows). It sets calculation to Manual, then runs the command many times, and then put calculation back to automatic. This works fine for up to 300 times. But when I tried increasing to do 1,000 times, it became too big (Got "Compile Error: Procedure too large").

I imagine there's a better formula to create in the macro to run this. Can anyone help?

The Existing Macro I created to run 5 times.
Sub
Application.Calculation = xlManual


Range("c21").Select
Application.Run "'aaaStockMaster-andIB-all-2.xlsm'!Sheet1.requestGenericTicks"
Application.Run "'aaaStockMaster-andIB-all-2.xlsm'!Sheet1.requestGenericTicks"
Application.Run "'aaaStockMaster-andIB-all-2.xlsm'!Sheet1.requestGenericTicks"
Application.Run "'aaaStockMaster-andIB-all-2.xlsm'!Sheet1.requestGenericTicks"
Application.Run "'aaaStockMaster-andIB-all-2.xlsm'!Sheet1.requestGenericTicks"
Application.Run "'aaaStockMaster-andIB-all-2.xlsm'!Sheet1.requestGenericTicks"

Application.Calculation = xlAutomatic
End Sub

Using Excel 2007, windows Vista.






The command under requestGenericTicks is below (not sure you need this, but just in case)
Sub requestGenericTicks()
Dim server As String, req As String, reqType As String, id As String, secType As String, genericTicks
Dim delimiter As String
Dim i, j As Integer
Dim genericTicksArray() As String
Dim genericTicksRequestString As String
Dim supportedGenericTicksArray(0, 1) As Variant
supportedGenericTicksArray(0, 0) = 18 ' column offset for lastRthTrade
supportedGenericTicksArray(0, 1) = "318" ' lastRthTrade

server = util.getServerStr(serverCell)
If server = "" Then Exit Sub
Dim setSecType As String, setExchange As String
If Not util.composeContractReq(ActiveCell, req, reqType, True, , , , setSecType, setExchange) Then Exit Sub
id = util.getIDpost(genId)

' add generic ticks
genericTicks = ActiveCell.offset(0, 13).value
If genericTicks <> "" Then
delimiter = ","

' parse generic ticks string into array
genericTicksArray = VBA.Split(genericTicks, delimiter)

' check if generic tick is supported
Dim composeControlLink As Boolean
composeControlLink = False
For i = LBound(genericTicksArray) To UBound(genericTicksArray)
For j = LBound(supportedGenericTicksArray, 1) To UBound(supportedGenericTicksArray, 1)
If genericTicksArray(i) = supportedGenericTicksArray(j, 1) Then
genericTicksRequestString = genericTicksRequestString & genericTicksArray(i) & delimiter
ActiveCell.offset(0, reqOffset + supportedGenericTicksArray(j, 0)).Formula = util.composeLink(server, topicGenericTicks, id, supportedGenericTicksArray(j, 1))
composeControlLink = True
End If
Next
Next
If composeControlLink Then
genericTicksRequestString = Left(genericTicksRequestString, Len(genericTicksRequestString) - 1)
ActiveCell.offset(0, reqOffset + 1).Formula = util.composeControlLink(server, topicGenericTicks, id, reqType & "?" & genericTicksRequestString, req)
End If

End If

ActiveCell.offset(1, 0).Activate
End Sub

 

[Norie]

Where is all this code located?

 

[sfried]

Where is all this code located?

the GenericTick command line comes from an excel spreadsheet I got from a service provider... that runs some specific stuff related to the service provider's product. I'm not looking for work on that.

The macro I created, which I'm looking for help with.... runs the GenericTick command, MANY times in succession (1x for each row). I'm planning to have it run for 1,100 rows.

 

[Norie]

If you just want to run a line of code multiple times use a loop.

For I = 1 To 10000
    Application.Run "'aaaStockMaster-andIB-all-2.xlsm'!Sheet1.requestGenericTicks"
Next I

 

[sfried]

Awesome. Worked beautifully. I figured there was an easy command for it.