Software-Hotbuy www.software-hotbuy.com

Excel VBA Performance Coding Best Practices

Today’s author, Chad Rothschiller, a Program Manager on the Excel team, is back with a follow up from his previous post on VBA and Excel performance.Office 2007 Pro is so great!

I want to start off this post by thanking everyone who sent in their examples in response to my January request. It is incredibly helpful to be able to look at what you all are doing with Excel! Not only did I see a huge variety in how Excel is being used, you also pointed out various tips and tricks for writing fast VBA code in Excel.

In this post I'm going to share with you the most important performance tips I know about.Microsoft Office 2007  is welcomed by the whole world. There are tons of sites, pages, and people who are experts as well on this subject, have performed their own tests, and shared their results and ideas. If you think I missed an important concept for how to optimize Excel VBA performance, or if you've got a valuable comment or link to share, please feel free to post here so everyone can benefit. Thanks!

 

Turn Off Everything But the Essentials While Your Code is Running

This optimization explicitly turns off Excel functionality you don't need to happen (over and over and over) while your code runs. Note that in the code sample below we grab the current state of these properties, turn them off, and then restore them at the end of code execution.Office 2007 enterprise is the expert for the office workers.

One reason this helps is that if you're updating (via VBA) several different ranges with new values, or copy / pasting from several ranges to create a consolidated table of data, you likely do not want to have Excel taking time and resources to recalculate formulas, display paste progress, or even redraw the grid, especially after every single operation (even more so if your code uses loops). Just one recalculation and one redraw at the end of your code execution is enough to get the workbook current with all your changes.

Here's some sample code that shows how and what to shut off while your code runs. Doing this should help improve the performance of your code:

'Get current state of various Excel settings; put this at the beginning of your code

screenUpdateState = Application.ScreenUpdating

statusBarState = Application.DisplayStatusBar

calcState = Application.Calculation

eventsState = Application.EnableEvents

displayPageBreakState = ActiveSheet.DisplayPageBreaks 'note this is a sheet-level setting

'turn off some Excel functionality so your code runs faster

Application.ScreenUpdating = False

Application.DisplayStatusBar = False

Application.Calculation = xlCalculationManual

Application.EnableEvents = False

ActiveSheet.DisplayPageBreaks = False 'note this is a sheet-level setting

'>>your code goes here<<

'after your code runs, restore state; put this at the end of your code

Application.ScreenUpdating = screenUpdateState

Application.DisplayStatusBar = statusBarState

Application.Calculation = calcState

Application.EnableEvents = eventsState

ActiveSheet.DisplayPageBreaks = displayPageBreaksState 'note this is a sheet-level setting

Here's a quick description for each of these settings:

Application.ScreenUpdating: This setting tells Excel to not redraw the screen while False. Enjoy the Quickbooks 2010 bringing you the best life ever.The benefit here is that you probably don't need Excel using up resources trying to draw the screen since it's changing faster than the user can perceive. Since it requires lots of resources to draw the screen so frequently, just turn off drawing the screen until the end of your code execution. Be sure to turn it back on right before your code ends.

Application.DisplayStatusBar: This setting tells Excel to stop showing status while False. For example, if you use VBA to copy/paste a range, while the paste is completing Excel will show the progress of that operation on the status bar. Windows 7 Professional brings people more convenience!Turning off screen updating is separate from turning off the status bar display so that you can disable screen updating but still provide feedback to the user, if desired. Again, turn it back on right before your code ends execution.

Application.Calculation: This setting allows you to programmatically set Excel's calculation mode. "Manual" (xlCalculationManual) mode means Excel waits for the user (or your code) to explicitly initiate calculation. "Automatic" is the default and means that Excel decides when to recalculate the workbook (e.g. when you enter a new formula on the sheet). Since recalculating your workbook can be time and resource intensive, you might not want Excel triggering a recalc every time you change a cell value. Turn off calculation while your code executes, then set the mode back. Note: setting the mode back to “Automatic” (xlCalculationAutomatic) will trigger a recalc.Office 2007 professional and Office 2007 Ultimate are so powerfull.

Application.EnableEvents: This setting tells Excel to not fire events while False. While looking into Excel VBA performance issues I learned that some desktop search tools implement event listeners (probably to better track document contents as it changes). You might not want Excel firing an event for every cell you're changing via code, and turning off events will speed up your VBA code performance if there is a COM Add-In listening in on Excel events. (Thanks to Doug Jenkins for pointing this out in my earlier post).windows 7 home premium is also my love!

