none
Which methood would be faster? RRS feed

  • Question

  • Hi All,

    I have a worksheet event that creates a validation list in a target cell when its clicked.  Because of the complexity I was unable to produce it using a formula in the validation list, the very basic "IF" action below is not what actually happens and several ranges are referenced.

    Because this is a worksheet event, there is obviously a pause when a target cell is clicked.  I would like to reduce that paus as much as possible.

    To that end which method would be faster out of the two below and would the difference be noticeable?

    Dim Rng1 as Range
    Dim Rng2 as Range
    Dim Col1 as Collection

    Set Rng1 = Worksheets(1).Range("NamedRange1") 'Contains a list of Boolean values
    Set Rng2 = Worksheets(2).Range("NamedRange2")
    Set Col1 = New Collection

    For i = 1 To Rng1.Cells.count
    If Rng1 (i) Then Col1.Add Rng2(i)
    next i

    OR

    Dim Arr1() as Variant
    Dim Arr2() as Variant
    Dim Col1 as Collection

    Arr1() = Worksheets(1).Range("NamedRange1") 'Contains a list of Boolean values
    Arr2() = Worksheets(2).Range("NamedRange2")
    Set Col1 = New Collection

    For i = 1 To UBound(Arr1)
    If Rng1(i) Then Col1.Add Arr2(i)
    next i

    For i = 1 To UBound(ArrRngConATS)
                If ArrRngConATS(i) Then

    next i

    Finally, in the actual workbook, several of the ranges are never changed.  I assume that if these were read into the VB module at startup and retained there that this would have a marked improvement on speed in comparison to repeatedly referencing the workbook.  How would I load the values into VBA at start, would I need to put some code in to the Workbook_open module and if so could you give me an idea on how that script would look please?


    Friday, February 6, 2015 4:38 PM

Answers

  • >> Because this is a worksheet event, there is obviously a pause when a target cell is clicked.  I would like to reduce that paus as much as possible.

    I did not see the obvious performance issue in your code. And according to my experience, the performance optimization is usually depended on the specific business scenarios.  Usually, I will suggest you using formula rather than VBA to get the better performance.

    >> How would I load the values into VBA at start, would I need to put some code in to the Workbook_open module and if so could you give me an idea on how that script would look please?

    I think workbook_open would be an option.

    # load array from range (VBA)

    Public dataCache As Collection
    
    Private Sub Workbook_Open()
        'load data when the workbook was opened
        vArrary = Range("source_rng")
    
        For i = 1 To UBound(vArrary)
            dataCache.Add vArrary(i, 1)
        Next i
    End Sub
    
    Sub UseTheDataCache()
        MsgBox ThisWorkbook.dataCache.Count
    End Sub
    

    Regards,

    Jeffrey


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Proposed as answer by L.HlModerator Sunday, February 15, 2015 1:37 PM
    • Marked as answer by L.HlModerator Tuesday, February 17, 2015 7:25 AM
    Thursday, February 12, 2015 3:23 AM
    Moderator

All replies

  • Hi,

    >> Because this is a worksheet event, there is obviously a pause when a target cell is clicked

    If you want a better performance for users, the best way is using Excel formula or build-in functions.

    For your complex business requirement, you can't use formula. But you can set Application.ScreenUpdating to false to give a better performance to users.

    Best Regards

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, February 12, 2015 2:36 AM
    Moderator
  • >> Because this is a worksheet event, there is obviously a pause when a target cell is clicked.  I would like to reduce that paus as much as possible.

    I did not see the obvious performance issue in your code. And according to my experience, the performance optimization is usually depended on the specific business scenarios.  Usually, I will suggest you using formula rather than VBA to get the better performance.

    >> How would I load the values into VBA at start, would I need to put some code in to the Workbook_open module and if so could you give me an idea on how that script would look please?

    I think workbook_open would be an option.

    # load array from range (VBA)

    Public dataCache As Collection
    
    Private Sub Workbook_Open()
        'load data when the workbook was opened
        vArrary = Range("source_rng")
    
        For i = 1 To UBound(vArrary)
            dataCache.Add vArrary(i, 1)
        Next i
    End Sub
    
    Sub UseTheDataCache()
        MsgBox ThisWorkbook.dataCache.Count
    End Sub
    

    Regards,

    Jeffrey


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Proposed as answer by L.HlModerator Sunday, February 15, 2015 1:37 PM
    • Marked as answer by L.HlModerator Tuesday, February 17, 2015 7:25 AM
    Thursday, February 12, 2015 3:23 AM
    Moderator
  • Thank you Jeffery,

    Its not that its impossible to do, but there are a few to many layers in branching lists for me to work out a suitable lookup formula.  Building the logic in script seemed a lot easier after scratching my head and tying to use worksheet functions for a couple hours without success.

    I'll crack it one day, but until then this will have to do.

    Tuesday, February 17, 2015 11:43 AM