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

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"

---

Clarification of Answer by hammer-ga on 31 Oct 2006 13:43 PST

Slo2000,

Thanks for the tip and the nice rating. To answer your questions:

[[[ 1)  Why did you go with option base 1? ]]]

Because some of your code was counting from 1 and some was counting
from 0. I picked one and had everything use that method. VBA arrays
default to Base 1. If this was C, I'd have used 0.

[[[ 2)  I consider myself a half decent programmer, but have trouble
comprehending logic of how
Private Type T_small
    Els() As String
End Type
works. ]]]

It declares an object type called T_small with a single property
called Els, which is an array of type string. You could put more
properties in the type declaration, if you wanted. It's a trick that
allows VBA to use an array of arrays, which it can't do without you
being a bit sneaky.

A simple example of User Defined Types
Daily Dose of Excel Blog Archive
http://www.dicks-blog.com/archives/2004/07/15/user-defined-types/

Please let me know if you have any other questions.

- Hammer

Great answer.  Code works great.  I would love to understand 2 things.
1)  Why did you go with option base 1?
2)  I consider myself a half decent programmer, but have trouble
comprehending logic of how
Private Type T_small
    Els() As String
End Type
works.

slo2000,

Respectfully, if this is your only option for reducing redundant code,
you may want to take a step back and rework the code. Normally, a
function or subroutine that can accept arguments is the key to
reducing redundant code. Code in the condition you describe generally
has some design issues that it is better to address sooner than later.

- Hammer

Point Taken, final result will be a function.  Right now it is reading
values from a listbox.

OK, what I am really trying to do is build 10 dynamic Arrays.  Bumping
the referenced Array to the next using the same code block.  Code is
used to build a simplified SQL statement of passed values eliminating
redundant values.  Really didn't want to go multidimensional here
because of limitation that only the last element can be resized with
redim.  Secondly in the below example, individual array elements whill
have varying UBounds due to different numbers of elements.

Sample Value to be Parsed: 1HGCM71626A007999


Sub VinOptimize()
'USE-OPTIMIZE SQL TO LEAST COMMON DENOMINATOR
    
    'DEC VARS
    Dim MArray1() As String
    Dim MArray2() As String
    Dim MArray3() As String
    Dim MArray4() As String
    Dim MArray5() As String
    Dim MArray6() As String
    Dim MArray7() As String
    Dim MArray8() As String
    Dim MArray9() As String
    Dim MArray10() As String
    
    Dim Bump1 As Integer
    Dim Bump2 As Integer
    Dim Bump3 As Integer
    Dim Bump4 As Integer
    Dim Bump5 As Integer
    Dim Bump6 As Integer
    Dim Bump7 As Integer
    Dim Bump8 As Integer
    Dim Bump9 As Integer
    Dim Bump10 As Integer
    Dim Jump1 As Label
    Dim Bypass1 As Label
    Dim indexval As Integer 'VARRAY INDEXER
       
    '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
    Bump1 = 0
    Bump2 = 0
    Bump3 = 0
    Bump4 = 0
    Bump5 = 0
    Bump6 = 0
    Bump7 = 0
    Bump8 = 0
    Bump9 = 0
    Bump10 = 0
  
    'MESS USER
    Call UPMess("SQL Optimization in Process")

   'ARRAY1
    For x = 0 To frm_viperpro.lstVIN.ListCount - 1
        ckval = frm_viperpro.lstVIN.List(x)    'LOAD FIRST VIN
          indexval = 0
'*********************************************************************************************************
'************************************ PROCESS 1
**********************************************************
'*********************************************************************************************************
        LOOKUPVAL = Mid(ckval, VArray1(indexval), VArray2(indexval))  
 'LOAD INDIVIDUAL VALUES
        '---------------------------------------------------------
        
        If Bump1 = 0 Then
            GoTo Jump1
        Else
            '--------------------------------------------------
            '-------TEST FOR MATCH-----------------
           
            flagbit = 0
            For xx = 0 To Bump1 - 1
                val1 = MArray1(xx)
                If val1 = LOOKUPVAL Then
                    flagbit = 1
                    Exit For
                End If
            Next xx

            '-------EVALUATE FLAGBIT------
            If flagbit = 0 Then
                ReDim Preserve MArray1(Bump1)
                MArray1(Bump1) = LOOKUPVAL
                Bump1 = Bump1 + 1
            Else
                'DO NOTHING
            End If
            GoTo Bypass1
            '-----------------------------

            '-----------------------------
