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

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

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
>
>

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

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

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
>
>

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" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > 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
> >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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"
<[email protected]>
> > wrote in message
> > news:[email protected]...
> > >
> > > 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
> > >
> >
> >
> >