VBA Arrays - Faster alternative than Vlookup?

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)

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.

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

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.

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.

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

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?

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.

Hummm

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

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.

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.

Thanks for trying.