Jump1:
            ReDim Preserve MArray1(Bump1)
            MArray1(Bump1) = LOOKUPVAL
            Bump1 = Bump1 + 1
            '-----------------------------
            '--------------------------------------------------
        End If
        '---------------------------------------------------------
Bypass1:

'*********************************************************************************************************
'************************************ END PROCESS 1
******************************************************
'*********************************************************************************************************
indexval = indexval + 1
'*********************************************************************************************************
'************************************ PROCESS 2
**********************************************************
'*********************************************************************************************************
               LOOKUPVAL = Mid(ckval, VArray1(indexval),
VArray2(indexval))    'LOAD INDIVIDUAL VALUES
        '---------------------------------------------------------
        
        If Bump2 = 0 Then
            GoTo Jump2
        Else
            '--------------------------------------------------
            '-------TEST FOR MATCH-----------------
           
            flagbit = 0
            For xx = 0 To Bump2 - 1
                val1 = MArray2(xx)
                If val1 = LOOKUPVAL Then
                    flagbit = 1
                    Exit For
                End If
            Next xx

            '-------EVALUATE FLAGBIT------
            If flagbit = 0 Then
                ReDim Preserve MArray2(Bump2)
                MArray2(Bump2) = LOOKUPVAL
                Bump2 = Bump2 + 1
            Else
                'DO NOTHING
            End If
            GoTo Bypass2
            '-----------------------------

            '-----------------------------
Jump2:
            ReDim Preserve MArray2(Bump2)
            MArray2(Bump2) = LOOKUPVAL
            Bump2 = Bump2 + 1
            '-----------------------------
            '--------------------------------------------------
        End If
        '---------------------------------------------------------
Bypass2:


'*********************************************************************************************************
'************************************ END PROCESS 2
******************************************************
'*********************************************************************************************************
indexval = indexval + 1
'*********************************************************************************************************
'************************************ PROCESS 3
**********************************************************
'*********************************************************************************************************
               LOOKUPVAL = Mid(ckval, VArray1(indexval),
VArray2(indexval))    'LOAD INDIVIDUAL VALUES
        '---------------------------------------------------------
        
        If Bump3 = 0 Then
            GoTo Jump3
        Else
            '--------------------------------------------------
            '-------TEST FOR MATCH-----------------
           
            flagbit = 0
            For xx = 0 To Bump3 - 1
                val1 = MArray3(xx)
                If val1 = LOOKUPVAL Then
                    flagbit = 1
                    Exit For
                End If
            Next xx

            '-------EVALUATE FLAGBIT------
            If flagbit = 0 Then
                ReDim Preserve MArray3(Bump3)
                MArray3(Bump3) = LOOKUPVAL
                Bump3 = Bump3 + 1
            Else
                'DO NOTHING
            End If
            GoTo Bypass3
            '-----------------------------

            '-----------------------------
Jump3:
            ReDim Preserve MArray3(Bump3)
            MArray3(Bump3) = LOOKUPVAL
            Bump3 = Bump3 + 1
            '-----------------------------
            '--------------------------------------------------
        End If
        '---------------------------------------------------------
Bypass3:


'*********************************************************************************************************
'************************************ END PROCESS 3
******************************************************
'*********************************************************************************************************
indexval = indexval + 1
'*********************************************************************************************************
'************************************ PROCESS 4
**********************************************************
'*********************************************************************************************************
                LOOKUPVAL = Mid(ckval, VArray1(indexval),
VArray2(indexval))    'LOAD INDIVIDUAL VALUES
        '---------------------------------------------------------
        
        If Bump4 = 0 Then
            GoTo Jump4
        Else
            '--------------------------------------------------
            '-------TEST FOR MATCH-----------------
           
            flagbit = 0
            For xx = 0 To Bump4 - 1
                val1 = MArray4(xx)
                If val1 = LOOKUPVAL Then
                    flagbit = 1
                    Exit For
                End If
            Next xx

            '-------EVALUATE FLAGBIT------
            If flagbit = 0 Then
                ReDim Preserve MArray4(Bump4)
                MArray4(Bump4) = LOOKUPVAL
                Bump4 = Bump4 + 1
            Else
                'DO NOTHING
            End If
            GoTo Bypass4
            '-----------------------------

            '-----------------------------
Jump4:
            ReDim Preserve MArray4(Bump4)
            MArray4(Bump4) = LOOKUPVAL
            Bump4 = Bump4 + 1
            '-----------------------------
            '--------------------------------------------------
        End If
        '---------------------------------------------------------
