Help with slow running XL Automation Code

  • Thread starter Thread starter Bill Schanks
  • Start date Start date
B

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>>
 
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.
 
Bill said:
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?
Click to expand...

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.
 
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.
Click to expand...

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.
 
Back
Top