Number of Items in an Array.

Hi,

How do I find out the number of items in an array, so that I can loop check
the values in the array with the incoming data.

Dim Users as Variant
Dim NumValues, Counter as Integer
Users = Array("Bob","Fred")

NumValues = Users

For Counter = 0 to NumValues
    CheckUser = Users(Counter)
    If CheckUser = IncomingUser then
        Print "Cool"  -  okay this is just pseudo code.
    Else
        Print "Not Cool"
    End If
next

Cheers
Craig.


0
crb (7)
10/19/2004 5:39:38 AM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
387 Views

Similar Articles

[PageSpeed] 50

Hi
NumValues = ubound(Users)

--
Regards
Frank Kabel
Frankfurt, Germany


Craig & Co. wrote:
> Hi,
>
> How do I find out the number of items in an array, so that I can loop
> check the values in the array with the incoming data.
>
> Dim Users as Variant
> Dim NumValues, Counter as Integer
> Users = Array("Bob","Fred")
>
> NumValues = Users
>
> For Counter = 0 to NumValues
>     CheckUser = Users(Counter)
>     If CheckUser = IncomingUser then
>         Print "Cool"  -  okay this is just pseudo code.
>     Else
>         Print "Not Cool"
>     End If
> next
>
> Cheers
> Craig.

0
frank.kabel (11126)
10/19/2004 5:48:19 AM
A small correction, you need to add 1 as the first Item is 0:

NumValues = UBound(Users) + 1

KL

"Frank Kabel" <frank.kabel@freenet.de> wrote in message 
news:OuOnd8ZtEHA.4040@tk2msftngp13.phx.gbl...
> Hi
> NumValues = ubound(Users)
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
>
> Craig & Co. wrote:
>> Hi,
>>
>> How do I find out the number of items in an array, so that I can loop
>> check the values in the array with the incoming data.
>>
>> Dim Users as Variant
>> Dim NumValues, Counter as Integer
>> Users = Array("Bob","Fred")
>>
>> NumValues = Users
>>
>> For Counter = 0 to NumValues
>>     CheckUser = Users(Counter)
>>     If CheckUser = IncomingUser then
>>         Print "Cool"  -  okay this is just pseudo code.
>>     Else
>>         Print "Not Cool"
>>     End If
>> next
>>
>> Cheers
>> Craig.
> 


0
dropspam (2)
10/19/2004 5:51:12 AM
Hi Craig

NumValues = Ubound(Users) - (Lbound(Users)=0)

The reason for subtracting is, that TRUE in VBA is -1,
so if Lbound(Users) is 0, 1 is added ( -(-1) = +1) to
Ubound(Users).
In contrast, TRUE in Excel is 1 (positive one)

Normally you would loop the array with:

For Counter = Lbound(Users) to Ubound(Users)
etc.

Please notice, that

Dim NumValues, Counter as Integer

will dimension NumValues as Variant. Each variable
must be dimensioned explicitly:

Dim NumValues as Integer, Counter as Integer


-- 
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Craig & Co." <crb@amsa.gov.au> skrev i en meddelelse
news:4174a8e4$0$24156$c30e37c6@ken-reader.news.telstra.net...
> Hi,
>
> How do I find out the number of items in an array, so that I can loop
check
> the values in the array with the incoming data.
>
> Dim Users as Variant
> Dim NumValues, Counter as Integer
> Users = Array("Bob","Fred")
>
> NumValues = Users
>
> For Counter = 0 to NumValues
>     CheckUser = Users(Counter)
>     If CheckUser = IncomingUser then
>         Print "Cool"  -  okay this is just pseudo code.
>     Else
>         Print "Not Cool"
>     End If
> next
>
> Cheers
> Craig.
>
>


0
10/19/2004 6:30:15 AM
That depends upon the array lower bound. To be absolutely sure, you could
use

    For Counter = LBound(Users,1) To  UBound(Users,1)

caters for most options

-- 

HTH

RP

"KL" <dropspam@rathole.nul> wrote in message
news:OIuZl%23ZtEHA.2128@TK2MSFTNGP11.phx.gbl...
> A small correction, you need to add 1 as the first Item is 0:
>
> NumValues = UBound(Users) + 1
>
> KL
>
> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> news:OuOnd8ZtEHA.4040@tk2msftngp13.phx.gbl...
> > Hi
> > NumValues = ubound(Users)
> >
> > --
> > Regards
> > Frank Kabel
> > Frankfurt, Germany
> >
> >
> > Craig & Co. wrote:
> >> Hi,
> >>
> >> How do I find out the number of items in an array, so that I can loop
> >> check the values in the array with the incoming data.
> >>
> >> Dim Users as Variant
> >> Dim NumValues, Counter as Integer
> >> Users = Array("Bob","Fred")
> >>
> >> NumValues = Users
> >>
> >> For Counter = 0 to NumValues
> >>     CheckUser = Users(Counter)
> >>     If CheckUser = IncomingUser then
> >>         Print "Cool"  -  okay this is just pseudo code.
> >>     Else
> >>         Print "Not Cool"
> >>     End If
> >> next
> >>
> >> Cheers
> >> Craig.
> >
>
>


