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

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

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

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

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

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

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

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

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

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