Using collection to store variable values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm attempting to use a collection to store variable values associated with
some of my code, so I don't have to keep using DLOOKUP to get information
from some of my tables.

I have a function that I pass a key and an optional value. If the key value
is not part of the collection, I add it and set its value. If the optional
value is included, the function sets the value associated with the
collections key value. if no value is provided, the function returns the
value that is already associated with the key.

How can I enumerate the key values associated with this collection? I know
I can loop through the item numbers, but how can I see what keys are
associated with a particular item number?
 
Dale said:
I'm attempting to use a collection to store variable values associated with
some of my code, so I don't have to keep using DLOOKUP to get information
from some of my tables.

I have a function that I pass a key and an optional value. If the key value
is not part of the collection, I add it and set its value. If the optional
value is included, the function sets the value associated with the
collections key value. if no value is provided, the function returns the
value that is already associated with the key.

How can I enumerate the key values associated with this collection? I know
I can loop through the item numbers, but how can I see what keys are
associated with a particular item number?
Click to expand...


It seems like a stupid omission, but you can not get the key
value from a collection. I suggest that you use a two
dimensional array instead (probably faster too).
 
Thanks, Marsh,

I was just playing around with this concept, I don't really need the key
values but was wondering whether there was a method to do so.

A while back, I read an article that advised use of custom functions to
store variables rather than declaring Global Variables. It explaained that:

1. some global variables will lose their values when some errors occur,
causing unintended problems
2. unlike functions, you cannot use global variables as parameters in
queries, unless you build the SQL dynamically and concatenate the value of
the variable.

It described how to create the function that allows you to both set and
retrieve a value stored in a static variable. I've found this extremely
useful but in one of my programs have about a dozen of these functions. I
was toying with the idea of extending that functionality to a static
collection defined within a function, so that instead of having to have
numerous functions, I'd just have this one function that gets passed a key,
and a value (to set the value) or a key (to retrieve the value). So far,
that functionality is working fine in the test program I've developed. Have
not had a chance to test reading and writing to it multiple times (for a
speed test comparison).

The problem with using an array is that you either have to keep track of
which position the variable is stored in, or have to search the array for
the value. At least with a collection, you can get the value as long as you
know the key. Hopefully, the code that supports the collection is optimized
for this search.

Dale
 
Dale said:
I was just playing around with this concept, I don't really need the key
values but was wondering whether there was a method to do so.

A while back, I read an article that advised use of custom functions to
store variables rather than declaring Global Variables. It explaained that:

1. some global variables will lose their values when some errors occur,
causing unintended problems
2. unlike functions, you cannot use global variables as parameters in
queries, unless you build the SQL dynamically and concatenate the value of
the variable.

It described how to create the function that allows you to both set and
retrieve a value stored in a static variable. I've found this extremely
useful but in one of my programs have about a dozen of these functions. I
was toying with the idea of extending that functionality to a static
collection defined within a function, so that instead of having to have
numerous functions, I'd just have this one function that gets passed a key,
and a value (to set the value) or a key (to retrieve the value). So far,
that functionality is working fine in the test program I've developed. Have
not had a chance to test reading and writing to it multiple times (for a
speed test comparison).

The problem with using an array is that you either have to keep track of
which position the variable is stored in, or have to search the array for
the value. At least with a collection, you can get the value as long as you
know the key. Hopefully, the code that supports the collection is optimized
for this search.
Click to expand...


I agree with everthing above with the possible exception of
searching a collection being faster than searcing an array
(run some timing tests to verify or refute your assumption).
 
Marsh,

I tested this technique against using DLOOKUP, and it was about 6 times
faster, but have yet to test it against an array.

Did an on-line search and found a couple of articles that mentioned this.
Both indicated that using the array is faster if you have less than 100 key
values, after which the collection method is faster. I'll test this and post
back.

Dale
 
Marsh,

Bottom line is that for larger numbers of variables, storing and retrieving
the values in a collection is "much" faster than storing them in an array
(at least the way my functions are written - see below).

I wrote a function (fnMyVars2) to store and retrieve the values from an
array (I used a simple 1-to-n search of the array, since I didn't store the
keys in any particular order). If you have another idea on how to make this
routine more efficient, let me know.

Then I wrote a subroutine that would clear out all of the key values in both
functions (Collection and Array), generate a sequence of keys ("Test1" ->
"Test#") and values (double, date/time, or single character string values).
Finally, I randomly generated 500,000 keys and set a variable equal to the
value returned from each of the functions for that key (using the same
random number sequence for both techniques), and counted the number of
seconds between starting and ending each of these processes (I could have
gotten more accurate if I'd use Timer instead of Now, but didn't think about
it until just now). I ran this routine 10 times for each number of
variables, and generated the following table of variables and the ration of
seconds (Array/Collection). Keep in mind that that the maximum time to
retrieve 500,000 values from the collection was 2 seconds, and for the array
was only 8 seconds.

Variables Ratio (Array/Collection)
5 1.1
8 1.27
10 1.34
25 2.15
50 3.37
75 4.45
100 5.75


If you see any way to improve on either of these, I'd appreciate hearing
from you.

Public Function fnMyVars(key As String, _
Optional SomeValue As Variant =
Null, _
Optional Clear As Boolean = False)
As Variant

Static myVariables As New Collection
Dim intLoop As Integer

'Use in-line error processing to facilitate adding new items to the
collection
On Error Resume Next

'If SomeValue was passed, then remove the previous value and set the new
value
If Clear = True Then
For intLoop = myVariables.Count To 1 Step -1
myVariables.Remove (intLoop)
Next
ElseIf Not IsNull(SomeValue) Then
'if the key is not present, the next line will generate an error,
clear it and continue
myVariables.Remove key
myVariables.Add SomeValue, key
fnMyVars = SomeValue
Else
'Retrieve the value from the collection
fnMyVars = myVariables(key)
If IsEmpty(fnMyVars) Then fnMyVars = Null
End If