0
bob.phillips1 (6510)
10/19/2004 8:00:28 AM
since arrays are not limited to 0 or 1 for a lower bound it might be better
to use the old tried and true

Ubound(users) - Lbound(users) + 1

Sub Tester2()
Dim Users(6 To 12)
Debug.Print UBound(Users) - (LBound(Users) = 0)
Debug.Print UBound(Users) - LBound(Users) + 1
End Sub

Produced:
 12
 7
-- 
Regards,
Tom Ogilvy

"Leo Heuser" <leo.heuser@NOSPAMadslhome.dk> wrote in message
news:OGCHKWatEHA.2520@tk2msftngp13.phx.gbl...
> Hi Craig
>
> NumValues = Ubound(Users) - (Lbound(Users)=0)
>
> The reason for subtracting is, that TRUE in VBA is -1,
> so if Lbound(Users) is 0, 1 is added ( -(-1) = +1) to
> Ubound(Users).
> In contrast, TRUE in Excel is 1 (positive one)
>
> Normally you would loop the array with:
>
> For Counter = Lbound(Users) to Ubound(Users)
> etc.
>
> Please notice, that
>
> Dim NumValues, Counter as Integer
>
> will dimension NumValues as Variant. Each variable
> must be dimensioned explicitly:
>
> Dim NumValues as Integer, Counter as Integer
>
>
> -- 
> Best Regards
> Leo Heuser
>
> Followup to newsgroup only please.
>
> "Craig & Co." <crb@amsa.gov.au> skrev i en meddelelse
> news:4174a8e4$0$24156$c30e37c6@ken-reader.news.telstra.net...
> > Hi,
> >
> > How do I find out the number of items in an array, so that I can loop
> check
> > the values in the array with the incoming data.
> >
> > Dim Users as Variant
> > Dim NumValues, Counter as Integer
> > Users = Array("Bob","Fred")
> >
> > NumValues = Users
> >
> > For Counter = 0 to NumValues
> >     CheckUser = Users(Counter)
> >     If CheckUser = IncomingUser then
> >         Print "Cool"  -  okay this is just pseudo code.
> >     Else
> >         Print "Not Cool"
> >     End If
> > next
> >
> > Cheers
> > Craig.
> >
> >
>
>


0
twogilvy (1078)
10/19/2004 1:03:15 PM
I gave my answer, because the OP's example used the
Array function, but of course you're correct, that
Ubound(users) - Lbound(users) + 1
is the general way of getting the number of elements in an array.
Most of the time, I use it myself :-)

-- 
Best Regards
Leo Heuser

"Tom Ogilvy" <twogilvy@msn.com> skrev i en meddelelse
news:e8XqAwdtEHA.224@TK2MSFTNGP15.phx.gbl...
> since arrays are not limited to 0 or 1 for a lower bound it might be
better
> to use the old tried and true
>
> Ubound(users) - Lbound(users) + 1
>
> Sub Tester2()
> Dim Users(6 To 12)
> Debug.Print UBound(Users) - (LBound(Users) = 0)
> Debug.Print UBound(Users) - LBound(Users) + 1
> End Sub
>
> Produced:
>  12
>  7
> -- 
> Regards,
> Tom Ogilvy
>
> "Leo Heuser" <leo.heuser@NOSPAMadslhome.dk> wrote in message
> news:OGCHKWatEHA.2520@tk2msftngp13.phx.gbl...
> > Hi Craig
> >
> > NumValues = Ubound(Users) - (Lbound(Users)=0)
> >
> > The reason for subtracting is, that TRUE in VBA is -1,
> > so if Lbound(Users) is 0, 1 is added ( -(-1) = +1) to
> > Ubound(Users).
> > In contrast, TRUE in Excel is 1 (positive one)
> >
> > Normally you would loop the array with:
> >
> > For Counter = Lbound(Users) to Ubound(Users)
> > etc.
> >
> > Please notice, that
> >
> > Dim NumValues, Counter as Integer
> >
> > will dimension NumValues as Variant. Each variable
> > must be dimensioned explicitly:
> >
> > Dim NumValues as Integer, Counter as Integer
> >
> >
> > -- 
> > Best Regards
> > Leo Heuser
> >
> > Followup to newsgroup only please.
> >
> > "Craig & Co." <crb@amsa.gov.au> skrev i en meddelelse
> > news:4174a8e4$0$24156$c30e37c6@ken-reader.news.telstra.net...
> > > Hi,
> > >
> > > How do I find out the number of items in an array, so that I can loop
> > check
> > > the values in the array with the incoming data.
> > >
> > > Dim Users as Variant
> > > Dim NumValues, Counter as Integer
> > > Users = Array("Bob","Fred")
> > >
> > > NumValues = Users
> > >
> > > For Counter = 0 to NumValues
> > >     CheckUser = Users(Counter)
> > >     If CheckUser = IncomingUser then
> > >         Print "Cool"  -  okay this is just pseudo code.
> > >     Else
> > >         Print "Not Cool"
> > >     End If
> > > next
> > >
> > > Cheers
> > > Craig.
> > >
> > >
> >
> >
>
>


