Student7
11-07-2015, 06:29 PM
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.
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.