CODEWITHSUNDEEP
performancevbaexcel
SeanP
SeanP

Reputation: 11

VBA Calculation performance

I know its something very simple but I just have no idea where I am going wrong – please help

Setting the scene:
I currently have 3 worksheets in a workbook

Sheet1 – consisting of raw data that will moved about depending on the fixture date for the race

Sheet2 - consisting of raw data that is copied over from sheet 1

View – a view of the data from sheet2

The process is- a user will manually drag and drop fixtures in sheet1 and then clip a macro enabled button that will take a copy of the fixtures and add additional columns with data in sheet2

Then a final button will be clicked to create a view of this data in the ‘view’ sheet. All the view sheet is doing is calling on a defined module called VnthLookup to populate each cell with a relative value.

Issue
Currently the VBA and formula for each sheet works fine, and when creating the view if I manually prompt calculation by clicking on the cell and clicking return button on keyboard it updates and works fine. However, when I try to recalculate whole view sheet it times out and takes forever to complete calc (up to an hour + for just 30 rows, 8 column headers)

Coding for the vnthlookup module is

Public Function VlookupNth(MyVal As Variant, MyRange As Range, Optional ColRef As Long, Optional Nth As Long = 1)
    Dim Count, i As Long
    Dim MySheet As Worksheet

    Count = 0
    Set MySheet = Sheets(MyRange.Parent.Name)

    If ColRef = 0 Then ColRef = MyRange.Columns.Count

    For i = MyRange.Row To MyRange.Row + MyRange.Rows.Count - 1
        If MySheet.Cells(i, MyRange.Column).Value = MyVal Then
            Count = Count + 1
            If Count = Nth Then
                VlookupNth = MySheet.Cells(i, MyRange.Column + ColRef - 1).Value
                Exit Function
            End If
        End If
    Next i

    VlookupNth = ""
End Function

And my view sheet has the following formulae in each and every cell from column B to column J - depending on value I am requesting back from sheet2 the ‘X’ will be different.

=VlookupNth(1,Sheet2!C:AAA,X,2)

Any help in understanding why this is taking so long would be appreciated.

p.s happy to send over workbook if explanation needs clarifying, please do leave me a forwarding address.

Upvotes: 1

Views: 368

Answers (2)

GSerg
GSerg

Reputation: 78175

You have two problems:

  1. A smaller problem is that you're using sheet names to refer to your ranges. Instead use the MyRange variable you already have.
  2. A bigger problem is that you pass the whole column to the function, and the function will blindly examine the whole column, till the last millionth row (in case it doesn't find the value you want early enough). You need to restrict the function to the actual data present on the sheet.

Rewrite as follows:

Public Function VlookupNth(ByVal MyVal As Variant, ByVal MyRange As Range, Optional ByVal ColRef As Long = 0, Optional ByVal Nth As Long = 1) As Variant
    Dim Count As Long, i As Long

    Count = 0
    If ColRef = 0 Then ColRef = MyRange.Columns.Count

    'Do not consider uninitialized rows
    Set MyRange = Application.Intersect(MyRange, MyRange.Parent.UsedRange)

    If Not MyRange Is Nothing Then
        For i = 1 To MyRange.Rows.Count
            If MyRange.Cells(i, 1).Value = MyVal Then
                Count = Count + 1
                If Count = Nth Then
                    VlookupNth = MyRange.Cells(i, ColRef).Value
                    Exit Function
                End If
            End If
        Next i
    End If

    VlookupNth = ""
End Function

Upvotes: 4

iDevlop
iDevlop

Reputation: 25262

Perhaps not the response, but a few hints & suggestions to start. 

Dim Count, i As Long  'is defining Count as a variant
Dim Count as long, i As Long    'is the proper syntax

Then
Set MySheet = Sheets(MyRange.Parent.Name) seems meanlingless. Since MySheet is a worksheet and you use Set, it should be Set MySheet = Sheets(MyRange.Parent).
I suggest you

  • try those small fixes,
  • make sure sure that Option Explicit is set for the module,
  • compile, test and come back with news...

Upvotes: 1

Related Questions