VBA array basics - how to test for value?

[deko]

What I'd like to do is create an array of values and test for existence of
those values.
Here's the non-working code I'm having trouble with:

Dim wcSearch(4 To 7) As Integer

If Me.Value = wcSearch Then
Do Something
Else
Do Something else
End If

Compile Error: Type mismatch <<=== error when try to compile

Anyone know how to do this with a VBA array?

Thanks in advance!

[Wayne Gillespie]

On Tue, 30 Sep 2003 23:18:00 GMT, "deko" <dj****@hotmail.com> wrote:

What I'd like to do is create an array of values and test for existence of
those values.
Here's the non-working code I'm having trouble with:

Dim wcSearch(4 To 7) As Integer

If Me.Value = wcSearch Then
Do Something
Else
Do Something else
End If

Compile Error: Type mismatch <<=== error when try to compile

Anyone know how to do this with a VBA array?

Thanks in advance!

Dim wcSearch(4 To 7) As Integer
Dim i As Integer

For i = LBound(wcSearch) To UBound(wcSearch)
If Me.Value = wcSearch(i) Then
Do Something
Exit For
Else
Do Something else
End If
Next i
Wayne Gillespie
Gosford NSW Australia

[deko]

Thanks for the reply. That code seems to work okay, but I've just
discovered I need change the contents of the array to something like:

Dim wcSearch(1, 2, 3, 8, 9) As Integer

But the line:

For i = LBound(wcSearch) To UBound(wcSearch)

does not appear to work now.

How to remedy?

Thanks again!!

"Wayne Gillespie" <be*****@NObestfitsoftwareSPAM.com.au> wrote in message
news:b8********************************@4ax.com...

On Tue, 30 Sep 2003 23:18:00 GMT, "deko" <dj****@hotmail.com> wrote:

What I'd like to do is create an array of values and test for existence ofthose values.
Here's the non-working code I'm having trouble with:

Dim wcSearch(4 To 7) As Integer

If Me.Value = wcSearch Then
Do Something
Else
Do Something else
End If

Compile Error: Type mismatch <<=== error when try to compile

Anyone know how to do this with a VBA array?

Thanks in advance!

Dim wcSearch(4 To 7) As Integer
Dim i As Integer

For i = LBound(wcSearch) To UBound(wcSearch)
If Me.Value = wcSearch(i) Then
Do Something
Exit For
Else
Do Something else
End If
Next i
Wayne Gillespie
Gosford NSW Australia

[Trevor Best]

On Tue, 30 Sep 2003 23:28:55 GMT in comp.databases.ms-access, Wayne
Gillespie <be*****@NObestfitsoftwareSPAM.com.au> wrote:

On Tue, 30 Sep 2003 23:18:00 GMT, "deko" <dj****@hotmail.com> wrote:

What I'd like to do is create an array of values and test for existence of
those values.
Here's the non-working code I'm having trouble with:

Dim wcSearch(4 To 7) As Integer

If Me.Value = wcSearch Then
Do Something
Else
Do Something else
End If

Compile Error: Type mismatch <<=== error when try to compile

Anyone know how to do this with a VBA array?

Thanks in advance!

Dim wcSearch(4 To 7) As Integer
Dim i As Integer

For i = LBound(wcSearch) To UBound(wcSearch)
If Me.Value = wcSearch(i) Then
Do Something
Exit For
Else
Do Something else
End If
Next i

Not sure you'd want to "Do something else" at that point, perhaps...

For i = LBound(wcSearch) To UBound(wcSearch)
If Me.Value = wcSearch(i) Then
Do Something
Exit For
End If
Next i
If i > UBound(wcSearch) Then
' exited For loop naturally
' so didn't find one
Do Something Else
End If

--
A)bort, R)etry, I)nfluence with large hammer.

[Wayne Gillespie]

On Tue, 30 Sep 2003 23:45:02 GMT, "deko" <dj****@hotmail.com> wrote:
Try this

Dim wcSearch As Variant
Dim i As Integer

wcSearch = Array(1, 2, 3, 8, 9)
For i = LBound(wcSearch) To UBound(wcSearch)
If Me.ControlName.Value = wcSearch(i) Then
'Do Something
Exit For
Next i

'Trevor's suggestion
If i > UBound(wcSearch) Then
' exited For loop naturally
' so didn't find one
Do Something Else
End If

Thanks for the reply. That code seems to work okay, but I've just
discovered I need change the contents of the array to something like:

Dim wcSearch(1, 2, 3, 8, 9) As Integer

But the line:

For i = LBound(wcSearch) To UBound(wcSearch)

does not appear to work now.

How to remedy?

Thanks again!!

"Wayne Gillespie" <be*****@NObestfitsoftwareSPAM.com.au> wrote in message
news:b8********************************@4ax.com.. .

On Tue, 30 Sep 2003 23:18:00 GMT, "deko" <dj****@hotmail.com> wrote:

