Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Optimize VBA Code for Speed?

  1. #1
    Join Date
    15th October 2004
    Posts
    12

    Optimize VBA Code for Speed?

    I am using the following code (see code example at bottom of this post) to change some cell formatting based on the value of a cell linked to a combo box. I first tried using named ranges but that failed miserably (it made the formatting changes but only to the Print Area and it was S-L-O-W). In any case, the code below works but still runs pretty slowly (3-5 seconds to change the formats for 8 cells) and I am trying to find a way to make it run lickity-split fast. Any help would be much appreciated.
    SNIPPET OF CODE
    VB:
    Private Sub ComboBox1_Change() 
        Dim myCell As Range 
        Dim cell As Range 
         
        Set myCell = Range("$M$2") 
         
        For Each cell In Range("$B$2:$K$3") 
            If myCell.Value = "SiteA" Then 
                cell.Interior.ColorIndex = 25 
                cell.Font.ColorIndex = 2 
                cell.Font.Bold = True 
                 
            ElseIf myCell.Value = "SiteB" Then 
                cell.Interior.ColorIndex = 14 
                cell.Font.ColorIndex = 2 
                cell.Font.Bold = True 
                 
            ElseIf myCell.Value = "SiteC" Then 
                cell.Interior.ColorIndex = 53 
                cell.Font.ColorIndex = 2 
                cell.Font.Bold = True 
            End If 
             
        Next cell 
    End Sub 
    
    
    END SNIPPET
    Thanks,
    Omomyid

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    11th February 2003
    Location
    Near the Land of OZ
    Posts
    1,591
    This may or may not help, but Select Case will typically run faster than If... Then code. Also, With ... End With will provide more speed.. Something like this? And maybe a couple other changes to accommodate this.

    VB:
    Sub Test() 
        Dim myCell As Range 
        Dim cell As Range 
         
        Set myCell = Range("$M$2") 
         
         
        For Each cell In Range("$B$2:$K$3") 
            Select Case myCell.Value 
            Case myCell.Value = "SiteA" 
                With cell 
                    .Interior.ColorIndex = 25 
                    .Font.ColorIndex = 2 
                    .Font.Bold = True 
                End With 
            Case myCell.Value = "SiteB" 
                With cell 
                    .Interior.ColorIndex = 14 
                    .Font.ColorIndex = 2 
                    .Font.Bold = True 
                End With 
                 
            Case myCell.Value = "SiteC" 
                With cell 
                    .Interior.ColorIndex = 53 
                    .Font.ColorIndex = 2 
                    .Font.Bold = True 
                End With 
                 
            Case myCell.Value = "SiteD" 
                With cell 
                    .Interior.ColorIndex = 5 
                    .Font.ColorIndex = 2 
                    .Font.Bold = True 
                End With 
                 
            Case myCell.Value = "SiteE" 
                With cell 
                    .Interior.ColorIndex = 3 
                    .Font.ColorIndex = 2 
                    .Font.Bold = True 
                End With 
            Case myCell.Value = "SiteF" 
                With cell 
                    .Interior.ColorIndex = 37 
                    .Font.ColorIndex = 2 
                    .Font.Bold = True 
                End With 
            End Select 
        Next cell 
    End Sub 
    
    

    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt

    Old, slow, and confused - but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3.28.2008)

  3. #3
    Join Date
    15th October 2004
    Posts
    12
    Ok, thanks for the tip...unfortunately while there are no visible errors, the cell formatting is not changing when a new value is selected from the combo box.
    I am probably missing 'those couple other changes to accomodate this' since I am a VBA neophyte.

    -Omomyid

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    6th September 2004
    Posts
    438
    VB:
     'The lines
    Case myCell.Value = "SiteE" 
         
         'Should be
    Case "SiteE" 
    
    
    TJ
    Oh dear I need a beer
    Online Motorsport Game

  5. #5
    Join Date
    15th October 2004
    Posts
    12
    Tinyjack,

    Definitely works now, but runs just as slowly as the If/Then loops from the first iteration.
    Do you think it would run faster with a named range? If so, can you give me an example of how to code in the named range (which as stated in the original post I failed horribly with)

    Thanks,

    Omomyid

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    6th September 2004
    Posts
    438
    Why use For Each? Why not just:

    VB:
    Range("$B$2:$K$3").Interior.ColorIndex = 25 
    
    
    TJ
    Oh dear I need a beer
    Online Motorsport Game

  7. #7
    Join Date
    15th October 2004
    Posts
    12
    PERFECT!!!

    Thanks so much for your quick response and excellent advice!

    -Omomyid

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    15th October 2004
    Posts
    12

    Final Code for Conditional Formatting on Combo Box Change

    Thought folks might find the final code solution for this question useful.
    It can be used to conditionally format cell ranges based on the change of a value selected in a combo box control. It should only be used if you have 5 or more items in the combo box, since the native conditional formatting in Excel can handle up to 4 cases (3 conditional formats plus one base format).
    Obviously, you can add as many cases as there are items in the drop down.

    VB:
    Private Sub ComboBox1_Change() 
        Dim myCell As Range 
        Dim myRng As Range 
         
        Set myCell = Range("$M$2") 'Set myCell to the address of the combo box's linked cell
        Set myRng = Range("$B$2:$K$3,$B$22:$K$23") 'This is the range to be modified
         
        Select Case myCell.Value 
             
        Case "A" 'This is the case that you want to match
            With myRng 
                .Interior.ColorIndex = 25 'range background color
                .Font.ColorIndex = 2 'font color
                .Font.Bold = True 'font weight
            End With 
             
        Case "B" 
            With myRng 
                .Interior.ColorIndex = 14 
                .Font.ColorIndex = 2 
                .Font.Bold = True 
            End With 
             
        Case "C" 
            With myRng 
                .Interior.ColorIndex = 53 
                .Font.ColorIndex = 2 
                .Font.Bold = True 
            End With 
        End Select 
    End Sub 
    
    
    -Omomyid

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,714
    Hi Omomyid

    Thanks fo posting your final code. When posting code could you please use the Code tags so others can easily copy paste or read your code. E.g

    [ code]
    Your Code
    [ /code]

    Less any space after [

    You may find the links below of use also;

    Efficient VBA Code Part 1

    Efficient VBA Code Part 2

    Efficient VBA Code Part 3

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Optimize / Speed Up Slow Pivot Table Code
    By oediaz in forum EXCEL HELP
    Replies: 2
    Last Post: March 6th, 2008, 23:57
  2. Optimize VBA Code
    By ambarrovecchio in forum EXCEL HELP
    Replies: 23
    Last Post: July 3rd, 2007, 18:50
  3. Speed-Up/Optimize Recorded Macro Code
    By Malcolm Fenner in forum EXCEL HELP
    Replies: 3
    Last Post: August 30th, 2006, 19:38
  4. Optimize my code
    By jxuereb in forum EXCEL HELP
    Replies: 5
    Last Post: December 19th, 2005, 08:58

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno