VBA Arrays - Faster alternative than Vlookup?

**danmcdon** · 08-21-2012, 08:07 PM

Hi! I have several multi-dimensional arrays from which I am using different lookup values to pull data. I'm currently using WorksheetFunction.Vlookup which I think is going very slow. I have many iterations and several different arrays so the total impact in run time is pretty significant. What is the best way to lookup values in an array? Or any other strategies to speed things up? THANKS!!

------------------

'example

Dim ArrData

ArrData = range("data")

Value = Application.WorksheetFunction.Vlookup(LookupValue, ArrData, 5, 0)

**JBeaucaire** · 08-21-2012, 08:12 PM

Your example above puts the array data into computer memory and the VLOOKUP is applied to that, I can't imagine anything being faster than that.

**jindon** · 08-21-2012, 08:35 PM

Worksheet function calculates faster in the range.
If you explain more about what you are really trying to do, there may be a faster way.
e.g

Sub test()
    Dim a, x, s As Double, msg As String
    With Range("a1:a65535").Resize(, 2)
        .Formula = "=row()&char(column()+64)"
        s = Timer
        x = WorksheetFunction.VLookup("50000A", .Cells, 2, 0)
        msg = "Range:" & vbTab & Format$(Timer - s, "0.0000")
        s = Timer
        a = .Value
        x = WorksheetFunction.VLookup("50000A", a, 2, 0)
        MsgBox msg & vbLf & "Array:" & vbTab & Format$(Timer - s, "0.0000")
    End With
End Sub

**danmcdon** · 08-21-2012, 08:57 PM

Thank you for the replies.

I am trying to value a set of securities using Monte Carlo simulations. I have 50 securities to value on 50 different days. Each security contains 12 parts, which are valued separately and added together. The calculation for part 1 is slightly different than parts 2 through 12, which is contained in a separate module and called when necessary. There are 3 vlookups for part 1 which are executed 3x50x50x12 times and 3 for parts 2-12 which are executed 3x50x50x12x(#simulations) times.

I need to run 50,000 simulations, but as of now even 10 simulations takes 30 seconds. When I replace the values derived from the vlookups with made-up constants, 10 simulations only takes 2 seconds.

**vandan_tanna** · 08-21-2012, 09:00 PM

I wonder if:

1. VBA collections will be faster?
2. VLOOKUP using sorted array and 'True' as the last parameter. I think this makes binary lookup for vlookup possible. You can search web for shallsort algorithms if you decide to go this route.

**jindon** · 08-21-2012, 09:01 PM

I'm not sure if I can help you yet, but can you upload a small sample file with your desired results?

**danmcdon** · 08-21-2012, 09:13 PM

Sorry I'm brand new to this forum and don't know how to upload files.

I'm also unfamiliar with collections. Is there any easy way to assign a range (e.g. Data!A1:F10000) to a collection?

Thanks to all for the assistance.

**jindon** · 08-21-2012, 09:19 PM

If go down the page, you will find "Go Advanced" button.

Then "Manage Attachment" will leads you to select the files and attach.

Regarding Collection, I prefer to use Dictionary object, so that you can refer afterwards.

**danmcdon** · 08-21-2012, 09:31 PM

I cut the results I'm seeking down to 5 securites and 5 days and did not include all the input data but hopefully this will ggive you the idea.

**danmcdon** · 08-21-2012, 09:24 PM

I am going to private message you with the file if that's OK.

**jindon** · 08-21-2012, 09:27 PM

Originally Posted by danmcdon

I am going to private message you with the file if that's OK.

I don't think so, it against the rule here.

If your data have sensitive data, just change them to dummy.
Is that possible?

**jindon** · 08-21-2012, 10:01 PM

Hummm

What are the result that you are expecting?
Can you attach the result and the calculation algorithm?

**danmcdon** · 08-21-2012, 10:10 PM

The results in this abbreviated case on sheet "Results" would be -$18,875,291, -$18,881,589, -$18,889,169, -$18,906,998, -$18,911,267 in cells B3:B7, and $0 for the remaining values. I'm not sure what you mean by calculation algorithm other than the code in the file above.

Do you notice any obvious inefficiencies or suggestions for how to look up values such as those in sheet "FINAL_BRAZIL." In the real file there are 3 additional sheets like "FINAL_BRAZIL" with input data, each containing over 50,000 observations.

**jindon** · 08-21-2012, 10:46 PM

Sorry, but I don't want to read all through your code to find out what you are trying to do.

Hope someone else will come to help you.

**danmcdon** · 08-21-2012, 10:51 PM

Thanks for trying.

**vandan_tanna** · 08-22-2012, 10:28 AM

@danmcdon: your code errors out for me at several steps due to defined names issues. So I can be very general in help.
Snap3.jpg

1. Seems like most of your VLOOKUPs are happening on the ranges on the worksheet rather than your original post

Dim ArrData

ArrData = range("data")

Value = Application.WorksheetFunction.Vlookup(LookupValue, ArrData, 5, 0)

If you changed your VLOOKUPs to above method...it "should" be faster.

VBA Arrays - Faster alternative than Vlookup?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA Arrays - Faster alternative than Vlookup?

Re: VBA Arrays - Faster alternative than Vlookup?

Re: VBA Arrays - Faster alternative than Vlookup?

Re: VBA Arrays - Faster alternative than Vlookup?

Re: VBA Arrays - Faster alternative than Vlookup?

Re: VBA Arrays - Faster alternative than Vlookup?

Re: VBA Arrays - Faster alternative than Vlookup?

Re: VBA Arrays - Faster alternative than Vlookup?

Re: VBA Arrays - Faster alternative than Vlookup?

Re: VBA Arrays - Faster alternative than Vlookup?

Re: VBA Arrays - Faster alternative than Vlookup?

Re: VBA Arrays - Faster alternative than Vlookup?

Re: VBA Arrays - Faster alternative than Vlookup?

Re: VBA Arrays - Faster alternative than Vlookup?

Re: VBA Arrays - Faster alternative than Vlookup?

Re: VBA Arrays - Faster alternative than Vlookup?

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions