CODEWITHSUNDEEP
performanceexcelvlookupvba
JengaC
JengaC

Reputation: 1

Alternative to very slow vba vlookup to closed network files

The basis of this project is to provide a data entry portal located on a Shrepoint site. Managers will input their expected employee demand for each job function and the program will collate this data into easily readable graphs and metrics.

The data entry worksheets are each fed to a 'Total Demand' tab in each projects' workbook, but this is not my problem. My problem is trying to lift the data from these 'Total Demand' sheets (one for each project workbook) into a master workbook with a similar format.

These sheets are designed to be as automated as possible and are aimed at users not particularly familliar with anything more complicated than a SUM() function. For this reason I'd like to keep the projects sheets closed whilst the data is read from them.

The code in the master workbook I have right now first runs a Sub-routine to format the report with the correct dates and projects, then it loops through each row until it finds a row that requires data. From here it uses the GetValue function (found here: http://spreadsheetpage.com/index.php/tip/a_vba_function_to_get_a_value_from_a_closed_file/ ) to lift data, such as the first month with data, from the individual reports.

It then loops through the columns writing a formula into each cell to look up the values found in the reports. I have included a stripped down version of this code below.

job = Cells(rowindexA, 1).Value
targetSheetArray = "'" & path & "[" & file & "]" & ws & "'!" & Range(Cells(1, 2), Cells(100, 100)).Address(, , xlR1C1)

For ColIndex = 10 To numMonths + 9

    targetSheetColumn = ColIndex + monthsDif

    'This formula works but is incredibly slow (multiple seconds PER ENTRY)
    Cells(rowindexA + rowindexB, ColIndex).Formula = "=VLookup(" & Chr(34) & job & Chr(34) & "," & targetSheetArray & "," & targetSheetColumn & ",FALSE)"

    'This formula was from a similar idea than the one above, but Application.WorksheetFunction doesn't work on closed sheets
    'Cells(rowindexA + rowindexB, ColIndex).Value = Application.WorksheetFunction.VLookup(job, targetSheetArray, targetSheetColumn, False)                        

Next

Now for my question. Because the VLookup is so incredibly slow this sheet will take hours to run when every project has been included, is there a quicker way?

I'd like to find a way that prohibits the macro from calculating the lookup value until the very end, at which point it can read all of the data at once. I have tried

Application.Calculation = xlCalculationManual

but this doesn't seem to affect the VLookup procedure so I'm not sure it's possible.

I have also toyed with the idea of having the macro open the project workbooks, copy the data, then close them again but this seems too cumbersome and unreliable, though I do not doubt it would be faster.

Does anybody have any suggestions?

Upvotes: 0

Views: 600

Answers (1)

Maciej Lipinski
Maciej Lipinski

Reputation: 185

Yes, there is a quicker way than using VLookup.

Use a nested loop instead (looping over rows in ws). If possible, enclose the nested loop in an if statement (not sure from the way you formulated your question if it's applicable to your problem). It's still O(n^2), but loops are faster than VLookups.

There might be an even better way, though. Depending on the structure of the data you're comparing (the worksheet you run the macro on and the worksheet that is the source for VLookup data) you might be able to sort the worksheets first (based on a common key) and then run the nested loop with continue-like statements using GoTo. Example:

Dim ws As Worksheet, compared As Worksheet
Dim i As Integer, j As Integer
j = 1

For i = 1 to wsLastrow
    While j <= comparedLastrow
        j = j + 1
        If ws.cells(i, relevantColumn) = compared(j, someColumn) Then
            ws.cells(i, targetColumn) = someValue 'from row j in compared worksheet
            GoTo Continue
        End If
    Wend
Continue:
Next

Upvotes: 0

Related Questions