>What I'd like to do is create an array of values and test for existenceof >those values.
>Here's the non-working code I'm having trouble with:
>
>Dim wcSearch(4 To 7) As Integer
>
>If Me.Value = wcSearch Then
> Do Something
>Else
> Do Something else
>End If
>
>Compile Error: Type mismatch <<=== error when try to compile
>
>Anyone know how to do this with a VBA array?
>
>Thanks in advance!
>

Dim wcSearch(4 To 7) As Integer
Dim i As Integer

For i = LBound(wcSearch) To UBound(wcSearch)
If Me.Value = wcSearch(i) Then
Do Something
Exit For
Else
Do Something else
End If
Next i
Wayne Gillespie
Gosford NSW Australia

Wayne Gillespie
Gosford NSW Australia

[deko]

works great! But do I need the "Exit For" statement?

This seems to work okay:

Dim wcSearch As Variant
Dim i As Integer

wcSearch = Array(1, 2, 3, 8, 9)
For i = LBound(wcSearch) To UBound(wcSearch)
If Me.ControlName.Value = wcSearch(i) Then
DoSomething
Else
DoSomethingElse
Next i

Why do I need to Exit the For loop?
am I missing something?

Thanks Wayne and Trevor! I appreciate the help very much!

"Wayne Gillespie" <be*****@NObestfitsoftwareSPAM.com.au> wrote in message
news:gl********************************@4ax.com...

On Tue, 30 Sep 2003 23:45:02 GMT, "deko" <dj****@hotmail.com> wrote:
Try this

Dim wcSearch As Variant
Dim i As Integer

wcSearch = Array(1, 2, 3, 8, 9)
For i = LBound(wcSearch) To UBound(wcSearch)
If Me.ControlName.Value = wcSearch(i) Then
'Do Something
Exit For
Next i

'Trevor's suggestion
If i > UBound(wcSearch) Then
' exited For loop naturally
' so didn't find one
Do Something Else
End If

Thanks for the reply. That code seems to work okay, but I've just
discovered I need change the contents of the array to something like:

Dim wcSearch(1, 2, 3, 8, 9) As Integer

But the line:

For i = LBound(wcSearch) To UBound(wcSearch)

does not appear to work now.

How to remedy?

Thanks again!!

"Wayne Gillespie" <be*****@NObestfitsoftwareSPAM.com.au> wrote in message
news:b8********************************@4ax.com.. .

On Tue, 30 Sep 2003 23:18:00 GMT, "deko" <dj****@hotmail.com> wrote:

>What I'd like to do is create an array of values and test for

existenceof

>those values.
>Here's the non-working code I'm having trouble with:
>
>Dim wcSearch(4 To 7) As Integer
>
>If Me.Value = wcSearch Then
> Do Something
>Else
> Do Something else
>End If
>
>Compile Error: Type mismatch <<=== error when try to compile
>
>Anyone know how to do this with a VBA array?
>
>Thanks in advance!
>

Dim wcSearch(4 To 7) As Integer
Dim i As Integer

For i = LBound(wcSearch) To UBound(wcSearch)
If Me.Value = wcSearch(i) Then
Do Something
Exit For
Else
Do Something else
End If
Next i
Wayne Gillespie
Gosford NSW Australia

Wayne Gillespie
Gosford NSW Australia

[Wayne Gillespie]

On Wed, 01 Oct 2003 00:29:30 GMT, "deko" <dj****@hotmail.com> wrote:

works great! But do I need the "Exit For" statement?

This seems to work okay:

Dim wcSearch As Variant
Dim i As Integer

wcSearch = Array(1, 2, 3, 8, 9)
For i = LBound(wcSearch) To UBound(wcSearch)
If Me.ControlName.Value = wcSearch(i) Then
DoSomething
Else
DoSomethingElse
Next i

Why do I need to Exit the For loop?
am I missing something?

The code you have above will execute code for every element in the array.
If the array element = MeControlName.Value DoSomething will execute.
If the array element <> MeControlName.Value DoSomethingElse will execute.
If this is how you want the code to execute then the Exit For should not be used.

If however you want DoSomethingElse to ONLY execute if MeControlName.Value is not found in the array at all, then there is no need to continue looping
the array after a match is found (unless there could be additional matches within the array). The Exit For stops the loop when a match is found. The
value of the variable i will be <=UBound(wcSearch) so the check Trevor added will not be executed. If you do not use Exit For the loop will continue
to the end and the value of i will be UBound(wcSearch)+1. Therefore Trevor's check will ALWAYS execute whether you want it to or not.

Your design will determine whether to use Exit For or not.
Wayne Gillespie
Gosford NSW Australia

[Ross Presser]

"deko" <dj****@hotmail.com> wrote in
news:Ia********************@newssvr21.news.prodigy .com:

What I'd like to do is create an array of values and test for
existence of those values.
Here's the non-working code I'm having trouble with:

Dim wcSearch(4 To 7) As Integer

If Me.Value = wcSearch Then
Do Something
Else
Do Something else
End If

Compile Error: Type mismatch <<=== error when try to compile

Anyone know how to do this with a VBA array?

Thanks in advance!

Might be barking up the wrong tree, but whenever I see "test for existence of those values" I think
Dictionary, not array.