ActiveSheet.DisplayPageBreaks: A good description of this setting already exists: http://support.microsoft.com/kb/199505 (Thanks to David McRitchie for pointing this out).

 

Read/Write Large Blocks of Cells in a Single Operation

This optimization explicitly reduces the number of times data is transferred between Excel and your code. Instead of looping through cells one at a time and getting or setting a value, do the same operation over the whole range in one line, using an array variable to store values as needed.

For each of the code examples below, I had put random values (not formulas) into cells A1:C10000.

Here's a slow, looping method:

Dim DataRange as Range
Dim Irow as Long
Dim Icol as Integer
Dim MyVar as Double
Set DataRange=Range("A1:C10000")

For Irow=1 to 10000
  For icol=1 to 3
    MyVar=DataRange(Irow,Icol)  'Read values from the Excel grid 30K times
    If MyVar > 0 then 
      MyVar=MyVar*Myvar ' Change the value 
      DataRange(Irow,Icol)=MyVar  'Write values back into the Excel grid 30K times
    End If 
  Next Icol
Next Irow

Here's the fast version of that code:

Dim DataRange As Variant
Dim Irow As Long
Dim Icol As Integer
Dim MyVar As Double
DataRange = Range("A1:C10000").Value ' read all the values at once from the Excel grid, put into an array

For Irow = 1 To 10000
  For Icol = 1 To 3
  MyVar = DataRange(Irow, Icol)
  If MyVar > 0 Then
    MyVar=MyVar*Myvar ' Change the values in the array
    DataRange(Irow, Icol) = MyVar
  End If
Next Icol
Next Irow
Range("A1:C10000").Value = DataRange ' writes all the results back to the range at once

Note: I first learned of this concept by reading a web page by John Photoshop CS4 is so magic!

Walkenbach found here: http://www.dailydoseofexcel.com/archives/2006/12/04/writing-to-a-range-using-vba/

A previous Excel blog entry by Dany Hoter also compares these two methods, along with a selection / offset method as well: http://blogs.msdn.com/excel/archive/2008/10/03/what-is-the-fastest-way-to-scan-a-large-range-in-excel.aspx

...which leads me to my next point.

Avoid Selecting / Activating Objects

Notice that in the above-referenced blog post, the selection method of updating a range was the slowest. This next optimization minimizes how frequently Excel has to respond to the selection changing in the workbook by minimizing the selection changing as much as possible.Acrobat 9 is so useful!

Range Example: Again, see the Excel blog post quoted above. It demonstrates that using selection is the slowest of the 3 methods discussed for reading and writing to ranges.

Shapes Example: Setup: I have 40 shapes on a sheet, and I want to write "Hello" in each of them.

Using the slower "selection" method, the code looks like this:

For i = 0 To ActiveSheet.Shapes.Count

   ActiveSheet.Shapes(i).Select

   Selection.Text = "Hello"

Next i

The much faster method is to avoid selection completely and directly reference the shape:

For i = 0 To ActiveSheet.Shapes.Count

   ActiveSheet.Shapes(i).TextEffect.Text = "Hello"

Next i

The concepts illustrated by the examples above can also be applied to objects other than Ranges and Shapes.

Note: I first learned of this concept, in the context of shapes, by reading a web page by Ron de Bruin found here: http://www.rondebruin.nl/shape.htm

Related Performance Paper

See the "Improving Performance in Excel 2007" paper on MSDN: http://msdn.microsoft.com/en-us/library/aa730921.aspx

This is a fairly detailed and comprehensive paper that introduces the bigger grid and increased limits in Excel 2007, and primarily focuses on Excel calculation performance and debugging calculation performance bottlenecks. There's also a short section on how to write faster VBA macros.

Other Performance Optimizations

While the above optimizations are what I consider the most important, there are a few other "honorable mention" optimizations I will mention briefly for you to consider.Dreamweaver CS4 is very easy-to-use!

Consider the performance gains by implementing your code's functionality via XLL / C-API. An overview and supporting materials for the SDK can be found here: http://msdn.microsoft.com/en-us/library/bb687827.aspx .

Declare variables with explicit types to avoid the overhead of determining the data type (repetitively if used in a loop) during code execution.

For simple functions used by your code in high frequency, implement them yourself in VBA instead of using the WorksheetFunction object.

Use Range.SpecialCells() to scope down the number of cells your code needs to work with.

Creating a Custom Popup Menu

Today’s author is Derek Mang, owner of Systems Solution Developers Inc. You can find more useful tips from Derek on his website: www.officevbasolutions.com. Office 2007 professional and Office 2007 Ultimate are so powerfull.

