How can I evaluate an equation within a String?

Excel VBA-Evaluate Text String Value as referenced Variable

• In Excel VBA how do you evaluate a string text as a variable? Basically, the reason I need to do this to reference virtual string values. This is a very simplified example of what I am tring to do (In case you are thinking why not just use an array). In this example how do I render the value of DYNAMIC_STRING2 as the value of the corresponding Var1,Var2,Var3? The entire reason I need this is to reduce redundant code. When I run my code, DYANAMIC_STRING2 message box output is String text value:int_Var1,int_Var2,int_Var3 when the desired output is: 1,5,8 in the message box. Example Sub DynamicValues() Dim int_Var1 As Integer 'ANY OLD VALUE Dim int_Var2 As Integer 'ANY OLD VALUE Dim int_Var3 As Integer 'ANY OLD VALUE int_Var1 = 1 int_Var2 = 5 int_Var3 = 8 For x = 1 To 3 'LOOP DYNAMIC_STRING = "int_Var" & x 'COMBINE DYNAMIC_STRING2 = DYNAMIC_STRING MsgBox DYNAMIC_STRING2 Next x End Sub

• Answer:

  slo2000, The routine below demonstrates how to use a Type statement to create an array of arrays in VBA. It should do what your example code seems to require. Please let me know if you need anything explained. NOTES: 1. Code is not repetitive, as requested. 2. Logic reworked slightly to remove the use of GoTo. 3. Option Base 1 specified. 4. Code includes a marked debug section at the end to display results. 5. Code reworked to not require the use of any external form elements or routines not included. Code is self contained and can be run from any worksheet. ' CODE BEGIN Option Base 1 Private Type T_small Els() As String End Type Sub VinOptimize() 'USE-OPTIMIZE SQL TO LEAST COMMON DENOMINATOR 'DEC VARS Dim MArray(10) As T_small Dim Bump(10) As Integer Dim vList(5) As String Dim x As Integer Dim xx As Integer Dim flagbit As Integer Dim mindexval As Integer 'MArray Indexer Dim indexval As Integer 'VARRAY INDEXER Dim LOOKUPVAL As String 'SET VALS vArray1 = Array(1, 3, 4, 6, 7, 8, 9, 10, 11, 12) 'ARRAY VIN POSITION vArray2 = Array(2, 1, 2, 1, 1, 1, 1, 1, 1, 6) 'ARRAY VIN NUM CHARS ' Initialize Bump For x = 1 To 10 Bump(x) = 1 Next x ' Initialize list of sample values vList(1) = "1HGCM71626A007999" vList(2) = "1HJCM71426A008027" vList(3) = "1JGCM71526A004965" vList(4) = "1HGCM71026A007099" vList(5) = "1HGCM71326A002009" 'MESS USER MsgBox "SQL Optimization in Process" 'ARRAY1 For x = 1 To 5 ckval = vList(x) 'LOAD FIRST VIN For indexval = 1 To 10 LOOKUPVAL = Mid(ckval, vArray1(indexval), vArray2(indexval)) flagbit = 0 If Bump(indexval) > 1 Then '-------TEST FOR MATCH----------------- For xx = 1 To Bump(indexval) - 1 val1 = MArray(indexval).Els(xx) If val1 = LOOKUPVAL Then flagbit = 1 Exit For End If Next xx End If '-------EVALUATE FLAGBIT------ If flagbit = 0 Then ReDim Preserve MArray(indexval).Els(Bump(indexval)) MArray(indexval).Els(Bump(indexval)) = LOOKUPVAL Bump(indexval) = Bump(indexval) + 1 End If Next indexval Next x ' DEBUG PRINT RESULTS For x = 1 To 10 Debug.Print "----- MArray: " & x & " -----" Debug.Print "----- Elements: " & UBound(MArray(x).Els) & " -----" For xx = 1 To UBound(MArray(x).Els) Debug.Print xx & ": " & MArray(x).Els(xx) Next xx Next x End Sub ' CODE END With sample values: 1HGCM71626A007999 1HJCM71426A008027 1JGCM71526A004965 1HGCM71026A007099 1HGCM71326A002009 I produced a result of: ----- MArray: 1 ----- ----- Elements: 2 ----- 1: 1H 2: 1J ----- MArray: 2 ----- ----- Elements: 2 ----- 1: G 2: J ----- MArray: 3 ----- ----- Elements: 1 ----- 1: CM ----- MArray: 4 ----- ----- Elements: 1 ----- 1: 7 ----- MArray: 5 ----- ----- Elements: 1 ----- 1: 1 ----- MArray: 6 ----- ----- Elements: 5 ----- 1: 6 2: 4 3: 5 4: 0 5: 3 ----- MArray: 7 ----- ----- Elements: 1 ----- 1: 2 ----- MArray: 8 ----- ----- Elements: 1 ----- 1: 6 ----- MArray: 9 ----- ----- Elements: 1 ----- 1: A ----- MArray: 10 ----- ----- Elements: 5 ----- 1: 007999 2: 008027 3: 004965 4: 007099 5: 002009 Again, please let me know if you need further explanation of how this works. Good luck with your Excel project! - Hammer Search strategy: VBA "array of arrays"