Bypass4:

'*********************************************************************************************************
'************************************ END PROCESS 4
******************************************************
'*********************************************************************************************************
indexval = indexval + 1
'*********************************************************************************************************
'************************************ PROCESS 5
**********************************************************
'*********************************************************************************************************
               LOOKUPVAL = Mid(ckval, VArray1(indexval),
VArray2(indexval))    'LOAD INDIVIDUAL VALUES
        '---------------------------------------------------------
        
        If Bump5 = 0 Then
            GoTo Jump5
        Else
            '--------------------------------------------------
            '-------TEST FOR MATCH-----------------
           
            flagbit = 0
            For xx = 0 To Bump5 - 1
                val1 = MArray5(xx)
                If val1 = LOOKUPVAL Then
                    flagbit = 1
                    Exit For
                End If
            Next xx

            '-------EVALUATE FLAGBIT------
            If flagbit = 0 Then
                ReDim Preserve MArray5(Bump5)
                MArray5(Bump5) = LOOKUPVAL
                Bump5 = Bump5 + 1
            Else
                'DO NOTHING
            End If
            GoTo Bypass5
            '-----------------------------

            '-----------------------------
Jump5:
            ReDim Preserve MArray5(Bump5)
            MArray5(Bump5) = LOOKUPVAL
            Bump5 = Bump5 + 1
            '-----------------------------
            '--------------------------------------------------
        End If
        '---------------------------------------------------------
Bypass5:

'*********************************************************************************************************
'************************************ END PROCESS 5
******************************************************
'*********************************************************************************************************
indexval = indexval + 1
'*********************************************************************************************************
'************************************ PROCESS 6
**********************************************************
'*********************************************************************************************************
               LOOKUPVAL = Mid(ckval, VArray1(indexval),
VArray2(indexval))    'LOAD INDIVIDUAL VALUES
        '---------------------------------------------------------
        
        If Bump6 = 0 Then
            GoTo Jump6
        Else
            '--------------------------------------------------
            '-------TEST FOR MATCH-----------------
           
            flagbit = 0
            For xx = 0 To Bump6 - 1
                val1 = MArray6(xx)
                If val1 = LOOKUPVAL Then
                    flagbit = 1
                    Exit For
                End If
            Next xx

            '-------EVALUATE FLAGBIT------
            If flagbit = 0 Then
                ReDim Preserve MArray6(Bump6)
                MArray6(Bump6) = LOOKUPVAL
                Bump6 = Bump6 + 1
            Else
                'DO NOTHING
            End If
            GoTo Bypass6
            '-----------------------------

            '-----------------------------
Jump6:
            ReDim Preserve MArray6(Bump6)
            MArray6(Bump6) = LOOKUPVAL
            Bump6 = Bump6 + 1
            '-----------------------------
            '--------------------------------------------------
        End If
        '---------------------------------------------------------
Bypass6:

'*********************************************************************************************************
'************************************ END PROCESS 6
******************************************************
'*********************************************************************************************************
indexval = indexval + 1
'*********************************************************************************************************
'************************************ PROCESS 7
**********************************************************
'*********************************************************************************************************
         LOOKUPVAL = Mid(ckval, VArray1(indexval), VArray2(indexval)) 
  'LOAD INDIVIDUAL VALUES
        '---------------------------------------------------------
        
        If Bump7 = 0 Then
            GoTo Jump7
        Else
            '--------------------------------------------------
            '-------TEST FOR MATCH-----------------
           
            flagbit = 0
            For xx = 0 To Bump7 - 1
                val1 = MArray7(xx)
                If val1 = LOOKUPVAL Then
                    flagbit = 1
                    Exit For
                End If
            Next xx

            '-------EVALUATE FLAGBIT------
            If flagbit = 0 Then
                ReDim Preserve MArray7(Bump7)
                MArray7(Bump7) = LOOKUPVAL
                Bump7 = Bump7 + 1
            Else
                'DO NOTHING
            End If
            GoTo Bypass7
            '-----------------------------

            '-----------------------------
Jump7:
            ReDim Preserve MArray7(Bump7)
            MArray7(Bump7) = LOOKUPVAL
            Bump7 = Bump7 + 1
            '-----------------------------
            '--------------------------------------------------
        End If
        '---------------------------------------------------------
Bypass7:


