Check List in VBA

[alex]

Check List in VBA

Hello,

I’m looking for advice on the following:

Let’s say you wanted to check a list of values (same data type, same
length, etc) without using a table in VBA.

Something like…
If strValue in (
Value1,
Value2,
Value3,
ValueN)
Then do something

With a short list, a simple case statement would suffice, but because
of its structure (TestExpression), (ExpressionList), (StatementBlock),
it could get rather long.

What I’m trying to do is verify a list of user ID’s in code without
using a table object (which would be linked).

Am I crazy or can/should this be done easily? My reasoning is that
it’s faster and while not super secure, more secure than a table.

Thanks,
alex

 

[Tom Wickerath]

Hi Alex,

You can use the InStr function. Something like this:

strValues = "User1, User2, User3, User4, ....."

If InStr (strValues, strSearch) > 0 Then 'we found a match
Do something
End If

where strSearch is the value you are looking for.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[alex]

Hi Alex,

You can use the InStr function. Something like this:

strValues = "User1, User2, User3, User4, ....."

If InStr (strValues, strSearch) > 0 Then 'we found a match
    Do something
End If

where strSearch is the value you are looking for.

Tom Wickerath
Microsoft Access MVPhttp://www.accessmvp.com/TWickerath/
__________________________________________











- Show quoted text -

Thanks Tom for the help; I never thought of using that function!
I was hoping to go vertical with the list (might be easier to
maintain) but VBA only allows a certain number of continuation
characters ( _ ).
alex

 

[Tom Wickerath]

Hi Alex,

I've never heard of any limit for the number of line continuation
characters....what limit have you determined?

The best place for data is to store it in a table. You could then open a
recordset, and look for a match. But, you indicated in your opening post that
you are trying to accomplish this goal without using a table.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[John Spencer]

In Access 2003 I was allowed 24 before I got an error message about too many
line continuations. There is also a line length restriction - which I don't
recall at this time.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Tom Wickerath]

Thanks John. I've never bumped up against this limit, so I was not aware of it.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Larry Linson]

In Access 2003 I was allowed 24 before I got
an error message about too many line continuations.
There is also a line length restriction - which I don't
recall at this time.

I'm sure the line length maximum is available, but I tried searching and was
unable to find it. Googling for "Visual Basic for Applications" returns a
download link for the [MS-VBAL].pdf , the VBA Language Specification. It
says that an implementation may impose a length restriction. (When I went
to http://downloads.microsoft.com, I could not locate the language spec
download on that site, but it downloaded OK from the Google link.)

IIRC, in the past that length limit was 255. It may have been extended in
recent versions.

Larry Linson
Microsoft Office Access MVP

 

[alex]

 > In Access 2003 I was allowed 24 before I got
 > an error message about too many line continuations.
 > There is also a line length restriction - which I don't
 > recall at this time.

I'm sure the line length maximum is available, but I tried searching and was
unable to find it. Googling for "Visual Basic for Applications" returns a
download link for the [MS-VBAL].pdf , the VBA Language Specification. It
says that an implementation may impose a length restriction.  (When I went
tohttp://downloads.microsoft.com, I could not locate the language spec
download on that site, but it downloaded OK from the Google link.)

IIRC, in the past that length limit was 255. It may have been extended in
recent versions.

 Larry Linson
 Microsoft Office Access MVP

I was able to put about 60 or so user ids in a horizontal
fashion...looks tough to manage though.
I hear you about keeping data in a table, Tom!
alex