0
10/19/2004 1:46:25 PM
Reply:

Similar Artilces:

Disableing items in CLIstCtrl
Hello, Is there a way to disable an item in CListCtrl so that the item is gray out and a user won't be able to select it. Thank You! At this point I think you have to go to an owner-draw list control and do it yourself. You also have to prevent the selection from happening. joe On Fri, 15 Apr 2005 11:03:17 -0400, "Ririko Horvath" <horvathr@securemethods.com> wrote: >Hello, > > Is there a way to disable an item in CListCtrl so that the item is gray >out and a user won't be able to select it. > >Thank You! > Joseph M. Newcomer [MVP]...

Is there a way to empty the Deleted Items folder by date?
Is there a way to get Outlook to delete items in the Deleted Items folder that have been in there for some period of time (1 week, 1 month, ???)? I know there is a Tools option to empty it on exit, but I sometimes want to recover a file a few days later. FivePoundBag wrote: > Is there a way to get Outlook to delete items in the Deleted Items > folder that have been in there for some period of time (1 week, 1 > month, ???)? > > I know there is a Tools option to empty it on exit, but I sometimes > want to recover a file a few days later. Use AutoArchive. Enable the global...

OWA
SBS 2003 SP1, Exch 2003 SP2, FBA w/ SSL. If I add/delete a calendar event via OWA, I get a 5.1.7 NDR in my Inbox (pasted below). The calendar item is added/delete, and I see it in OWA or Outlook. I haven't seen any other posts similar to this, nothing on MS support. Any clues? ========== Your message did not reach some or all of the intended recipients. Subject: Sent: 12/21/2006 8:24 AM The following recipient(s) could not be reached: System Attendant on 12/21/2006 8:24 AM The e-mail address could not be found. Perhaps the recipient moved to a different e-mail organization, or t...

Contacts appear as mail items
Hi - Contacts from the Exchange server appear as mail items. WebDAV and HTTP Keep Alives are enabled. Any other suggestions to correct the problem? Thanks! Amy Cottrell <acottrell@getapproved.com> wrote: > Hi - Contacts from the Exchange server appear as mail items. WebDAV and > HTTP Keep Alives are enabled. Any other suggestions to correct the problem? This is a limitation in the current version of Entourage. See <http://www.entourage.mvps.org/faq/exchange_server.html#anchor-exup1>. Note followups...there is an Entourage-specific newsgroup. -- Adam Bailey | ...

Setting random number range based on query, not table
I have a "quiz" form that randomly pulls up questions from my table. It works fine, and I don't mind that it repeats questions. This allows me to sit and review for as long as I want. The problem is, that I want to use criteria in a query to limit the available questions. If I base this form on a query and limit the number of questions, the code I am using is still setting the maximum number as the number of records in my table. In the past, I had a similar database (which I have lost) that would open the form, set the selector to the last record available to the form,...

Serial number error in sales order entry window (2nd post)
I'm reposting this because there was no response to my first post. I'm sure I'm not the only one that has had this problem. I noticed that one of my coworkers invoiced and added (overided) a serial number into the Great Plains instead of allocating one of the existing ones. How do I return this serial number w/out affecting my inventory? I need to: 1. Return the item w/the wrong serial number. 2. Reinvoice the correct serial number. 3. NOT HAVE THE WRONG SERIAL NUMBER STUCK IN MY INVENTORY. How do I do this? Hi Paoakalani Try looking at Serial Number edit, by Blue Moon...

