Is it possible to store a large array in Excel... [modified]
-
so that a customized user function can access it in order to search through it? The array should not be stored in a spreadsheet but it should be stored programmatically as a global variable. It appears that there are limitations to the size of a function file so it cannot be stored in the customized function file itself. Plus, it does not make sense for the function to load the array each time that the function is called.
modified on Wednesday, May 13, 2009 10:27 AM
-
so that a customized user function can access it in order to search through it? The array should not be stored in a spreadsheet but it should be stored programmatically as a global variable. It appears that there are limitations to the size of a function file so it cannot be stored in the customized function file itself. Plus, it does not make sense for the function to load the array each time that the function is called.
modified on Wednesday, May 13, 2009 10:27 AM
What are you trying to achieve? Why the need for the large array? What is it doing currently? How large is large? Have you considered storing the array data in a table in whatever database you are using, and accessing it that way? Much better than trying to mangle Excel to behave like a database.
Knowledge is knowing that the tomato is a fruit. Wisdom is not putting it in fruit salad!! Booger Mobile - Camp Quality esCarpade 2010
-
What are you trying to achieve? Why the need for the large array? What is it doing currently? How large is large? Have you considered storing the array data in a table in whatever database you are using, and accessing it that way? Much better than trying to mangle Excel to behave like a database.
Knowledge is knowing that the tomato is a fruit. Wisdom is not putting it in fruit salad!! Booger Mobile - Camp Quality esCarpade 2010
I need to develop a specialized user function that will search an array consisting of 16500+/- records and two columns. If I were to load the array each time the function is used, and if there were 10,000 records in the spreadsheet to whic the formula should be applied, it would take a great deal of time. Thus, it would be more appropriate to store the array as a global variable and to only search throuh the array in the function. Is it possible to store the array as a global variable and to access the variable inside a customized function? To create a customized function, press Alt-F11 key at the same time and then "Insert > Module". A new customized function can be written here, but it has a certain file size limitation that will not accommodate the two dimensional array. To use the new function select "Insert > Function".
-
I need to develop a specialized user function that will search an array consisting of 16500+/- records and two columns. If I were to load the array each time the function is used, and if there were 10,000 records in the spreadsheet to whic the formula should be applied, it would take a great deal of time. Thus, it would be more appropriate to store the array as a global variable and to only search throuh the array in the function. Is it possible to store the array as a global variable and to access the variable inside a customized function? To create a customized function, press Alt-F11 key at the same time and then "Insert > Module". A new customized function can be written here, but it has a certain file size limitation that will not accommodate the two dimensional array. To use the new function select "Insert > Function".
How is the array going to help you more then having the 16500 records in a table, and using SQL to extract the records you want? What specifically are you doing?
Knowledge is knowing that the tomato is a fruit. Wisdom is not putting it in fruit salad!! Booger Mobile - Camp Quality esCarpade 2010
-
How is the array going to help you more then having the 16500 records in a table, and using SQL to extract the records you want? What specifically are you doing?
Knowledge is knowing that the tomato is a fruit. Wisdom is not putting it in fruit salad!! Booger Mobile - Camp Quality esCarpade 2010
Read this article: http://office.microsoft.com/en-us/excel/HA011117011033.aspx?pid=CL100570551033[^] OK. I have an array created inside the function, and then I search through the array for a match of a value and return the second value as the result. The problem is that it does not make sense to create the array each time the function is run. Can it be stored some place else so that the function has access to it?