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*****@NObest fitsoftwareSPAM .com.au> wrote in message
news:b8******** *************** *********@4ax.c om...

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*****@NObest fitsoftwareSPAM .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*****@NObest fitsoftwareSPAM .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*****@NObest fitsoftwareSPAM .com.au> wrote in message
news:gl******** *************** *********@4ax.c om...

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*****@NObest fitsoftwareSPAM .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.V alue DoSomething will execute.
If the array element <> MeControlName.V alue 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.V alue 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(wcSear ch) 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******** ************@ne wssvr21.news.pr odigy.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********@bwa y.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.