Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unpleasant VBA Agility
Hi
My VBA applications are too agile. I am trying to sell a few VBA applications on accounting but am afraid the said problem will be a negative factor. Cannot VBA made to perform below the surface with an inbuilt macro screening it? Varne M |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unpleasant VBA Agility
Just what does "agile" mean in the context of VBA?
"Varne" wrote in message ... Hi My VBA applications are too agile. I am trying to sell a few VBA applications on accounting but am afraid the said problem will be a negative factor. Cannot VBA made to perform below the surface with an inbuilt macro screening it? Varne M |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unpleasant VBA Agility
Hi Chip
Thanks If you click for action or report in an accounting package it comes to your screen with just one change. However underneath the dbms must be gathering data or consolidating data from many tables. You do not see it. Now VBA does it in front of you running to and from pages, fields and cells. Cannot this be hidden? Varne M "Chip Pearson" wrote: Just what does "agile" mean in the context of VBA? "Varne" wrote in message ... Hi My VBA applications are too agile. I am trying to sell a few VBA applications on accounting but am afraid the said problem will be a negative factor. Cannot VBA made to perform below the surface with an inbuilt macro screening it? Varne M |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unpleasant VBA Agility
Perhaps this helps:
at start of macro: Application.ScreenUpdating = False at end: Application.ScreenUpdating = True best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Varne" wrote in message ... Hi Chip Thanks If you click for action or report in an accounting package it comes to your screen with just one change. However underneath the dbms must be gathering data or consolidating data from many tables. You do not see it. Now VBA does it in front of you running to and from pages, fields and cells. Cannot this be hidden? Varne M "Chip Pearson" wrote: Just what does "agile" mean in the context of VBA? "Varne" wrote in message ... Hi My VBA applications are too agile. I am trying to sell a few VBA applications on accounting but am afraid the said problem will be a negative factor. Cannot VBA made to perform below the surface with an inbuilt macro screening it? Varne M |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unpleasant VBA Agility
The code writing tecnique can also eliminate a lot of the flutter and
flicker. The more you eliminate the select and activate methods, the less you see the sheets jumping on the screen. I don't know if it can be completely eliminated, but it can be greatly reduced. "Varne" wrote: Hi Chip Thanks If you click for action or report in an accounting package it comes to your screen with just one change. However underneath the dbms must be gathering data or consolidating data from many tables. You do not see it. Now VBA does it in front of you running to and from pages, fields and cells. Cannot this be hidden? Varne M "Chip Pearson" wrote: Just what does "agile" mean in the context of VBA? "Varne" wrote in message ... Hi My VBA applications are too agile. I am trying to sell a few VBA applications on accounting but am afraid the said problem will be a negative factor. Cannot VBA made to perform below the surface with an inbuilt macro screening it? Varne M |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unpleasant VBA Agility
Hi
Try putting "application.screenupdating = false" at the starts of macros for which you wish to hide the "agility" factor. This command hides changes of sheets,data updating visually and a host of others actions You may turn on screenupdating gain with "application.screenupdating = true" Happy hunting Regards N10 "Varne" wrote in message ... Hi Chip Thanks If you click for action or report in an accounting package it comes to your screen with just one change. However underneath the dbms must be gathering data or consolidating data from many tables. You do not see it. Now VBA does it in front of you running to and from pages, fields and cells. Cannot this be hidden? Varne M "Chip Pearson" wrote: Just what does "agile" mean in the context of VBA? "Varne" wrote in message ... Hi My VBA applications are too agile. I am trying to sell a few VBA applications on accounting but am afraid the said problem will be a negative factor. Cannot VBA made to perform below the surface with an inbuilt macro screening it? Varne M |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unpleasant VBA Agility
Besides the mentioned screen updating, you may do the same with
calculations: Application.Calculation = xlCalculationManual You can reset this to: Application.Calculation = xlCalculationAutomatic after your macro runs. Use early binding, and work with objects. This allows you to set With blocks, but also improves performance. One example of this is CreateObject() function. Whenever possible, include a reference to a library instead and do a strict type. Instead of this: dim objWord as Object set objWord = CreateObject("Word.Application") use this: dim objWord as Word.Application set objWord = new Word.Application A similar principle applies to using Selection, ActiveCell, ActiveSheet, etc. If you need to do something with the current selection (assuming on a worksheet), do this: dim rngSelection as Excel.Range set rngSelection = Application.Selection Or, if you need to use active sheet, do this: dim wshSheet as Excel.Worksheet set wshSheet = Application.ActiveSheet As mentioned above, do not "select" stuff to work on it. Macro recorder will generate code like this: ActiveSheet.Range("A1:D10").Select Selection.Font.Bold = True Instead, do this: ActiveSheet.Range("A1:D10").Font.Bold = True Use With blocks whenever possible. For instance, you might be tempted to write code like this: objWorksheet.Name = "My Worksheet" objWorksheet.Visible = xlSheetHidden objWorksheet.EnableAutoFilter = False objWorksheet.PageSetup.FitToPagesTall = 2 objWorksheet.PageSetup.FitToPagesWide = 1 objWorksheet.PageSetup.Zoom = 70 This is not efficient, because the objWorksheet is resolved each time. Here's how it ought to be done: With objWorksheet .Name = "My Worksheet" .Visible = xlSheetHidden .EnableAutoFilter = False With .PageSetup .FitToPagesTall = 2 .FitToPagesWide = 1 .Zoom = 70 End With End With Those are some main beginner inefficiencies (not sure of your level). Of course, code optimization is a complex subject, so it's difficult to tell which suggestions will work best for your particular situation. Perhaps if you describe some of the functionalities provided by your add-in some more examples specific to your situation can be provided. Good luck. -Ilia On May 20, 8:58 pm, Varne wrote: Hi Withholding screen updating helps. Thank you Bernard and N10. Whiz Could you please send 2 examples of best practice codes and corresponding untrained programmer's entries to Many Thanks Varne M "N10" wrote: Hi Try putting "application.screenupdating = false" at the starts of macros for which you wish to hide the "agility" factor. This command hides changes of sheets,data updating visually and a host of others actions You may turn on screenupdating gain with "application.screenupdating = true" Happy hunting Regards N10 "Varne" wrote in message ... Hi Chip Thanks If you click for action or report in an accounting package it comes to your screen with just one change. However underneath the dbms must be gathering data or consolidating data from many tables. You do not see it. Now VBA does it in front of you running to and from pages, fields and cells. Cannot this be hidden? Varne M "Chip Pearson" wrote: Just what does "agile" mean in the context of VBA? "Varne" wrote in message ... Hi My VBA applications are too agile. I am trying to sell a few VBA applications on accounting but am afraid the said problem will be a negative factor. Cannot VBA made to perform below the surface with an inbuilt macro screening it? Varne M- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unpleasant VBA Agility
But is this:
objWorksheet.Name = "My Worksheet" objWorksheet.Visible = xlSheetHidden objWorksheet.EnableAutoFilter = False Any slower than this: With objWorksheet .Name = "My Worksheet" .Visible = xlSheetHidden .EnableAutoFilter = False End With I thought it was the number of dots that mattered. Just wondering. RBS "ilia" wrote in message oups.com... Besides the mentioned screen updating, you may do the same with calculations: Application.Calculation = xlCalculationManual You can reset this to: Application.Calculation = xlCalculationAutomatic after your macro runs. Use early binding, and work with objects. This allows you to set With blocks, but also improves performance. One example of this is CreateObject() function. Whenever possible, include a reference to a library instead and do a strict type. Instead of this: dim objWord as Object set objWord = CreateObject("Word.Application") use this: dim objWord as Word.Application set objWord = new Word.Application A similar principle applies to using Selection, ActiveCell, ActiveSheet, etc. If you need to do something with the current selection (assuming on a worksheet), do this: dim rngSelection as Excel.Range set rngSelection = Application.Selection Or, if you need to use active sheet, do this: dim wshSheet as Excel.Worksheet set wshSheet = Application.ActiveSheet As mentioned above, do not "select" stuff to work on it. Macro recorder will generate code like this: ActiveSheet.Range("A1:D10").Select Selection.Font.Bold = True Instead, do this: ActiveSheet.Range("A1:D10").Font.Bold = True Use With blocks whenever possible. For instance, you might be tempted to write code like this: objWorksheet.Name = "My Worksheet" objWorksheet.Visible = xlSheetHidden objWorksheet.EnableAutoFilter = False objWorksheet.PageSetup.FitToPagesTall = 2 objWorksheet.PageSetup.FitToPagesWide = 1 objWorksheet.PageSetup.Zoom = 70 This is not efficient, because the objWorksheet is resolved each time. Here's how it ought to be done: With objWorksheet .Name = "My Worksheet" .Visible = xlSheetHidden .EnableAutoFilter = False With .PageSetup .FitToPagesTall = 2 .FitToPagesWide = 1 .Zoom = 70 End With End With Those are some main beginner inefficiencies (not sure of your level). Of course, code optimization is a complex subject, so it's difficult to tell which suggestions will work best for your particular situation. Perhaps if you describe some of the functionalities provided by your add-in some more examples specific to your situation can be provided. Good luck. -Ilia On May 20, 8:58 pm, Varne wrote: Hi Withholding screen updating helps. Thank you Bernard and N10. Whiz Could you please send 2 examples of best practice codes and corresponding untrained programmer's entries to Many Thanks Varne M "N10" wrote: Hi Try putting "application.screenupdating = false" at the starts of macros for which you wish to hide the "agility" factor. This command hides changes of sheets,data updating visually and a host of others actions You may turn on screenupdating gain with "application.screenupdating = true" Happy hunting Regards N10 "Varne" wrote in message ... Hi Chip Thanks If you click for action or report in an accounting package it comes to your screen with just one change. However underneath the dbms must be gathering data or consolidating data from many tables. You do not see it. Now VBA does it in front of you running to and from pages, fields and cells. Cannot this be hidden? Varne M "Chip Pearson" wrote: Just what does "agile" mean in the context of VBA? "Varne" wrote in message ... Hi My VBA applications are too agile. I am trying to sell a few VBA applications on accounting but am afraid the said problem will be a negative factor. Cannot VBA made to perform below the surface with an inbuilt macro screening it? Varne M- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unpleasant VBA Agility
AFAIK, yes, With only make an impact on performance if the number of dots is
reduced, so the object does not have to be fully resolved each time. However, I tend to use With to improve readability anyway. NickHK "RB Smissaert" wrote in message ... But is this: objWorksheet.Name = "My Worksheet" objWorksheet.Visible = xlSheetHidden objWorksheet.EnableAutoFilter = False Any slower than this: With objWorksheet .Name = "My Worksheet" .Visible = xlSheetHidden .EnableAutoFilter = False End With I thought it was the number of dots that mattered. Just wondering. RBS "ilia" wrote in message oups.com... Besides the mentioned screen updating, you may do the same with calculations: Application.Calculation = xlCalculationManual You can reset this to: Application.Calculation = xlCalculationAutomatic after your macro runs. Use early binding, and work with objects. This allows you to set With blocks, but also improves performance. One example of this is CreateObject() function. Whenever possible, include a reference to a library instead and do a strict type. Instead of this: dim objWord as Object set objWord = CreateObject("Word.Application") use this: dim objWord as Word.Application set objWord = new Word.Application A similar principle applies to using Selection, ActiveCell, ActiveSheet, etc. If you need to do something with the current selection (assuming on a worksheet), do this: dim rngSelection as Excel.Range set rngSelection = Application.Selection Or, if you need to use active sheet, do this: dim wshSheet as Excel.Worksheet set wshSheet = Application.ActiveSheet As mentioned above, do not "select" stuff to work on it. Macro recorder will generate code like this: ActiveSheet.Range("A1:D10").Select Selection.Font.Bold = True Instead, do this: ActiveSheet.Range("A1:D10").Font.Bold = True Use With blocks whenever possible. For instance, you might be tempted to write code like this: objWorksheet.Name = "My Worksheet" objWorksheet.Visible = xlSheetHidden objWorksheet.EnableAutoFilter = False objWorksheet.PageSetup.FitToPagesTall = 2 objWorksheet.PageSetup.FitToPagesWide = 1 objWorksheet.PageSetup.Zoom = 70 This is not efficient, because the objWorksheet is resolved each time. Here's how it ought to be done: With objWorksheet .Name = "My Worksheet" .Visible = xlSheetHidden .EnableAutoFilter = False With .PageSetup .FitToPagesTall = 2 .FitToPagesWide = 1 .Zoom = 70 End With End With Those are some main beginner inefficiencies (not sure of your level). Of course, code optimization is a complex subject, so it's difficult to tell which suggestions will work best for your particular situation. Perhaps if you describe some of the functionalities provided by your add-in some more examples specific to your situation can be provided. Good luck. -Ilia On May 20, 8:58 pm, Varne wrote: Hi Withholding screen updating helps. Thank you Bernard and N10. Whiz Could you please send 2 examples of best practice codes and corresponding untrained programmer's entries to Many Thanks Varne M "N10" wrote: Hi Try putting "application.screenupdating = false" at the starts of macros for which you wish to hide the "agility" factor. This command hides changes of sheets,data updating visually and a host of others actions You may turn on screenupdating gain with "application.screenupdating = true" Happy hunting Regards N10 "Varne" wrote in message ... Hi Chip Thanks If you click for action or report in an accounting package it comes to your screen with just one change. However underneath the dbms must be gathering data or consolidating data from many tables. You do not see it. Now VBA does it in front of you running to and from pages, fields and cells. Cannot this be hidden? Varne M "Chip Pearson" wrote: Just what does "agile" mean in the context of VBA? "Varne" wrote in message ... Hi My VBA applications are too agile. I am trying to sell a few VBA applications on accounting but am afraid the said problem will be a negative factor. Cannot VBA made to perform below the surface with an inbuilt macro screening it? Varne M- Hide quoted text - - Show quoted text - |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unpleasant VBA Agility
From how I understand it, the fewer dots the better - unless you're
in .NET, in which case it doesn't matter. Page Setup is a bad example, because that accesses printer drivers and those are usually slow anyway. Here's a crude test. Put this code into a new workbook and run it. Public Sub tryWith() Dim d1 As Double, d2 As Double Dim objWorksheet As Excel.Worksheet Dim rngRange As Excel.Range Const iterations As Double = 30000 Set objWorksheet = Application.ActiveSheet Application.ScreenUpdating = False objWorksheet.Range("C1").Value = "Not using with" objWorksheet.Range("D1").Value = "Using with" d1 = Now For Each rngRange In objWorksheet.Range("A1:A" & iterations) rngRange.Value = rngRange.Row() rngRange.Font.Bold = True rngRange.Font.Italic = True Next rngRange d2 = Now objWorksheet.Range("C2").Value = (d2 - d1) * 86400 d1 = Now For Each rngRange In objWorksheet.Range("B1:B" & iterations) With rngRange .Value = rngRange.Row() With .Font .Bold = True .Italic = True End With End With Next rngRange d2 = Now objWorksheet.Range("D2").Value = (d2 - d1) * 86400 Application.ScreenUpdating = True End Sub Theoretically, C2 should be greater than D2 (that's in seconds). Change the constant at the top for more iterations. On May 22, 12:04 am, "NickHK" wrote: AFAIK, yes, With only make an impact on performance if the number of dots is reduced, so the object does not have to be fully resolved each time. However, I tend to use With to improve readability anyway. NickHK "RB Smissaert" wrote in message ... But is this: objWorksheet.Name = "My Worksheet" objWorksheet.Visible = xlSheetHidden objWorksheet.EnableAutoFilter = False Any slower than this: With objWorksheet .Name = "My Worksheet" .Visible = xlSheetHidden .EnableAutoFilter = False End With I thought it was the number of dots that mattered. Just wondering. RBS "ilia" wrote in message roups.com... Besides the mentioned screen updating, you may do the same with calculations: Application.Calculation = xlCalculationManual You can reset this to: Application.Calculation = xlCalculationAutomatic after your macro runs. Use early binding, and work with objects. This allows you to set With blocks, but also improves performance. One example of this is CreateObject() function. Whenever possible, include a reference to a library instead and do a strict type. Instead of this: dim objWord as Object set objWord = CreateObject("Word.Application") use this: dim objWord as Word.Application set objWord = new Word.Application A similar principle applies to using Selection, ActiveCell, ActiveSheet, etc. If you need to do something with the current selection (assuming on a worksheet), do this: dim rngSelection as Excel.Range set rngSelection = Application.Selection Or, if you need to use active sheet, do this: dim wshSheet as Excel.Worksheet set wshSheet = Application.ActiveSheet As mentioned above, do not "select" stuff to work on it. Macro recorder will generate code like this: ActiveSheet.Range("A1:D10").Select Selection.Font.Bold = True Instead, do this: ActiveSheet.Range("A1:D10").Font.Bold = True Use With blocks whenever possible. For instance, you might be tempted to write code like this: objWorksheet.Name = "My Worksheet" objWorksheet.Visible = xlSheetHidden objWorksheet.EnableAutoFilter = False objWorksheet.PageSetup.FitToPagesTall = 2 objWorksheet.PageSetup.FitToPagesWide = 1 objWorksheet.PageSetup.Zoom = 70 This is not efficient, because the objWorksheet is resolved each time. Here's how it ought to be done: With objWorksheet .Name = "My Worksheet" .Visible = xlSheetHidden .EnableAutoFilter = False With .PageSetup .FitToPagesTall = 2 .FitToPagesWide = 1 .Zoom = 70 End With End With Those are some main beginner inefficiencies (not sure of your level). Of course, code optimization is a complex subject, so it's difficult to tell which suggestions will work best for your particular situation. Perhaps if you describe some of the functionalities provided by your add-in some more examples specific to your situation can be provided. Good luck. -Ilia On May 20, 8:58 pm, Varne wrote: Hi Withholding screen updating helps. Thank you Bernard and N10. Whiz Could you please send 2 examples of best practice codes and corresponding untrained programmer's entries to Many Thanks Varne M "N10" wrote: Hi Try putting "application.screenupdating = false" at the starts of macros for which you wish to hide the "agility" factor. This command hides changes of sheets,data updating visually and a host of others actions You may turn on screenupdating gain with "application.screenupdating = true" Happy hunting Regards N10 "Varne" wrote in message ... Hi Chip Thanks If you click for action or report in an accounting package it comes to your screen with just one change. However underneath the dbms must be gathering data or consolidating data from many tables. You do not see it. Now VBA does it in front of you running to and from pages, fields and cells. Cannot this be hidden? Varne M "Chip Pearson" wrote: Just what does "agile" mean in the context of VBA? "Varne" wrote in message ... Hi My VBA applications are tooagile. I am trying to sell a few VBA applications on accounting but am afraid the said problem will be a negative factor. Cannot VBA made to perform below the surface with an inbuilt macro screening it? Varne M- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|