Odd Looking Number
Hi I am using a spreadsheet in Excel 2002 on Windows XP, for some reason the account numbers (which someone input, along with addresses) have a little green mark in the corner of the cell. I thought it was to show that there is a comment, but it is not. When you click on the cell, a little warning sign shows, saying ERROR - number in this cell formatted as text or preceded by an apostrophe. When I click on the arrow by the warning sign, it says Number stored as text Convert to number Ignore error Edit in formula bar Error checking options Show formula in audinting toolbar Now I l...

Numbers turn red when equals to 0
Good day, Im monitoring our stock inventory using excel, i just want to know if I can make a certain cell turn red if my inventory is >5 or equal to zero.? Thanks -- shiela21cute ------------------------------------------------------------------------ shiela21cute's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33169 View this thread: http://www.excelforum.com/showthread.php?threadid=529926 Check out Conditional formatting Ed -- EdMac ------------------------------------------------------------------------ EdMac's Profile: http://www.excelforum...

Item backorders on Inventory Transfer to Site
Hi all, Is there any way to leave a qty on "backorder" on an Inventory Transfer ? For example, we want to transfer 10 of item ABC to another site. The main site only has 4 right now. How do we have the system track that the other site still requires an additional 6 units of that item, and when the main site receives more of item ABC, it should flag that the other site still requires them. We will be using the order points to give us suggested levels, but there are times when we require more, and want the system to track that ? Any ideas.... Thanks, Chris B. ...

how to mark emails in deleted items as read automatically???
Hi How to mark emails in deleted items as read automatically??? Now i've to right click en choose 'mark as read'. If looked in rules, but one can not make a rule on the map 'deleted items' . Or am I wrong. Peter Peter, I think that you can go to options and st teh setting there. If I can remember right the option is "mark all deleted items as read". I don't know the version of Outlook you are woking in but it should be there. Hoop jy kom reg!! Marais van Zyl >-----Original Message----- >Hi > >How to mark emails in deleted items as read...

when I type a long number it shows up as smaller number and +
when converting from xls to csv format, some of my longer mortgage numbers get condensed into a smaller number with a letter, a plus sign and another number. example: 100020013120 turns into 1.0002E+11. Any way to get rid of this? Thanks, Yes, just specify the format that you want. Format Cells...>Number>0 decimal places Regards, Fred "SandyC" <SandyC@discussions.microsoft.com> wrote in message news:FA52E426-360E-4BE5-B63B-1209E673CA65@microsoft.com... > when converting from xls to csv format, some of my longer mortgage numbers > get conde...

Rotating page numbers
I have a document with both page layouts (landscape and portrait) however the page numbers obviously will be pointing in the direction of the associated layout does anyone know how to be consistent in this? ...

vendors and Items replicated to another company.
My customer has 2 companies under GP The 2 companies shared similar vendors and items. they wanted the system which allows vendors and items created in 1 company to be replicated to the next company. Anyone know any 3rd party module can manage! Example: A create a new item01 and it will automatic replicated to company B. Chris, There are 'master trigger' tools sold by Microsoft that can do this for Vendors, Customers and GL accounts - they're part of the Professional Services Tools Library. I don't know of a tool for inventory items. -- Victoria Yudin Dynamics GP MVP...

Recover Deleted item?
I have a user that is unable to recover any deleted items from the Deleted Items folder (they are trying to recover by selecting Tools/Recover Deleted Items) it is empty inside, like nothing has been deleted. Any help is appreciated. What is the deleted items retention policy? Are you using an Exchange server? Versions of Outlook/Exchange? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After furious head scratchin...

Finding unique numbers in a column
Is there any way to find the number of unique values among a set o values in a column in an excel sheet. I would also like to know th number of times each value appears in the column. Thank -- coolkid39 ----------------------------------------------------------------------- coolkid397's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2431 View this thread: http://www.excelforum.com/showthread.php?threadid=37924 There was also a similar thread under Excel Worksheet functions titled "Delete duplicates". Solution provided was to use: Data>Filter&g...

Create a textbox that displays a list of possible items as we type
Hi All, Is it possible to display a list once user starts typing a word in vba? For eg, I have a userform connected to access database(I can already pull info from access, based on what user inputs in ID field), So Once a user starts typing in a text box, lets say User Name textbox, a list of users is displayed (something like google search, we start writing in the search bar and it suggests us what we might want based on what text we input) SO if I type in "A" in User Name text box, I get all the names starting with "A" such as: Name: [ A.... ] ...

