How to use array declared in VBA in spreadsheet

Could you explain in more detail what you’re trying to do?
Providing an example would help.

Hi Akuini,

thank you for your reply!

I am trying to load several tables into arrays and then I want to vlookup the arrays in my spreadsheet.

Ex. I have a combobox named combobox1 and it has two items.

Please Login or Register  to view this content.

I want to load a table into an array

Please Login or Register  to view this content.

Then in my spredsheet (not userform) I want to VLOOKUP the combobox value in the array/arrays.

Please Login or Register  to view this content.

I am not sure if this is faster/better then just using the underlaying table to VLOOKUP on?

Are you trying to compare which one is faster, doing VLOOKUP in the table or in the array?
In my opinion, I never tested it, it would be just the same, because VLOOKUP only find the value once.
Usually the difference is noticeable when we doing an iteration\looping. Looping through an array is faster than looping through a range, especially if you’re working on a large data.

Hi Akuini,

Yes, I want to know which one is faster but before I try it on large data I want to see if there is a notable difference.

I am thinking that maybe I can assign a named range to the array? Or is there a better way?

Or is there a better way?

Better way to what?
Maybe if you can describe your situation & what the goal is, we can discuss better ways to do it.

Hi Akuini,

thank you for your reply! I am asking for any way to access an array declared in an module in my spreadsheet??


I have a spreadsheet that uses a lot of vloopkup and match/countifs/sumifs and I am trying to loop through two combobox and each iteration of the loop takes a couple of seconds.

I am looking at ways of speeding up the process.

So I select an item in combobox1 and then the sheet calculates all possible combinations of two comboboxes, both comboboxes have 320 items.

I am using two for loops to loop through the comboboxes and I am storing the results of every iteration in an array.

What I am thinking is if I can run the vlookup and match/countifs and sumifs on an array and then run the loop?

Can you attach your sample workbook?

It is a very large file and it would take me hours to create a sample :/

From what I have googled it seems like I can assign an array to a named range and even hide the range from the user.

It is slower to use worksheet functions on arrays than on the worksheet. Excel is optimised for performing calculations on worksheets

Kyle123, TMS,

thank you for your replies!

I want to speed up my code.

I am probably explaining it poorly.

I want to read all my data (100+ data tables) into arrays, then vlookup all data and create a MASTER array, then run my code on it and then dump it to a sheet.

Maybe I am going down the wrong path?

Now I have tried using named ranges for arrays, however the 8192 characters limit for formulas makes the spreadsheet be unreliable?

Is there another way to access an array declared in VBA in my spreadsheet?

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi Kyle,

the named range corrupts my file and even if I try to save it as binary workbook it says that the file is corrupted and not possible to save on my computer.

Please Login or Register  to view this content.

I was trying to figure out if storing an array in a named range was faster and it seems like it isn't.

Please Login or Register  to view this content.

Look, no one can help you as long as you continue to refuse to upload a sample workbook. You’re wasting our time and yours. I’m afraid I’m ducking out of your threads until you upload one, without a workbook, we’re shooting in the dark.