From: Bill Schanks on
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>>
From: aaron.kempf on
I'm sorry did you say you're making 8,000 named ranges?

-Aaron



On Mar 11, 2:14 pm, Bill Schanks <wscha...(a)gmail.com> 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?
>
> 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>>

From: Bill Schanks on
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, "aaron.ke...(a)gmail.com" <aaron.ke...(a)gmail.com>
wrote:
> I'm sorry did you say you're making 8,000 named ranges?
>
> -Aaron
>
> On Mar 11, 2:14 pm, Bill Schanks <wscha...(a)gmail.com> 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?
>
> > 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>>

From: Steve Gerrard on
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.


From: Bill Schanks on
<mynameh...(a)comcast.net> wrote:
> 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.