Examples of Optimization in VB/VBA/VB.net?

Question

Please post examples of optimization done in VB/VBA/VB.net? Optimization can be in the context of performance or space/maintainability.

Edit: Please specify somewhere in your post which environment you know your technique works in. Thanks.

Answer 1

Here's some code I wrote in Excel/VBA to replace a page and half of buggy, ugly initialization statements. It was based on the fact the the thirty comboboxes on the page had similiar names, with similiar data, and I could exploit that to fill them.

' For Buy & Sell Sheets, Fill all ComboBoxes and Clear contents  
    For Each initsheet In Worksheets  
        If (Left$(initsheet.Name, 3) = "Buy" Or Left$(initsheet.Name, 4) = "Sell") Then  
            initsheet.Activate  
            For Each cCont In ActiveSheet.OLEObjects  
            ' Cycle through all controls, filling all ComboBoxes  
                If TypeOf cCont.Object Is MSForms.ComboBox Then  
                    With cCont  
                        Select Case Left$(.Name, 5)  
                            Case "cmbMW"  
                                .ListFillRange = parSheet.Name & "!" & parSheet.Range("transUnit", Sheet2.Range("transUnit").End(xlDown)).Address  
                                .Object.ListIndex = 0  
                            Case "cmbTy"  
                                .ListFillRange = parSheet.Name & "!" & parSheet.Range("transType", Sheet2.Range("transType").End(xlDown)).Address  
                                .Object.ListIndex = 0  
                            Case "cmbCo"  
                                .ListFillRange = parSheet.Name & "!" & parSheet.Range("transCo", Sheet2.Range("transCo").End(xlDown)).Address  
                                .Object.ListIndex = 0   
                            Case "cmbSe"  
                                .Object.List() = ServerArray  
                        End Select  
                    End With  
                End If  
            Next cCont  
            Range("B6:K30").ClearContents  
        End If  
    Next initsheet

Answer 2

Avoid variable declaration inside a loop like this... (Well, read the whole post for a clearer meaning)

For each ctl as Control in Form1.Controls
  Dim string1 as String = ctl.ID
  Dim string2 as String = ctl.ToolTip
  Dim string3 as String = ctl.Text
  Dim string4 as String = ctl.BackColor.ToString()
  'do things with the strings
Next

Use this instead. Its more typing, but will save you resources for repeated variable instantiation.

Dim string1 as String 
Dim string2 as String 
Dim string3 as String 
Dim string4 as String 

For each ctl as Control in Form1.Controls
  string1 = ctl.ID
  string2 = ctl.ToolTip
  string3 = ctl.Text
  string4 = ctl.BackColor.ToString()
  'do things with the strings
Next

Per my comment to Mark...

For _x as Integer = 0 To 5

  Dim _y as Integer

  If _x % 2 = 0 Then
    _y = _x
  End If

  Console.WriteLine(_y)
Next

Given this example, most would assume the output would be 0, 0, 2, 0, 4, 0; when in fact its going to be 0, 0, 2, 2, 4, 4. If it would have been written like this, then it would be grokked rightly.

Dim _y as Integer

For _x as Integer = 0 To 5

  _y = 0

  If _x % 2 = 0 Then
    _y = _x
  End If

  Console.WriteLine(_y)
Next

Answer 3

When you really need to be careful not to waste resources in VB6/VBA:

Private Sub DoStuff()
    Dim objMyFoo As New FooClass

    ' use objMyFoo

    ' done with objMyFoo
    Set objMyFoo = Nothing

    ' do some more stuff
End Sub

Setting objMyFoo to Nothing will fire the FooClass' Class_Terminate method to do any custom freeing up or closing of resources then de-allocates any of objMyFoo's resources. GC in VB.NET behaves a little differently, though...

Answer 4

Here are some techniques for optimizing VBA code (Excel VBA specifically). This is based partially on the tendencies of beginning VBA programmers to use the "Record Macro" function to build portions of their code:

1. Application.ScreenUpdating = False
   Turn off screen updating when you start your procedure, then turn it back on at the end. Makes an enourmous difference!!

2. Using the macro recorder in Excel gives you statements like:

Range("B1").Select
ActiveCell.Formula = "Hello there!"

Optimize that to just

Range("B1").Formula = "Hello there!"

Massive speed improvement!

Answer 5

LET`S GO DOWN TO BASICS.

Choosing right data types can improve .NET applications performance up to 30% (source: MSDN).

Here are simple yet powerful guidelines to follow:

Avoid Object type where possible

Although variable defined as Object can hold any type of data it comes with performance overhead. Compiler misses the opportunity to do type checking and member lookup at compile time (optimization) and these actions are performed at run time (performance overhead).

Be sure to define instances with concrete types where possible:

Dim data As AnyDataType  ' right (strongly typed)'
Dim data As Object       ' wrong (weakly typed)'
Dim data                 ' wrong (weakly typed; defaults to the Object)'

(!) using variable without declaration always defaults to variable being implicitly defined as Object.

Use Value types instead of Reference types where data amounts are small

Value types are stored on stack and only need stack allocation (performance efficient when kept in small size). Reference types are stored on heap and need to do heap allocation, object access, and garbage collection (creates performance overhead).

Dim config As ConfigStruct  ' right (when config holds small amount of data)'
Dim config As ConfigClass   ' wrong (when config holds small amount of data)'

(!) always consider value type instead of reference type where data amounts are small.

Use native data widths where possible

Most efficient data types are those that use native data width of run-time platform. On current platforms, the data width is 32 bits, for both the computer and the operating system. To get best performance use Integer and Double. The next best are Short, Long, Single, Decimal, etc.

Hope this helps you to get edge on performance!