Help with slow running XL Automation Code

[Bill Schanks]

I have this code that takes ~3 Mins to run while automating an XL
Instance (XL 2003). There are 558 Rows and 15 Columns in rngSelection.
So it is adding Named ranges on 8,000+ Cells. Is this just as good as
it's going to get?

Imports Excel = Microsoft.Office.Interop.Excel
<<Snip>>
With XL
.Application.Calculation =
Excel.XlCalculation.xlCalculationManual
.Application.ScreenUpdating = False
<<Snip>>
Dim rngSelection As Excel.Range = CType(.Range("A1").CurrentRegion,
Excel.Range)
.Range("B3").Select()
For iRow = 2 To CShort(rngSelection.Rows.Count)
For iColumn = 2 To
CShort(rngSelection.Columns.Count)

.ActiveWorkbook.Names.Add(Name:="_" _
& Trim(Replace(CType(.ActiveCell.Value,
String), "-", "_")) & "_" _
& CType(.Range("O2").Offset(0, -
rngSelection.Columns.Count + iColumn + 1).Value, String) _
, RefersToR1C1:=.ActiveCell.Offset(0,
iColumn - 1))

Next iColumn
.ActiveCell.Offset(1, 0).Select()
Next iRow
<<Snip>>

[aaron.kempf]

I'm sorry did you say you're making 8,000 named ranges?

-Aaron

On Mar 11, 2:14*pm, Bill Schanks <[e-mail]:

I have this code that takes ~3 Mins to run while automating an XL
Instance (XL 2003). There are 558 Rows and 15 Columns in rngSelection.
So it is adding Named ranges on 8,000+ Cells. Is this just as good as
it's going to get?

Imports Excel = Microsoft.Office.Interop.Excel
<<Snip>>
With XL
* * *.Application.Calculation =
Excel.XlCalculation.xlCalculationManual
* * *.Application.ScreenUpdating = False
<<Snip>>
Dim rngSelection As Excel.Range = CType(.Range("A1").CurrentRegion,
Excel.Range)
* * * * * * * * .Range("B3").Select()
* * * * * * * * For iRow = 2 To CShort(rngSelection.Rows..Count)
* * * * * * * * * * For iColumn = 2 To
CShort(rngSelection.Columns.Count)

* * * * * * * * * * * * .ActiveWorkbook.Names.Add(Name:="_" _
* * * * * * * * * * * * * * & Trim(Replace(CType(.ActiveCell.Value,
String), "-", "_")) & "_" _
* * * * * * * * * * * * * * & CType(.Range("O2").Offset(0, -
rngSelection.Columns.Count + iColumn + 1).Value, String) _
* * * * * * * * * * * * * * , RefersToR1C1:=..ActiveCell.Offset(0,
iColumn - 1))

* * * * * * * * * * Next iColumn
* * * * * * * * * * .ActiveCell.Offset(1, 0).Select()
* * * * * * * * Next iRow
<<Snip>>

[Bill Schanks]

Yes ... It's a long story. I took over support of this process and the
end result is that are multiple spreadsheets that link to this
spreadsheet via a named range. It's not perfect and needs to be re-
written. With my current set of projects it's just going to happen any
time soon.

So for now, I just need to make the current process work.

On Mar 12, 11:21 am, "[e-mail]" <[e-mail]>
wrote:

I'm sorry did you say you're making 8,000 named ranges?

-Aaron

On Mar 11, 2:14 pm, Bill Schanks <[e-mail]:

I have this code that takes ~3 Mins to run while automating an XL
Instance (XL 2003). There are 558 Rows and 15 Columns in rngSelection.
So it is adding Named ranges on 8,000+ Cells. Is this just as good as
it's going to get?

Imports Excel = Microsoft.Office.Interop.Excel
<<Snip>>
With XL
.Application.Calculation =
Excel.XlCalculation.xlCalculationManual
.Application.ScreenUpdating = False
<<Snip>>
Dim rngSelection As Excel.Range = CType(.Range("A1").CurrentRegion,
Excel.Range)
.Range("B3").Select()
For iRow = 2 To CShort(rngSelection.Rows.Count)
For iColumn = 2 To
CShort(rngSelection.Columns.Count)

.ActiveWorkbook.Names.Add(Name:="_" _
& Trim(Replace(CType(.ActiveCell.Value,
String), "-", "_")) & "_" _
& CType(.Range("O2").Offset(0, -
rngSelection.Columns.Count + iColumn + 1).Value, String) _
, RefersToR1C1:=.ActiveCell.Offset(0,
iColumn - 1))

Next iColumn
.ActiveCell.Offset(1, 0).Select()
Next iRow
<<Snip>>

[Steve Gerrard]

Bill Schanks wrote:

I have this code that takes ~3 Mins to run while automating an XL
Instance (XL 2003). There are 558 Rows and 15 Columns in rngSelection.
So it is adding Named ranges on 8,000+ Cells. Is this just as good as
it's going to get?

That's 8,000 inter-process marshals and calls. It's going to stay slow.

Any chance you can set this up so the code runs as a macro within the Excel
workbook? It would go much faster there. Maybe You could even inject the macro
into the workbook, then call it.

[Bill Schanks]

<[e-mail]:

That's 8,000 inter-process marshals and calls. It's going to stay slow.

Any chance you can set this up so the code runs as a macro within the Excel
workbook? It would go much faster there. Maybe You could even inject the macro
into the workbook, then call it.

Actually that's were it is now, and I am pulling it out for other
reasons. But I can setup new code within the book and then call that
macro.Thanks.

[Bill Schanks]

That dropped the execution time to below 20s ... Thanks.