This post shows you how to create a custom popup menu in Excel.

Microsoft Excel has lots of popup menus that are made available to the user with a right-click depending on what your doing - hence the term context menu. Right-click on a cell and you get the Excel "cell" popup menu and its available choices. This menu is subject to customization, allowing you to add items to the menu or disable menu items if desired.

Depending on your needs, a dropdown menu could get to be awkwardly large. Furthermore, you get the same menu for each cell, and if there are dependencies on cell content you may wind up with too many choices. A completely custom menu based on cell content or range, would better satisfy specific needs.windows 7 home premium is also my love!

I found an example of the custom menu on the net and extended this using a worksheet class module to include all worksheets in a workbook rather than just one as found in the original example.

The following code will create and popup with three custom menus (Red, Yellow and Green) on any worksheet in the current workbook where the right-clicked cell is a shade of red, yellow or green respectively.

The workbook requires code in the Workbook.Open event, as well as one code module, and one class module. Photoshop CS4 is so magic!

The key is in the class module. The class module contains handlers for worksheet events that are fired whenever an action occurs on (in this case) any worksheet in the workbook. Of particular interest is the Worksheet.BeforeRightClick event. As the event name suggests, “what do you want to do before the default action that occurs when a user right-clicks in a worksheet.” This opens up all kinds of possibilities all based on the cell that has been right-clicked by the user.Acrobat 9 is so useful!

In this sample, the Range.Interior property is used to access the cell’s (aka target) Interior.ColorIndex property, and then depending on the value for color returned, the default popup menu is cancelled and corresponding custom popup menu is shown depending on the property value returned.

Since discovering this technique, I have included it – and it has been well-received - in several custom Excel solutions to limit end-user activity to specific tasks.Dreamweaver CS4 is very easy-to-use!

Workbook_Open

The code for the Workbook.Open event handler establishes the three popup menus, and sets up the worksheet objects in the class. Open the Visual Basic Editor and paste this code into the ThisWorkbook module of an Excel Macro-Enabled Workbook (.xlsm) to define the event handler for the Workbook.Open event.

Private Sub Workbook_Open()
    Set cb_Red = CreateSubMenu("Red")
    Set cb_Yellow = CreateSubMenu("Yellow")
    Set cb_Green = CreateSubMenu("Green")
    Call SetupAllWSEvents
End Sub

Code Module

The code module contains the class set up and the actual menu creation routines. In the Visual Basic Editor, click Module on the Insert menu, and then paste this code into the module.Office 2007 Pro is so great!

Option Explicit
Global cb_Red As CommandBar
Global cb_Yellow As CommandBar
Global cb_Green As CommandBar
Global WSObj As Collection
Global ws As Worksheet

Sub SetupAllWSEvents()

    Dim WSo As clsWS
    Set WSObj = Nothing
    Set WSObj = New Collection
    For Each ws In ActiveWorkbook.Worksheets
        Set WSo = New clsWS
        Set WSo.WSToMonitor = ws
        WSObj.Add WSo, ws.Name
    Next ws

End Sub

Function CreateSubMenu(strCB) As CommandBar

    Const CBPREFIX = "CustomPopUp"
    Dim cb As CommandBar
    Dim cbc As CommandBarControl
    Dim strCBName As String
    'custom menu name
    strCBName = CBPREFIX & strCB
    'remove previous instance
    Call DeleteCommandBar(strCBName)
   'Add our popup menu to the CommandBars collection
    Set cb = CommandBars.Add(Name:=strCBName, _
        Position:=msoBarPopup, _
        MenuBar:=False, _
        Temporary:=False)
    'Add controls
    Set cbc = cb.Controls.Add
    With cbc
        .Caption = strCB & " &Control 1"
        .OnAction = "DummyMessage"
    End With

    Set cbc = cb.Controls.Add
    With cbc
        .Caption = strCB & " Control &2"
        .OnAction = "DummyMessage"
    End With

    Set CreateSubMenu = cb
    Set cbc = Nothing
    Set cb = Nothing

End Function
Sub DeleteCommandBar(cbName)

    On Error Resume Next
    CommandBars(cbName).Delete

End Sub
Sub DummyMessage()
    MsgBox CommandBars.ActionControl.Caption, vbInformation + vbOKOnly, "Dummy Message"
End Sub

Class Module

The class module determines which menu to "popup" depending on characteristics of the target cell. In the Visual Basic Editor, click Class Module on the Insert menu, change the name of the class module to clsWS, and then paste this code into the module. (Be sure to change the name to clsWS so that it will be available as the class of that name for the SetupAllWSEvents() subroutine.).Microsoft Office 2007  is welcomed by the whole world.