Dim wcSearch as Dictionary
wcSearch.add 1,"z"
wcSearch.add 3,"z"
wcSearch.add 5,"z"
wcSearch.add 7,"z"

if wcSearch.Exists(Me.Value) Then
do something
else
do something else
end if
--
Ross Presser -- rpresser AT imtek DOT com
"... VB is essentially the modern equivalent of vulgar Latin in 13th Centurary Europe. Understand it, and
you can travel to places you never heard of and still understand some people." -- Alex K. Angelopoulos

[David W. Fenton]

rp******@NOSPAM.imtek.com.invalid (Ross Presser) wrote in
<Xn**********************@129.250.170.100>:

Might be barking up the wrong tree, but whenever I see "test for
existence of those values" I think Dictionary, not array.

Dim wcSearch as Dictionary
wcSearch.add 1,"z"
wcSearch.add 3,"z"
wcSearch.add 5,"z"
wcSearch.add 7,"z"

if wcSearch.Exists(Me.Value) Then
do something
else
do something else
end if

I don't see any "Dictionary" data or object type in Access97 or
Access2K.

Is that VB only?

Is it a custom type that can be implemented in some other way?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[rkc]

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.78.. .

I don't see any "Dictionary" data or object type in Access97 or
Access2K.

Is that VB only?

Is it a custom type that can be implemented in some other way?

It's part of the Windows Scripting Runtime library so you would
have no use for it.

[David W. Fenton]

rk*@yabba.dabba.do.rochester.rr.mom (rkc) wrote in
<IK***************@twister.nyroc.rr.com>:

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.78. ..

I don't see any "Dictionary" data or object type in Access97 or
Access2K.

Is that VB only?

Is it a custom type that can be implemented in some other way?

It's part of the Windows Scripting Runtime library so you would
have no use for it.

Ah.

Nor should anyone else, then.

BTW, can it be used with late binding successfully?

And do people who depend on it check that it's installed before
using it?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[rkc]

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.78.. .

rk*@yabba.dabba.do.rochester.rr.mom (rkc) wrote in
<IK***************@twister.nyroc.rr.com>:

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.78. ..

I don't see any "Dictionary" data or object type in Access97 or
Access2K.

It's part of the Windows Scripting Runtime library so you would
have no use for it.
Ah.

Nor should anyone else, then.

BTW, can it be used with late binding successfully?

You do not need to set a reference to the library.
CreateObject ("Scripting.Dictionary") works just fine.
And do people who depend on it check that it's installed before
using it?

Can't answer that one for obvious reasons..
Dictionary advantages over a Collection:

Speed increase.
..Key method is read/write
..Keys method returns an array of all keys.
..Exists method determines if a key exists.
..Items method returns an array of all items.
..Item method is read/write.

[Ross Presser]

dX********@bway.net.invalid (David W. Fenton) wrote in
news:94***************************@24.168.128.78:

It's part of the Windows Scripting Runtime library so you would
have no use for it.
Ah.

Your decisions are your own.
Nor should anyone else, then.
Your opinions are your own, and I wish they'd remain your own.
BTW, can it be used with late binding successfully?
Of course.
And do people who depend on it check that it's installed before
using it?

Since I do not publish applications but instead use them in my own
corporate environment, I have control over that environment.

[David W. Fenton]

rk*@yabba.dabba.do.rochester.rr.mom (rkc) wrote in
<CG****************@twister.nyroc.rr.com>:

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.78. ..

rk*@yabba.dabba.do.rochester.rr.mom (rkc) wrote in
<IK***************@twister.nyroc.rr.com>:

>"David W. Fenton" <dX********@bway.net.invalid> wrote in
>message news:94***************************@24.168.128.78.. .
>
>> I don't see any "Dictionary" data or object type in Access97
>> or Access2K. >It's part of the Windows Scripting Runtime library so you would
>have no use for it.
Ah.

Nor should anyone else, then.

BTW, can it be used with late binding successfully?

You do not need to set a reference to the library.
CreateObject ("Scripting.Dictionary") works just fine.

And do people who depend on it check that it's installed before
using it?

Can't answer that one for obvious reasons..

Well, then you don't use the library, I take it?

If not, do you get paid for this kind of neglect of your duties?
Dictionary advantages over a Collection:

Speed increase.
.Key method is read/write
.Keys method returns an array of all keys.
.Exists method determines if a key exists.
.Items method returns an array of all items.
.Item method is read/write.

Why are you comparing to a collection instead of an array?

And isn't there a performance hit for using an outside library for
this kind of data structure?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

rp******@NOSPAM.imtek.com.invalid (Ross Presser) wrote in
<Xn**********************@129.250.170.91>:

dX********@bway.net.invalid (David W. Fenton) wrote in
news:94***************************@24.168.128.7 8:

And do people who depend on it check that it's installed before
using it?

Since I do not publish applications but instead use them in my own
corporate environment, I have control over that environment.

Every desktop?

And you assume that every desktop is running properly?

I guess you probably have no error checking in any of your code,
since you've already accounted for everything that could go wrong,
right?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[rkc]

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.90.. .

If not, do you get paid for this kind of neglect of your duties?

Sorry. Not interested.