'*********************************************************************************************************
'************************************ END PROCESS 7
******************************************************
'*********************************************************************************************************
indexval = indexval + 1
'*********************************************************************************************************
'************************************ PROCESS 8
**********************************************************
'*********************************************************************************************************
               LOOKUPVAL = Mid(ckval, VArray1(indexval),
VArray2(indexval))    'LOAD INDIVIDUAL VALUES
        '---------------------------------------------------------
        
        If Bump8 = 0 Then
            GoTo Jump8
        Else
            '--------------------------------------------------
            '-------TEST FOR MATCH-----------------
           
            flagbit = 0
            For xx = 0 To Bump8 - 1
                val1 = MArray8(xx)
                If val1 = LOOKUPVAL Then
                    flagbit = 1
                    Exit For
                End If
            Next xx

            '-------EVALUATE FLAGBIT------
            If flagbit = 0 Then
                ReDim Preserve MArray8(Bump8)
                MArray8(Bump8) = LOOKUPVAL
                Bump8 = Bump8 + 1
            Else
                'DO NOTHING
            End If
            GoTo Bypass8
            '-----------------------------

            '-----------------------------
Jump8:
            ReDim Preserve MArray8(Bump8)
            MArray8(Bump8) = LOOKUPVAL
            Bump8 = Bump8 + 1
            '-----------------------------
            '--------------------------------------------------
        End If
        '---------------------------------------------------------
Bypass8:


'*********************************************************************************************************
'************************************ END PROCESS 8
******************************************************
'*********************************************************************************************************
indexval = indexval + 1
'*********************************************************************************************************
'************************************ PROCESS 9
**********************************************************
'*********************************************************************************************************
             LOOKUPVAL = Mid(ckval, VArray1(indexval),
VArray2(indexval))    'LOAD INDIVIDUAL VALUES
        '---------------------------------------------------------
        
        If Bump9 = 0 Then
            GoTo Jump9
        Else
            '--------------------------------------------------
            '-------TEST FOR MATCH-----------------
           
            flagbit = 0
            For xx = 0 To Bump9 - 1
                val1 = MArray9(xx)
                If val1 = LOOKUPVAL Then
                    flagbit = 1
                    Exit For
                End If
            Next xx

            '-------EVALUATE FLAGBIT------
            If flagbit = 0 Then
                ReDim Preserve MArray9(Bump9)
                MArray9(Bump9) = LOOKUPVAL
                Bump9 = Bump9 + 1
            Else
                'DO NOTHING
            End If
            GoTo Bypass9
            '-----------------------------

            '-----------------------------
Jump9:
            ReDim Preserve MArray9(Bump9)
            MArray9(Bump9) = LOOKUPVAL
            Bump9 = Bump9 + 1
            '-----------------------------
            '--------------------------------------------------
        End If
        '---------------------------------------------------------
Bypass9:


'*********************************************************************************************************
'************************************ END PROCESS 9
******************************************************
'*********************************************************************************************************
indexval = indexval + 1
'*********************************************************************************************************
'************************************ PROCESS 10
**********************************************************
'*********************************************************************************************************
               LOOKUPVAL = Mid(ckval, VArray1(indexval),
VArray2(indexval))    'LOAD INDIVIDUAL VALUES
        '---------------------------------------------------------
        
        If Bump10 = 0 Then
            GoTo Jump10
        Else
            '--------------------------------------------------
            '-------TEST FOR MATCH-----------------
           
            flagbit = 0
            For xx = 0 To Bump10 - 1
                val1 = MArray10(xx)
                If val1 = LOOKUPVAL Then
                    flagbit = 1
                    Exit For
                End If
            Next xx

            '-------EVALUATE FLAGBIT------
            If flagbit = 0 Then
                ReDim Preserve MArray10(Bump10)
                MArray10(Bump10) = LOOKUPVAL
                Bump10 = Bump10 + 1
            Else
                'DO NOTHING
            End If
            GoTo Bypass10
            '-----------------------------

            '-----------------------------
Jump10:
            ReDim Preserve MArray10(Bump10)
            MArray10(Bump10) = LOOKUPVAL
            Bump10 = Bump10 + 1
            '-----------------------------
            '--------------------------------------------------
        End If
        '---------------------------------------------------------
Bypass10:

'*********************************************************************************************************
'************************************ END PROCESS 10
******************************************************
'*********************************************************************************************************
    Next x
'FINAL RESULT-ARRAYS CONTAIN LOWEST DENOMINATER OF PASSED VALUE

End Sub