| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Procedure
I have been slowly adding to the following procedure and now it runs real
slow. Is there a better way to write it to speed it up? Sub addestimateitem() Dim cboxno, itemno As Integer unprotectsheet itemno = ActiveSheet.Range("estitemnum") + 1 Range("estitemnum").Value = itemno cboxno = ActiveSheet.Range("estcboxnum") + 1 Range("estcboxnum").Value = cboxno Range("esttotal").Select ActiveCell.Offset(itemno, 0).Name = "esttotalamt" Range("estno").Select ActiveCell.Offset(itemno, 0).Select Selection.EntireRow.Insert ActiveCell.Value = itemno Range(ActiveCell.Offset(0, 2), ActiveCell.Offset(0, 4)).Select mergecells Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 3)).Select mergecells ActiveCell.EntireRow.Cells(13).Value = cboxno ActiveCell.Offset(0, -5).Select Selection.RowHeight = 12.75 Selection.Font.Bold = False Selection.Font.Size = 10 Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, 9)).Select addborders ActiveCell.Offset(0, 1).Select Selection.Borders(xlEdgeRight).LineStyle = xlNone Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 7)).Select Selection.Locked = False Range(ActiveCell.Offset(0, 4), ActiveCell.Offset(0, 5)).Select Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)" With Selection.Font .Name = "Arial" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "=IF(RC[2],RC[-1],0)" ActiveCell.Offset(0, -8).Select addcheckbox Range("esttotal").Select Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(itemno, 0)).Select Selection.Name = "esttotalscol" Range("esttotalamt").Select Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)" With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With ActiveCell.FormulaR1C1 = "=sum(esttotalscol)" Range("estsubven").Select ActiveCell.Offset(itemno, 0).Select protectsheet End Sub Thank You |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Slow Procedure
The easiest thing to try is this... (otherwise you need to restructure the
logic to avoid ".select" and the multiple "ActiveCell." style of syntax.) At the top add: With Application .ScreenUpdating = False .EnableEvents = False .Calculation = xlCalculationManual End With At the bottom add: With Application .ScreenUpdating = True .EnableEvents = True .Calculation = xlCalculationAutomatic End With HTH -- Regards, John "ranswert" wrote: I have been slowly adding to the following procedure and now it runs real slow. Is there a better way to write it to speed it up? Sub addestimateitem() Dim cboxno, itemno As Integer unprotectsheet itemno = ActiveSheet.Range("estitemnum") + 1 Range("estitemnum").Value = itemno cboxno = ActiveSheet.Range("estcboxnum") + 1 Range("estcboxnum").Value = cboxno Range("esttotal").Select ActiveCell.Offset(itemno, 0).Name = "esttotalamt" Range("estno").Select ActiveCell.Offset(itemno, 0).Select Selection.EntireRow.Insert ActiveCell.Value = itemno Range(ActiveCell.Offset(0, 2), ActiveCell.Offset(0, 4)).Select mergecells Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 3)).Select mergecells ActiveCell.EntireRow.Cells(13).Value = cboxno ActiveCell.Offset(0, -5).Select Selection.RowHeight = 12.75 Selection.Font.Bold = False Selection.Font.Size = 10 Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, 9)).Select addborders ActiveCell.Offset(0, 1).Select Selection.Borders(xlEdgeRight).LineStyle = xlNone Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 7)).Select Selection.Locked = False Range(ActiveCell.Offset(0, 4), ActiveCell.Offset(0, 5)).Select Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)" With Selection.Font .Name = "Arial" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "=IF(RC[2],RC[-1],0)" ActiveCell.Offset(0, -8).Select addcheckbox Range("esttotal").Select Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(itemno, 0)).Select Selection.Name = "esttotalscol" Range("esttotalamt").Select Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)" With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With ActiveCell.FormulaR1C1 = "=sum(esttotalscol)" Range("estsubven").Select ActiveCell.Offset(itemno, 0).Select protectsheet End Sub Thank You |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Slow Procedure
|
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Slow Procedure
Thanks
any reccomendations on how I can and change cells without .select. "John Keith" wrote: The easiest thing to try is this... (otherwise you need to restructure the logic to avoid ".select" and the multiple "ActiveCell." style of syntax.) At the top add: With Application .ScreenUpdating = False .EnableEvents = False .Calculation = xlCalculationManual End With At the bottom add: With Application .ScreenUpdating = True .EnableEvents = True .Calculation = xlCalculationAutomatic End With HTH -- Regards, John "ranswert" wrote: I have been slowly adding to the following procedure and now it runs real slow. Is there a better way to write it to speed it up? Sub addestimateitem() Dim cboxno, itemno As Integer unprotectsheet itemno = ActiveSheet.Range("estitemnum") + 1 Range("estitemnum").Value = itemno cboxno = ActiveSheet.Range("estcboxnum") + 1 Range("estcboxnum").Value = cboxno Range("esttotal").Select ActiveCell.Offset(itemno, 0).Name = "esttotalamt" Range("estno").Select ActiveCell.Offset(itemno, 0).Select Selection.EntireRow.Insert ActiveCell.Value = itemno Range(ActiveCell.Offset(0, 2), ActiveCell.Offset(0, 4)).Select mergecells Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 3)).Select mergecells ActiveCell.EntireRow.Cells(13).Value = cboxno ActiveCell.Offset(0, -5).Select Selection.RowHeight = 12.75 Selection.Font.Bold = False Selection.Font.Size = 10 Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, 9)).Select addborders ActiveCell.Offset(0, 1).Select Selection.Borders(xlEdgeRight).LineStyle = xlNone Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 7)).Select Selection.Locked = False Range(ActiveCell.Offset(0, 4), ActiveCell.Offset(0, 5)).Select Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)" With Selection.Font .Name = "Arial" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "=IF(RC[2],RC[-1],0)" ActiveCell.Offset(0, -8).Select addcheckbox Range("esttotal").Select Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(itemno, 0)).Select Selection.Name = "esttotalscol" Range("esttotalamt").Select Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)" With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With ActiveCell.FormulaR1C1 = "=sum(esttotalscol)" Range("estsubven").Select ActiveCell.Offset(itemno, 0).Select protectsheet End Sub Thank You |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Slow Procedure
One example...
Range("esttotal").Select ActiveCell.Offset(itemno, 0).Name = "esttotalamt" The above can be replaced with... Range("esttotal").Offset(itemno, 0).Name = "esttotalamt" One other note; this line.... Dim cboxno, itemno As Integer is not doing what you think. Only itemno is declared as an Integer; cboxno defaults to a Variant. In VB/VBA, one must explicitly declare each variable or it defaults to Variant. So, you should do either this... Dim cboxno As Integer, itemno As Integer or this.... Dim cboxno As Integer Dim itemno As Integer Rick "ranswert" wrote in message ... Thanks any reccomendations on how I can and change cells without .select. "John Keith" wrote: The easiest thing to try is this... (otherwise you need to restructure the logic to avoid ".select" and the multiple "ActiveCell." style of syntax.) At the top add: With Application .ScreenUpdating = False .EnableEvents = False .Calculation = xlCalculationManual End With At the bottom add: With Application .ScreenUpdating = True .EnableEvents = True .Calculation = xlCalculationAutomatic End With HTH -- Regards, John "ranswert" wrote: I have been slowly adding to the following procedure and now it runs real slow. Is there a better way to write it to speed it up? Sub addestimateitem() Dim cboxno, itemno As Integer unprotectsheet itemno = ActiveSheet.Range("estitemnum") + 1 Range("estitemnum").Value = itemno cboxno = ActiveSheet.Range("estcboxnum") + 1 Range("estcboxnum").Value = cboxno Range("esttotal").Select ActiveCell.Offset(itemno, 0).Name = "esttotalamt" Range("estno").Select ActiveCell.Offset(itemno, 0).Select Selection.EntireRow.Insert ActiveCell.Value = itemno Range(ActiveCell.Offset(0, 2), ActiveCell.Offset(0, 4)).Select mergecells Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 3)).Select mergecells ActiveCell.EntireRow.Cells(13).Value = cboxno ActiveCell.Offset(0, -5).Select Selection.RowHeight = 12.75 Selection.Font.Bold = False Selection.Font.Size = 10 Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, 9)).Select addborders ActiveCell.Offset(0, 1).Select Selection.Borders(xlEdgeRight).LineStyle = xlNone Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 7)).Select Selection.Locked = False Range(ActiveCell.Offset(0, 4), ActiveCell.Offset(0, 5)).Select Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)" With Selection.Font .Name = "Arial" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "=IF(RC[2],RC[-1],0)" ActiveCell.Offset(0, -8).Select addcheckbox Range("esttotal").Select Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(itemno, 0)).Select Selection.Name = "esttotalscol" Range("esttotalamt").Select Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)" With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With ActiveCell.FormulaR1C1 = "=sum(esttotalscol)" Range("estsubven").Select ActiveCell.Offset(itemno, 0).Select protectsheet End Sub Thank You |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Slow Procedure
Thanks I will give it a try.
"Rick Rothstein (MVP - VB)" wrote: One example... Range("esttotal").Select ActiveCell.Offset(itemno, 0).Name = "esttotalamt" The above can be replaced with... Range("esttotal").Offset(itemno, 0).Name = "esttotalamt" One other note; this line.... Dim cboxno, itemno As Integer is not doing what you think. Only itemno is declared as an Integer; cboxno defaults to a Variant. In VB/VBA, one must explicitly declare each variable or it defaults to Variant. So, you should do either this... Dim cboxno As Integer, itemno As Integer or this.... Dim cboxno As Integer Dim itemno As Integer Rick "ranswert" wrote in message ... Thanks any reccomendations on how I can and change cells without .select. "John Keith" wrote: The easiest thing to try is this... (otherwise you need to restructure the logic to avoid ".select" and the multiple "ActiveCell." style of syntax.) At the top add: With Application .ScreenUpdating = False .EnableEvents = False .Calculation = xlCalculationManual End With At the bottom add: With Application .ScreenUpdating = True .EnableEvents = True .Calculation = xlCalculationAutomatic End With HTH -- Regards, John "ranswert" wrote: I have been slowly adding to the following procedure and now it runs real slow. Is there a better way to write it to speed it up? Sub addestimateitem() Dim cboxno, itemno As Integer unprotectsheet itemno = ActiveSheet.Range("estitemnum") + 1 Range("estitemnum").Value = itemno cboxno = ActiveSheet.Range("estcboxnum") + 1 Range("estcboxnum").Value = cboxno Range("esttotal").Select ActiveCell.Offset(itemno, 0).Name = "esttotalamt" Range("estno").Select ActiveCell.Offset(itemno, 0).Select Selection.EntireRow.Insert ActiveCell.Value = itemno Range(ActiveCell.Offset(0, 2), ActiveCell.Offset(0, 4)).Select mergecells Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 3)).Select mergecells ActiveCell.EntireRow.Cells(13).Value = cboxno ActiveCell.Offset(0, -5).Select Selection.RowHeight = 12.75 Selection.Font.Bold = False Selection.Font.Size = 10 Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, 9)).Select addborders ActiveCell.Offset(0, 1).Select Selection.Borders(xlEdgeRight).LineStyle = xlNone Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 7)).Select Selection.Locked = False Range(ActiveCell.Offset(0, 4), ActiveCell.Offset(0, 5)).Select Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)" With Selection.Font .Name = "Arial" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "=IF(RC[2],RC[-1],0)" ActiveCell.Offset(0, -8).Select addcheckbox Range("esttotal").Select Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(itemno, 0)).Select Selection.Name = "esttotalscol" Range("esttotalamt").Select Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)" With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With ActiveCell.FormulaR1C1 = "=sum(esttotalscol)" Range("estsubven").Select ActiveCell.Offset(itemno, 0).Select protectsheet End Sub Thank You |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Stop a Procedure from another procedure | Excel Discussion (Misc queries) | |||
| Slow Excel Navigation with Up / Down Arrow and slow scrolling | Excel Discussion (Misc queries) | |||
| How to jump from a Form procedure to a Workbook or Module procedure? | Excel Programming | |||
| Calling a procedure in a procedure | Excel Programming | |||
Linear Mode