Customer Items
Is there any 3rd Party software that we can map one Item Number to Multiple Customer Items? regards, David, Do you mean your item number is ABC001, customer 0001 calls this same item XYZ002 and customer 0002 calls it KLM999? If so, GP will handle that out of the box, if not, can you describe what you're looking for in more detail? -- Victoria Yudin Dynamics GP MVP Flexible Solutions, Inc. "davidv" <davidv@discussions.microsoft.com> wrote in message news:E06C7280-BF4E-4B76-98D9-4A5E467BBC00@microsoft.com... > Is there any 3rd Party software that we can map on...

Chart Legend Items: hide/show
How do I hide/show a series on a chart legend (scatter plot.) I tried a null string (""), but the trace still shows. I also tried #N/A. But it then shows "#N/A"; the same with function: NA(). What I am trying to do is to have a control button to show or hide a series from the chart. I can set all the series values to #N/A which hides it, but I am having trouble trying to hide the legend for the series. I also tried an "if" function in the chart->series dialog, but It evidently will only accept a cell pointer. "Jon Peltier" <jonREMOVExlmvp@p...

Sequential numbering of invoices
I have recently downloaded an Excel template for invoices from Microsoft's website. Is there any way to fix it so that every time I create a new document based on this template, it automatically gives it an invoice number one greater than the previous invoice (eg, starting at 100 and then the next one I open would be 101, then 102 and so on)? http://www.mcgimpsey.com/excel/udfs/sequentialnums.html -- Regards, Peo Sjoblom "Nick Xylas" <nickxylas@wmconnect.com> wrote in message news:1180551140.693535.260930@g4g2000hsf.googlegroups.com... >I have recently download...

viewing deleted items that are outgoing "sent" messages
Version: 2004 Processor: Power PC Hi: I was able to view my deleted messages in the "deleted items" folder until recently. I am still able to view the deleted items that are incoming messages, but the outgoing messages or "sent" messages have completely disappeared. Can someone please advise on how I can fix this? Thank you. Diana "Diana_Kim@officeformac.com" wrote: > I was able to view my deleted messages in the "deleted items" folder until > recently. I am still able to view the deleted items that are incoming > messages, but the outgoing...

Convert a number to a time
I have the following number 232.5 which is 232 hours and 0.5 of an hour. I want to convert this to 232 hours and 30 minutes or 232:30. How can i do this? I know i need to multiply the decimal by 60, but how can i perform a calculation on just the decimal part of a number and not the whole number? Depending what you wish to do excel stores times as well as dates as numbers, with 1 being a full day. An option is to divide everything by 24 and use a custom format of dd hh:mm although this will express the hours as days and hours It depends what you want to do really -- Dav --------------...

Item Replenishment & Item Fulfill Method
Does anyone know the table names that the above 2 fields are located in? I would like to add these to a smartlist so I can make sure they are all set correctly. The Replenishment Method is o nthe Item Resource Planning Maintenance window and the Fulfill method is on the Item Engineering data window. We do have manufacturing so I am not sure if they are manufacturing tables or not. thanks! -- Doug ...

How can I write in a text in a cell using numbers and the letters.
I'm trying to get some codes in for example 511E09 is one fo them but when I try to enter another code it makes the code a formula and it changes to 5.11E+09 why is that? I tried changing it by formatting cells to text, but that doesn't change it. Waht can I do? Clear the data, change it to text, and then enter it. -- HTH RP (remove nothere from the email address if mailing direct) "Sandy" <Sandy@discussions.microsoft.com> wrote in message news:9C474C50-E47D-43EB-A4F0-B4D21141E39F@microsoft.com... > I'm trying to get some codes in for example 511E09 i...

multiple IF statements in array
How can I need to add another if, to this array {=AVERAGE(IF(close!U2:U19000="Y",close!R2:R19000))} if close!U2:U19000="Y" AND if close!Y2:Y19000="Y",... So, I have several Excel books, but they don't really go into any complex formulas. I haven't been able to figure out when I should use arrays, sumproduct as opposed to count, (--(sumproduct, etc. Is there a resource where I can see the logic behind the formula? I want to learn to fish!! Thanx!! ~Julz Hi try (as array formula) AVERAGE(IF((close!U2:U19000="Y")*(close!Y2:Y19000="...

I have moved my Inbox by "mistake" to my Deleted Items
I have moved my Inbox by "mistake" to my Deleted Items folder. I can not move it back. Has anyone had this problem? Can anyone help? The message I receive is: Can't move the items. Special folders, including Inbox, Contacts, Calendar, Notes, Task and Journal folders can not be moved. stevenson <lstevenson@crowell.com> wrote: > I have moved my Inbox by "mistake" to my Deleted Items > folder. I can not move it back. Has anyone had this > problem? Can anyone help? The message I receive is: > Can't move the items. Special folders, inc...