Results 1 to 7 of 7
Thread: variable help (Excel 2000)

20050429, 17:23 #1
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: variable help (Excel 2000)
What are you trying to accomplish?
You create an array of 64,001 elements, then only populate one in every 500 elements, so 63,872 elements remain empty. And you don't do anything with it  the function result is not assigned.

20050429, 17:30 #2
 Join Date
 May 2004
 Location
 Toronto, Ontario, Canada
 Posts
 425
 Thanks
 5
 Thanked 0 Times in 0 Posts
Re: variable help (Excel 2000)
Hans,
I'm tryng to pupulate a listbox with 8000 to 72000.
How did you figure out 64,001?
I want to start at 8000 and count to 72000 by 500
I did manage to count by 1's but that makes it too large a list.
is it possible or is it too large a number?
Thanks
Darryl

20050429, 17:32 #3
 Join Date
 May 2004
 Location
 Toronto, Ontario, Canada
 Posts
 425
 Thanks
 5
 Thanked 0 Times in 0 Posts
variable help (Excel 2000)
Hi all
What data type would I use if the numbers I am using are X=8000 to 72000
I keep getting an overflow error.
Public Function getnum() as long()
dim x(8000 to 72000) as long
dim y as long
For y=8000 to 72000 step 500
x(y)=y+500
next y
getnum = x
end function
Thanks
Darryl.

20050429, 17:37 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: variable help (Excel 2000)
If the list index starts at 8000 and runs to 8005, there are 8005  8000 + 1 = 6 entries:
8000
8001
8002
8003
8004
8005
Similarly, if the list index runs from 8000 to 72000, there are 72000  8000 + 1 = 64001 entries.
A list box can have only 32767 (=2^151) entries, but it becomes unwieldy far before you get to the maximum.

20050429, 17:48 #5
 Join Date
 May 2004
 Location
 Toronto, Ontario, Canada
 Posts
 425
 Thanks
 5
 Thanked 0 Times in 0 Posts
Re: variable help (Excel 2000)
Hans that's great,
But I didn't think that I had that many, I thought it would be about 144 entries.
I wanted it to be
8000
8500
9000
So what you're saying is even though each element is 500 the listbox still understands only 32,767 entrires and sees it as a 64,001?
Thanks,
Darryl.

20050429, 17:55 #6
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: variable help (Excel 2000)
Then your code should look more like this:
<pre>Public Function getnum() As Long()
Dim x(1 To 129) As Long
Dim iVal As Long
Dim I As Long
iVal = 8000
For I = 1 To 129
x(I) = iVal
iVal = iVal + 500
Next I
getnum = x
End Function
</pre>
Legare Coleman

20050429, 19:59 #7
 Join Date
 May 2004
 Location
 Toronto, Ontario, Canada
 Posts
 425
 Thanks
 5
 Thanked 0 Times in 0 Posts
Re: variable help (Excel 2000)
Thanks Legare
works great,
Darryl.