Efficiently searching text array?

[Student7]

I'm saving VBA variables to an external text file in the format variableName(delimiter)variableValue. When my VBA form initializes, it reads the text file into a two-dimensional array. There are functions that search the array for variableNames and return their variableValues. Also have function to update values.

I'm thinking that the way I came up with to store and search for these values is not the most efficient and could be slow if there are a large number of variables or frequent searches.

Is there a more efficient way to store/search for these variableNames and return the variableValue?



Private Function GetArrayIndexOf(strVariableName As String) As Integer
'Returns the located array position given a variable name or returns -1 if not found

For x = LBound(arrVariables, 2) To UBound(arrVariables, 2)
If strVariableName = arrVariables(0, x) Then
GetArrayIndexOf = x
Exit Function
End If
Next x

'Search string not found in array
GetArrayIndexOf = -1
End Function


Public Function GetVariable(strVariableName As String) As String
'Returns value of a variable given its variable name or returns and empty string if not found

intArrayPosition = GetArrayIndexOf(strVariableName)

If intArrayPosition >= 0 Then
GetVariable = arrVariables(1, intArrayPosition)
Else
GetVariable = ""
End If
End Function


Public Sub UpdateVariable(strVariableName As String, strNewValue As String)
'updates a variable value in the array, given the variable name and new value

intArrayPosition = GetArrayIndexOf(strVariableName)

If intArrayPosition >= 0 Then
arrVariables(1, intArrayPosition) = strNewValue
End Sub


Thank you.

[mikerickson]

These functions don't loop.

MyArray is a mock-up of the array extracted from the txt file


Sub test()
Dim i As Long
Dim myArray(0 To 1, 0 To 10)
For i = 0 To 10
myArray(0, i) = "name" & i
myArray(1, i) = "value" & i
Next i

MsgBox GetValueFromName("name3", myArray)

SetValueOfName "name3", myArray, 333
MsgBox GetValueFromName("name3", myArray)
End Sub

Function GetValueFromName(variableName As String, arrVariables As Variant) As Variant
Dim variableIndex As Variant

variableIndex = Application.Match(variableName, Application.Index(arrVariables, 1, 0), 0)

If IsNumeric(variableIndex) Then
GetValueFromName = arrVariables(1, variableIndex + (LBound(arrVariables, 2) - 1))
Else
GetValueFromName = CVErr(xlErrNA)
End If
End Function

Function SetValueOfName(variableName As String, ByRef arrVariables, newValue As Variant) As Boolean
Dim variableIndex As Variant

variableIndex = Application.Match(variableName, Application.Index(arrVariables, 1, 0), 0)

If IsNumeric(variableIndex) Then
arrVariables(1, variableIndex + (LBound(arrVariables, 2) - 1)) = newValue
SetValueOfName = True
Else
SetValueOfName = False
End If
End Function

[snb]

Why don't you store these data in the Userform Initialize event ?

[mikerickson]

I agree, a two-column list box seems ideal.

Another option would be to put the values from the txt file in a collection

'Loop through txt file entries

myCollection.Add item:= theValue item:= theName

'end loop

and you could myCollection("variableName") to reference the value.

[Student7]

Thanks Mike for considering my question and providing multiple solutions - all answers avoid the looping that I was concerned about. I knew there had to be a better way to do it when I was stepping through in debug mode and saw how many statements it took to extract one little variable! I'd never worked with creating my own collections before and decided to go that route after learning how they work. A collection seemed perfect since what I'm doing is already essentially a key/value pair.

I understand the same thing could be done with a two column list box but the syntax for collections is more straightforward at my experience level. Not sure what the advantage of the list box is over collections but it does sound like there is something...

I can see how leveraging the Match function could also be useful if one had the requirement of needing to stay with using arrays or had array with more than 2 dimensions. Thanks again!