Does VBA have a string indexed array capability?

mdlueck · Oct 6, 2011

Greetings,

Does VBA in Access 2007 have an array type object which supports a string to be the identifier into the array storage space rather than a numeric?

That would save me having to scan a lookup array to find the ID# that attribute's other parameters are stored at in other array objects.

I am in need of something similar to the Stem in the Rexx language.

For example:

Code:

  'Define forms here...
  strFormNameArray(0) = "1"
  strFormNameArray(1) = "partsbuyercodeselect"

  'Define SQL for normal forms here...
  strSQLNormArray(1) = "SELECT t.id,t.sort,t.active,t.title FROM tmptblqry_partsbuyercodetype AS t WHERE (((t.active) = 1))"

I want to do something like:

Code:

strFormNameIndexArray("partsbuyercodeselect") = "1"

I have not seen support for such in VBA. Suggestions other than scanning the array with the master list?

stopher · Oct 6, 2011

I'm not entirely sure what you are after with the SQL thing. However, maybe this will help...

VBA has collections which you can store a collection of data. You can add items to the collection then recall them using a string index like this:

Code:

Dim oColl As Collection

Set oColl = New Collection

oColl.Add "1", "partsbuyercodeselect"
oColl.Add "2", "somethingelse"
oColl.Add "3", "somethingmore"
oColl.Add "4", "anything"

Debug.Print oColl("partsbuyercodeselect")

This will output a value of 1 (string) to the immediate window.

Collections are great if you only need to add and read them. But if you need to update an item they are rather tedious. However, the Microsoft Scripting Runtime provides a dictionary class which does much the same and more.

Code:

Dim dict As Dictionary

Set dict = New Dictionary

dict.Add "partsbuyercodeselect", "1"
dict.Add "somethingelse", "2"
dict.Add "somethingmore", "3"
dict.Add "anything", "4"

Debug.Print dict("somethingelse")                  'outputs 2 (string)

dict("somethingelse") = "23"

Debug.Print dict("somethingelse")                  'outputs 23 (string)

Where these two data types really come into their own is when they are used to store objects rather than strings. In this way I can easily access the object for say "Fred" and hence all the properties/methods of the Fred object.

hth

Chris

mdlueck · Oct 6, 2011

Yes, I think collections are what I was looking for.

"Using Custom Collections in Microsoft Access"
http://www.databaseadvisors.com/new...ng custom collections in microsoft access.asp

And yes, I actually need to be able to store multiple pieces of string information about a string named entity.

And I am statically defining the entries in my source code, so the tedious editing you mentioned may wait for another day!

MarkK · Oct 7, 2011

Or maybe you want an enumerated type to use as subscripts in your array? Consider...

Code:

[COLOR="Green"]'type declaration[/COLOR]
Public Enum enMyEnumeratedType
  enMyEnumNotSet
  enMyEnumSetLow
  enMyEnumSetMedium
  enMyEnumSetHigh
  enMyEnumSetOverTheTop
End Enum

[COLOR="Green"]'public array declaration[/COLOR]
Public MyArray(enMyEnumLow To enMyEnumOverTheTop) As String

[COLOR="Green"]'method to fill an array that is subscripted using an enumerated type value[/COLOR]
Sub FillArray
  dim i as enMyEnumeratedtype
  for i = enMyEnumSetLow to enMyEnumOverTheTop
    MyArray(i) = i
  next
  debug.print MyArray(enMyEnumSetLow), MyArray(enMyEnumOverTheTop)
End Sub

Galaxiom · Oct 7, 2011

mdlueck said:
And I am statically defining the entries in my source code, so the tedious editing you mentioned may wait for another day!

Avoid the tedium by storing the information in a table where it is easily edited either directly or using queries if you like. Then open a recordset on that table and write the items to the collection or dictionary in a loop.

vbaInet · Oct 7, 2011

Galaxiom said:
Avoid the tedium by storing the information in a table where it is easily edited either directly or using queries if you like. Then open a recordset on that table and write the items to the collection or dictionary in a loop.

mdlueck likes the complex way of doing things apparently

mdlueck, also note that if the value of one of your variables change in your database, the variable's value will not change.

My preference is a Dictionary object and if you want some info about it here's a link:

http://www.techbookreport.com/tutorials/vba_dictionary.html

mdlueck · Oct 7, 2011

Indeed the collection suggestion worked, and I only slightly needed to change the public interface of my class to exchange data storage backends.

Here is the code for adding new entities to the collection:

Code:

'Add a new clsObjMultipleItemsForm item to the collection
Public Function Add(ByVal FormName As String, ByVal SQLNormal As String, ByVal SQLAdmin As String, ByVal DAOQueryDef As String, ByVal InitialSort As String) As clsObjMultipleItemsForm

  Dim newItem As New clsObjMultipleItemsForm
  Dim Key As Variant
    
  With newItem
    .FormName = FormName
    .SQLNormal = SQLNormal
    .SQLAdmin = SQLAdmin
    .DAOQueryDef = DAOQueryDef
    .InitialSort = InitialSort
    Key = .FormName
  End With
    
  'add to the private collection
  m_PrivateCollection.Add newItem, Key

  Set Add = newItem

Exit_Function:
    Set newItem = Nothing
    Exit Function

End Function

This LOC is responsible for making each entity in the collection searchable via the string name:

Code:

Key = .FormName

This solution is reminiscent of IBM's VisualAge C++ product and its Factory component. Very neat to see VBA capable of such concepts.

mdlueck · Jan 18, 2012

vbaInet said:
mdlueck likes the complex way of doing things apparently l

@vbaInet: Recalling this morning that you wrote the above... just FYI stats of how this solution is working out for me.

Now I have 20 forms supported by this "ObjMultipleItemsForms" collection class scheme. To add support for each form, it takes only one class method call requiring 5 arguments. For neatness, since a couple of the args are complete lines of SQL, I bust up the args onto separate lines. So "five lines per form" in place of needing to develop separate pass-through forms! That is FAR simpler than keeping track of dedicated pass-through forms!

Yes, up front work was more complex. As it is working now, I am able to ignore the under cover complexities, simply open my clsObjMultipleItemsFormsInit class, copy/paste an ObjMultipleItemsForms.Add call, and update the args for the new form. "Plop, plop, fizz, fizz, just works!"

vbaInet · Jan 19, 2012

If it works for you Michael

Good work!

Does VBA have a string indexed array capability?

mdlueck

Sr. Application Developer

stopher

AWF VIP

mdlueck

Sr. Application Developer

MarkK

bit cruncher

Galaxiom

Super Moderator

vbaInet

AWF VIP

mdlueck

Sr. Application Developer

mdlueck

Sr. Application Developer

vbaInet

AWF VIP

Similar threads

Users who are viewing this thread