End Function

Public Function fnMyVars2(key As String, _
Optional SomeValue As Variant =
Null, _
Optional Clear As Boolean =
False) As Variant

Static myArray(100, 2) As Variant
Static aCount As Integer
Dim intLoop As Integer

If Clear = True Then
For intLoop = UBound(myArray) To LBound(myArray) Step -1
myArray(intLoop, 1) = Null
myArray(intLoop, 2) = Null
Next
aCount = 0
Exit Function
ElseIf Not IsNull(SomeValue) Then
aCount = aCount + 1
myArray(aCount, 1) = key
myArray(aCount, 2) = SomeValue
fnMyVars2 = SomeValue
Exit Function
Else
For intLoop = 1 To aCount
If myArray(intLoop, 1) = key Then
fnMyVars2 = myArray(intLoop, 2)
Exit Function
End If
Next
fnMyVars2 = Null
End If

End Function

Dale
 
Dale said:
Bottom line is that for larger numbers of variables, storing and retrieving
the values in a collection is "much" faster than storing them in an array
(at least the way my functions are written - see below).

I wrote a function (fnMyVars2) to store and retrieve the values from an
array (I used a simple 1-to-n search of the array, since I didn't store the
keys in any particular order). If you have another idea on how to make this
routine more efficient, let me know.

Then I wrote a subroutine that would clear out all of the key values in both
functions (Collection and Array), generate a sequence of keys ("Test1" ->
"Test#") and values (double, date/time, or single character string values).
Finally, I randomly generated 500,000 keys and set a variable equal to the
value returned from each of the functions for that key (using the same
random number sequence for both techniques), and counted the number of
seconds between starting and ending each of these processes (I could have
gotten more accurate if I'd use Timer instead of Now, but didn't think about
it until just now). I ran this routine 10 times for each number of
variables, and generated the following table of variables and the ration of
seconds (Array/Collection). Keep in mind that that the maximum time to
retrieve 500,000 values from the collection was 2 seconds, and for the array
was only 8 seconds.

Variables Ratio (Array/Collection)
5 1.1
8 1.27
10 1.34
25 2.15
50 3.37
75 4.45
100 5.75


If you see any way to improve on either of these, I'd appreciate hearing
from you.

Public Function fnMyVars(key As String, _
Optional SomeValue As Variant =
Null, _
Optional Clear As Boolean = False)
As Variant

Static myVariables As New Collection
Dim intLoop As Integer

'Use in-line error processing to facilitate adding new items to the
collection
On Error Resume Next

'If SomeValue was passed, then remove the previous value and set the new
value
If Clear = True Then
For intLoop = myVariables.Count To 1 Step -1
myVariables.Remove (intLoop)
Next
ElseIf Not IsNull(SomeValue) Then
'if the key is not present, the next line will generate an error,
clear it and continue
myVariables.Remove key
myVariables.Add SomeValue, key
fnMyVars = SomeValue
Else
'Retrieve the value from the collection
fnMyVars = myVariables(key)
If IsEmpty(fnMyVars) Then fnMyVars = Null
End If

End Function

Public Function fnMyVars2(key As String, _
Optional SomeValue As Variant =
Null, _
Optional Clear As Boolean =
False) As Variant

Static myArray(100, 2) As Variant
Static aCount As Integer
Dim intLoop As Integer

If Clear = True Then
For intLoop = UBound(myArray) To LBound(myArray) Step -1
myArray(intLoop, 1) = Null
myArray(intLoop, 2) = Null
Next
aCount = 0
Exit Function
ElseIf Not IsNull(SomeValue) Then
aCount = aCount + 1
myArray(aCount, 1) = key
myArray(aCount, 2) = SomeValue
fnMyVars2 = SomeValue
Exit Function
Else
For intLoop = 1 To aCount
If myArray(intLoop, 1) = key Then
fnMyVars2 = myArray(intLoop, 2)
Exit Function
End If
Next
fnMyVars2 = Null
End If

End Function
Click to expand...


In general, I think the times to set/retrieve a value are so
small that it won't matter in most situations so you should
use the most versatile, which looks like the collection.

If you are going to have a LOT more than 100 entries OR you
will be looking up values from queries on large tables, then
you may want to explore faster ways of searching an array.
Check a good library for Donald Knuth's Vol I for in depth
analysis of techniques such as presorted (very effective for
semi static medium to large lists), scatter/hash (fastest
for lists where you never remove an entry), or maybe even
btrees. While all this may be an interesting and
illuminating exercise, except in rather special situations,
you may gain very little from a large investment in time.

(If you are not familiar with it, look up the Erase
statement in VBA Hep)
 
You're right. The times are so small that it isn't really an issue,
especially since I normally only store 6-10 variables this way. But now that
I realize that this is so much quicker than resorting to DLOOKUP, I might
start using this method for storing smaller sets of information as well.

I also considered trying this using a static recordset, what do you think?

No, I'm not familiar with the Erase command. I'll take a look.

Thanks
 
Dale said:
You're right. The times are so small that it isn't really an issue,
especially since I normally only store 6-10 variables this way. But now that
I realize that this is so much quicker than resorting to DLOOKUP, I might
start using this method for storing smaller sets of information as well.

I also considered trying this using a static recordset, what do you think?
Click to expand...


I may very well be wrong, but I doubt that FindFirst on a
static recorset would be faster than a collection (try it
and see). However, it may be fast enough that the
simplicity makes it worthwhile (especially as an alternatice
to DLookup). This may very well be a preferable approach if
you need to save the values across multiple session.
 
Back
Top