VBA - Use a table for an array instead of hard coding values

[graviz]

I'm trying to improve my coding and have created a function to loop through an array of values to see if a particular field contains them. Right now I have them hard coded as seen below. I'd like the array to read from a table if possible. I created a table called "REF_IHS_Projects" and the column is called "ID" that contains the values below.

Function IHS_Project_Levels(Project As String) As Integer

Dim CodeArray, i As Integer
       CodeArray = Array("sh.p.smart home", "sh.p.supply chain", "sh.p.wfm / ofs", "sh.x.perficient", "sh.x.wfm/ofs", "sh.x.crafted", "sh.x.d2c.interstellar", "sh.x.dbdevelopment", "sh.x.kryptonite", "sh.x.perficient", "sh.x.transformers")
       For i = 0 To UBound(CodeArray)
          If InStr(Project, CodeArray(i)) Then
                IHS_Project_Levels = 1
               Exit Function
          End If
       Next
       IHS_Project_Levels = 0


End Function

I've googled using the ADODB recordset function but can't figure out how to modify my code above. Thanks in advance!

 

[theDBguy]

Hi. Just curious, a table is like an array too. So, why not just use the table rather than move what's in the table into an array? If you were able to read the table into an array, you could just as simply use the values you read from the table for whatever you need the values in the array. Just a thought...

 

[graviz]

Hi. Just curious, a table is like an array too. So, why not just use the table rather than move what's in the table into an array? If you were able to read the table into an array, you could just as simply use the values you read from the table for whatever you need the values in the array. Just a thought...

I'm open to suggestions. I started with the following in the query design view:
"IHS Projects: IIf([Project]="sh.p.smart home" Or [Project]="sh.p.supply chain" Or [Project]="sh.p.wfm / ofs" Or [Project]="sh.x.perficient" Or [Project]="sh.x.wfm/ofs" Or [Project]="sh.x.crafted" Or [Project]="sh.x.d2c.interstellar" Or [Project]="sh.x.dbdevelopment" Or [Project]="sh.x.kryptonite" Or [Project]="sh.x.perficient" Or [Project]="sh.x.transformers",1,0)"

I then took this and make a custom function to do the same (which is pasted above). Now I'd like to keep my custom function but leverage the values in the table. I hope this makes sense?

 

[theDBguy]

I'm open to suggestions. I started with the following in the query design view:
"IHS Projects: IIf([Project]="sh.p.smart home" Or [Project]="sh.p.supply chain" Or [Project]="sh.p.wfm / ofs" Or [Project]="sh.x.perficient" Or [Project]="sh.x.wfm/ofs" Or [Project]="sh.x.crafted" Or [Project]="sh.x.d2c.interstellar" Or [Project]="sh.x.dbdevelopment" Or [Project]="sh.x.kryptonite" Or [Project]="sh.x.perficient" Or [Project]="sh.x.transformers",1,0)"

I then took this and make a custom function to do the same (which is pasted above). Now I'd like to keep my custom function but leverage the values in the table. I hope this makes sense?

Hmm, if you're thinking of using a table, you may not even need to use a function anymore. A joined query should be able to do that. Are you able to post a sample db with test data?

 

[Pat Hartman]

The simplest solution would be to add a Type code to the table that is used to hold the Project codes. You can assign "A" as the type for the projects listed above. They the query would be:

Select ...
From YourTable
Where Type = "A"

This gives you the ability to add or remove items from the group by modifying the Type code in the table rather than having to find/change all your queries and code.

 

[The_Doc_Man]

If I may offer a suggestion?

If you have a table of values for which to search, and those values would match EXACTLY if present at all, you might wish to do a subquery in which you do something like

SELECT X, Y, Z FROM TABLEA WHERE Z IN ( SELECT ZPRIME FROM TABLEB) ;

This syntax says "find cases where field Z in table A is also in another field in table B." In this example, table B would be the list of values you were seeking based on table A's fields. Look up the "IN" qualifier and sub-queries. If you use that method, you need no VBA and in fact this search would much faster. VBA is relatively slow whereas if you can do the right thing with SQL, that can go quite fast. Note also that other methods exist for this kind of search, such as outright JOIN methods.