# Thread: variable help (Excel 2000)

1. ## 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.

2. ## 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

3. ## 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.

4. ## 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^15-1) entries, but it becomes unwieldy far before you get to the maximum.

5. ## 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.

6. ## 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>

7. ## Re: variable help (Excel 2000)

Thanks Legare

works great,

Darryl.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•