Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Massive Array needed....in code
Ultimately, I need a user to be able to select one out of 406,396 individual values, probably out of a list box (if they can hold that many lines??) on a userform. The values are each unique -- an ID number My first thought was to simply plug all 406,396 values into a massive array: myarray = (1,2,...406,396). Using a loop code, I prepared my numbers to be dumped into an array with line continuations after every 11th value like this: "107894,107896,107901,107902,107905,107908,107909, 107910,107913,107915,107917 _" There are 36,945 lines like the above. I quickly leaned that Excel permits a maximum of 25 line continuations... Ideas? Any reccomendations generally about handling such large amounts of data would be appreciated. I would prefer NOT to dump these numbers in an actual worksheet. That is, if it is possible, I would like to have the numbers "in" the VBA code itself...by means of an array? a collection? -- Jason -- jasonsweeney ------------------------------------------------------------------------ jasonsweeney's Profile: http://www.excelforum.com/member.php...fo&userid=5222 View this thread: http://www.excelforum.com/showthread...hreadid=401043 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Massive Array needed....in code
Jason,
The concept of displaying 400K values to a user is inherently wrong. Devise some other means of them getting to the desired value. As for all the values; unless there is algorithm that can generate them. you will need to store them, either in a text file or database. A little explanation may help. Nick "jasonsweeney" wrote in message news:jasonsweeney.1uo4qb_1125558322.1142@excelforu m-nospam.com... Ultimately, I need a user to be able to select one out of 406,396 individual values, probably out of a list box (if they can hold that many lines??) on a userform. The values are each unique -- an ID number My first thought was to simply plug all 406,396 values into a massive array: myarray = (1,2,...406,396). Using a loop code, I prepared my numbers to be dumped into an array with line continuations after every 11th value like this: "107894,107896,107901,107902,107905,107908,107909, 107910,107913,107915,10791 7 _" There are 36,945 lines like the above. I quickly leaned that Excel permits a maximum of 25 line continuations... Ideas? Any reccomendations generally about handling such large amounts of data would be appreciated. I would prefer NOT to dump these numbers in an actual worksheet. That is, if it is possible, I would like to have the numbers "in" the VBA code itself...by means of an array? a collection? -- Jason -- jasonsweeney ------------------------------------------------------------------------ jasonsweeney's Profile: http://www.excelforum.com/member.php...fo&userid=5222 View this thread: http://www.excelforum.com/showthread...hreadid=401043 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Massive Array needed....in code
I agree with Nick....a user would surely have an issue picking one number out
of so many. Is there so other item that could be used as a drill down? "jasonsweeney" wrote: Ultimately, I need a user to be able to select one out of 406,396 individual values, probably out of a list box (if they can hold that many lines??) on a userform. The values are each unique -- an ID number My first thought was to simply plug all 406,396 values into a massive array: myarray = (1,2,...406,396). Using a loop code, I prepared my numbers to be dumped into an array with line continuations after every 11th value like this: "107894,107896,107901,107902,107905,107908,107909, 107910,107913,107915,107917 _" There are 36,945 lines like the above. I quickly leaned that Excel permits a maximum of 25 line continuations... Ideas? Any reccomendations generally about handling such large amounts of data would be appreciated. I would prefer NOT to dump these numbers in an actual worksheet. That is, if it is possible, I would like to have the numbers "in" the VBA code itself...by means of an array? a collection? -- Jason -- jasonsweeney ------------------------------------------------------------------------ jasonsweeney's Profile: http://www.excelforum.com/member.php...fo&userid=5222 View this thread: http://www.excelforum.com/showthread...hreadid=401043 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Massive Array needed....in code
nick, I am testing an encyption algorithm. In the case of the algorithm I am testing, there are a total of 406,396 possible "keys" that can be used. I do have an algorithm that creates the numbers (all numbers are relative primes to a modulus) but it takes, say, 1 minute to come up with the 406,396 numbers using the algorithm. That computation time is too slow for my purposes. Instead, since the array only needs to be calculated once, I would like to keep the numbers in memory so I can "pull" numbers from, say, the middle of the array quickly. I am intrigued with your text file idea....I've never worked with that concept before...how does it work? Regarding the selection process, I will probably break the numbers up so the user can select, say, a menu of numbers 1 through 50,000, a different menu for 50,001 through 100,000, etc. -- Jason -- jasonsweeney ------------------------------------------------------------------------ jasonsweeney's Profile: http://www.excelforum.com/member.php...fo&userid=5222 View this thread: http://www.excelforum.com/showthread...hreadid=401043 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Massive Array needed....in code
jason,
If it were me using your app, I would get annoyed if I had to choose from 50-100 values, never mind 50,000. As such I would pick 1 at random instead of wasting time going through the list(s). Does the user really have to choose a key for encryption ? Would they care ? NickHK "jasonsweeney" wrote in message news:jasonsweeney.1uoaah_1125565543.1477@excelforu m-nospam.com... nick, I am testing an encyption algorithm. In the case of the algorithm I am testing, there are a total of 406,396 possible "keys" that can be used. I do have an algorithm that creates the numbers (all numbers are relative primes to a modulus) but it takes, say, 1 minute to come up with the 406,396 numbers using the algorithm. That computation time is too slow for my purposes. Instead, since the array only needs to be calculated once, I would like to keep the numbers in memory so I can "pull" numbers from, say, the middle of the array quickly. I am intrigued with your text file idea....I've never worked with that concept before...how does it work? Regarding the selection process, I will probably break the numbers up so the user can select, say, a menu of numbers 1 through 50,000, a different menu for 50,001 through 100,000, etc. -- Jason -- jasonsweeney ------------------------------------------------------------------------ jasonsweeney's Profile: http://www.excelforum.com/member.php...fo&userid=5222 View this thread: http://www.excelforum.com/showthread...hreadid=401043 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Massive Array needed....in code
Hi Jason,
As Nick & Patrick have said, a list of 400k items is obviously too much to present in one go. Maybe look into the Treeview control (you'll need to add it to the controls toolbox) Then something like: Main node - ID's 1 - 999,999 Child1 Id's 1 - 100,000 GrandChild1 Id's 1 - 10,000 GtGrand1 Id's 1 - 1,000 GtGtGrandChild1 Id's 1-100 Child2 Id's 100,001 - 200,000 If user selects GtGtGrandChild1, then populate a normal listbox with all Id's that exist between 1-100. Search this ng for examples of treeview code. Just a thought. Regards, Peter T "jasonsweeney" wrote in message news:jasonsweeney.1uo4qb_1125558322.1142@excelforu m-nospam.com... Ultimately, I need a user to be able to select one out of 406,396 individual values, probably out of a list box (if they can hold that many lines??) on a userform. The values are each unique -- an ID number My first thought was to simply plug all 406,396 values into a massive array: myarray = (1,2,...406,396). Using a loop code, I prepared my numbers to be dumped into an array with line continuations after every 11th value like this: "107894,107896,107901,107902,107905,107908,107909, 107910,107913,107915,10791 7 _" There are 36,945 lines like the above. I quickly leaned that Excel permits a maximum of 25 line continuations... Ideas? Any reccomendations generally about handling such large amounts of data would be appreciated. I would prefer NOT to dump these numbers in an actual worksheet. That is, if it is possible, I would like to have the numbers "in" the VBA code itself...by means of an array? a collection? -- Jason -- jasonsweeney ------------------------------------------------------------------------ jasonsweeney's Profile: http://www.excelforum.com/member.php...fo&userid=5222 View this thread: http://www.excelforum.com/showthread...hreadid=401043 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Massive Array needed....in code
http://msdn.microsoft.com/library/de...ng03092004.asp
ADO And Text FIles this article shows how to unload and load an array to a text file http://support.microsoft.com/default...42&Product=vb6 HOWTO: Write Data to a File Using WriteFile API http://support.microsoft.com/default...b;en-us;151262 Working with Sequential Access Files http://www.applecore99.com/gen/gen029.asp -- Regards, Tom Ogilvy "jasonsweeney" wrote in message news:jasonsweeney.1uoaah_1125565543.1477@excelforu m-nospam.com... nick, I am testing an encyption algorithm. In the case of the algorithm I am testing, there are a total of 406,396 possible "keys" that can be used. I do have an algorithm that creates the numbers (all numbers are relative primes to a modulus) but it takes, say, 1 minute to come up with the 406,396 numbers using the algorithm. That computation time is too slow for my purposes. Instead, since the array only needs to be calculated once, I would like to keep the numbers in memory so I can "pull" numbers from, say, the middle of the array quickly. I am intrigued with your text file idea....I've never worked with that concept before...how does it work? Regarding the selection process, I will probably break the numbers up so the user can select, say, a menu of numbers 1 through 50,000, a different menu for 50,001 through 100,000, etc. -- Jason -- jasonsweeney ------------------------------------------------------------------------ jasonsweeney's Profile: http://www.excelforum.com/member.php...fo&userid=5222 View this thread: http://www.excelforum.com/showthread...hreadid=401043 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Massive Array needed....in code
Jason.
Why not fill the numbers into a grid on a worksheet. The user can then perfoirm a two-way look-up using a worksheet function (see John Walkenbech's website for the formula). You can employ a user form with this technique byu allowing the user to select the X and Y values (i.e. your grid's row and column references) from a list box and disply the chosen number in a textbox before using a command button to accept the number. Regards, Alasdair Stirling "jasonsweeney" wrote: Ultimately, I need a user to be able to select one out of 406,396 individual values, probably out of a list box (if they can hold that many lines??) on a userform. The values are each unique -- an ID number My first thought was to simply plug all 406,396 values into a massive array: myarray = (1,2,...406,396). Using a loop code, I prepared my numbers to be dumped into an array with line continuations after every 11th value like this: "107894,107896,107901,107902,107905,107908,107909, 107910,107913,107915,107917 _" There are 36,945 lines like the above. I quickly leaned that Excel permits a maximum of 25 line continuations... Ideas? Any reccomendations generally about handling such large amounts of data would be appreciated. I would prefer NOT to dump these numbers in an actual worksheet. That is, if it is possible, I would like to have the numbers "in" the VBA code itself...by means of an array? a collection? -- Jason -- jasonsweeney ------------------------------------------------------------------------ jasonsweeney's Profile: http://www.excelforum.com/member.php...fo&userid=5222 View this thread: http://www.excelforum.com/showthread...hreadid=401043 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Massive Array needed....in code
Me too. Why have to pick a number anyway? Why not let the user enter a
random number in a text box, from 1 to 400K, and use that number to select your number from that position in your array? NumberPicked = MyNumbers(AtPositionOfUsersRandomNumber) P.S. Definitely calc the numbers only once and save them to a file somewhere. "NickHK" wrote: jason, If it were me using your app, I would get annoyed if I had to choose from 50-100 values, never mind 50,000. As such I would pick 1 at random instead of wasting time going through the list(s). Does the user really have to choose a key for encryption ? Would they care ? NickHK "jasonsweeney" wrote in message news:jasonsweeney.1uoaah_1125565543.1477@excelforu m-nospam.com... nick, I am testing an encyption algorithm. In the case of the algorithm I am testing, there are a total of 406,396 possible "keys" that can be used. I do have an algorithm that creates the numbers (all numbers are relative primes to a modulus) but it takes, say, 1 minute to come up with the 406,396 numbers using the algorithm. That computation time is too slow for my purposes. Instead, since the array only needs to be calculated once, I would like to keep the numbers in memory so I can "pull" numbers from, say, the middle of the array quickly. I am intrigued with your text file idea....I've never worked with that concept before...how does it work? Regarding the selection process, I will probably break the numbers up so the user can select, say, a menu of numbers 1 through 50,000, a different menu for 50,001 through 100,000, etc. -- Jason -- jasonsweeney ------------------------------------------------------------------------ jasonsweeney's Profile: http://www.excelforum.com/member.php...fo&userid=5222 View this thread: http://www.excelforum.com/showthread...hreadid=401043 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Massive Array needed....in code
He said:
I am testing an encyption algorithm. so I think he his looking for something for his own convenience in doing his testing - and easy way to see a list of the available 400K + numbers. -- Regards, Tom Ogilvy "Charlie" wrote in message ... Me too. Why have to pick a number anyway? Why not let the user enter a random number in a text box, from 1 to 400K, and use that number to select your number from that position in your array? NumberPicked = MyNumbers(AtPositionOfUsersRandomNumber) P.S. Definitely calc the numbers only once and save them to a file somewhere. "NickHK" wrote: jason, If it were me using your app, I would get annoyed if I had to choose from 50-100 values, never mind 50,000. As such I would pick 1 at random instead of wasting time going through the list(s). Does the user really have to choose a key for encryption ? Would they care ? NickHK "jasonsweeney" wrote in message news:jasonsweeney.1uoaah_1125565543.1477@excelforu m-nospam.com... nick, I am testing an encyption algorithm. In the case of the algorithm I am testing, there are a total of 406,396 possible "keys" that can be used. I do have an algorithm that creates the numbers (all numbers are relative primes to a modulus) but it takes, say, 1 minute to come up with the 406,396 numbers using the algorithm. That computation time is too slow for my purposes. Instead, since the array only needs to be calculated once, I would like to keep the numbers in memory so I can "pull" numbers from, say, the middle of the array quickly. I am intrigued with your text file idea....I've never worked with that concept before...how does it work? Regarding the selection process, I will probably break the numbers up so the user can select, say, a menu of numbers 1 through 50,000, a different menu for 50,001 through 100,000, etc. -- Jason -- jasonsweeney ------------------------------------------------------------------------ jasonsweeney's Profile: http://www.excelforum.com/member.php...fo&userid=5222 View this thread: http://www.excelforum.com/showthread...hreadid=401043 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Massive link update | Excel Worksheet Functions | |||
Massive Find and Replace | Excel Discussion (Misc queries) | |||
Maybe an array is needed? | Excel Worksheet Functions | |||
search massive | Excel Worksheet Functions | |||
Array Help Needed | Excel Programming |