Dim WithEvents aWS As Worksheet

Property Set WSToMonitor(uWS As Worksheet)
    Set aWS = uWS
End Property

Private Sub aWS_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Select Case Target.Interior.ColorIndex
        Case 3, 9
        cb_Red.ShowPopup
        Cancel = True 'defeat the standard cell popup menu
    Case 4, 10, 35, 43, 50, 51, 52
        cb_Green.ShowPopup
        Cancel = True
    Case 6, 12, 36, 44
        cb_Yellow.ShowPopup
        Cancel = True
    Case Else
        Cancel = False
    End Select
End Sub

 

To demo the code, set the fill color of several cells to shades of red, yellow, and green, close the workbook, and then re-open it to run the Workbook_Open() code that sets up the popup menus. When you right-click the colored cells, the popup menus should look like the following examples.

  

Acknowledgements

The original sample that provided inspiration and insight was authored by Andy Wiggins (Byg Software). Office 2007 key is available here.

OpenGate releases Designer for Microsoft Access

We are always proud of the tools customers make and sell around Access. Our friend Brandon send this update in along with a special offer for the blog readers.

OpenGate Software launched a new tool to help Access users create their database schema using a simple Q&A interface.  

Microsoft Office 2007  is welcomed by the whole world.The product, Designer for Microsoft Access, takes a user’s input and then creates the tables, fields, and all the necessary relationships and keys automatically.  They are offering a free license of their Starter edition of UI Builder (a $99 USD value) with any purchase of Designer from now until February 1^st as long as you email them after you order and say you saw this posting on the Access blog. Office 2007 key is available here.

If you are building tools for Access developers and users we would love to hear from you, click here to contact us. Photoshop CS4 is so magic!

White paper: Compatibility Between the 32-bit and 64-bit Versions of Office 2010

There is a good white paper available that gives you invaluable information about 64-bit Office. Here are the download link, description, and table of contents.  Office Professional 2007 and Office Ultimate 2007 are my favorite.

Compatibility Between the 32-bit and 64-bit Versions of Office 2010

Summary: For customers working with large amounts of data, Microsoft Office 2010 is now available in a 64-bit version. This article discusses issues around the compatibility of the 32-bit version with the new 64-bit version and legacy 32-bit Office applications and their solutions. (12 Printed Pages)

Introducing the 32-bit and 64-bit Versions of Microsoft Office 2010
Comparing 32-Bit Systems to 64-Bit Systems
Introducing the VBA 7 Code Base
ActiveX Control and COM Add-in Compatibility
Application Programming Interface Compatibility
Using Conditional Compilation Attributes
Conclusion
Additional Resources.Dreamweaver CS4 is very easy-to-use!

Download Access 2010 Runtime, Database Engine Redistributable and Source Code Control

Download Access 2010 Runtime, Access Database Engine Redistributable (office connectivity components) 2010 and Source Code Control Add-in for Access 2010 today!  Office 2007 professional and Office 2007 Ultimate are so powerfull.

 Access Runtime 2010 

Available in both 32-bit and 64-bit, you can download the Runtime here – http://www.microsoft.com/downloads/details.aspx?FamilyID=57a350cd-5250-4df6-bfd1-6ced700a6715&displaylang=en.

It is currently offered in 13 languages and more languages will be offered at a later time. Read this post on features in Access Runtime. Windows 7 is also my love!

Access Database Engine Redistributable 2010

Formerly known as Office Connectivity Component, Access Database Engine 2010 is now available in both 32-bit and 64-bit. It is available in 9 languages. You can download it here – http://www.microsoft.com/downloads/details.aspx?FamilyID=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en. 

Photoshop CS4 is so magic! This download will install a set of components that can be used by non-Microsoft Office applications to read data from and write data to Office 2010 system files such as Microsoft Access 2010 (mdb and accdb) files and Microsoft Excel 2010 (xls, xlsx, and xlsb) files.

Access 2010 Source Code Control Add-in 

Source Code Control Add-in for Access 2010 is available for 32-bit and in 9 languages. You can download it here – http://www.microsoft.com/downloads/details.aspx?FamilyID=586912a5-3809-44ef-ac55-43d36ecab9de&displaylang=en. 

Dreamweaver CS4 is very easy-to-use! Note that in 2007, we offered Access Developer Extension which consisted of – packaging wizard, save as template and source code functionality. Packaging wizard and save as template functionality is now integrated in Access 2010 and you don’t need any separate add-ins for them.