Results 1 to 5 of 5

Thread: Dynamic Variable Use

  1. #1
    Seeker vonryan's Avatar
    Join Date
    Feb 2021
    Posts
    10
    Articles
    0
    Excel Version
    365

    Dynamic Variable Use



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Dear All,

    I have a large number of variables that I need to indirectly reference in a series of loops.

    For example if the loop count is from 1 to 100 then for each loop count the variable name has to change.

    I have an example of the variable names below:

    Code:
    Dim PTSACECriticalCount As Integer
    Dim PTSASERCriticalCount As Integer
    Dim SCOLCECriticalCount As Integer
    Dim SCOLSERCriticalCount As Integer
    Dim SEVTCECriticalCount As Integer
    Dim SEVTSERCriticalCount As Integer
    Dim SHVACECriticalCount As Integer
    Dim SMSACECriticalCount As Integer
    Dim SMSASERCriticalCount As Integer
    Dim SOVACECriticalCount As Integer
    Dim SOVASERCriticalCount As Integer
    Dim SEATCriticalCount As Integer
    Dim PCONCriticalCount As Integer
    Dim PCOECriticalCount As Integer
    Each one of these variables acts as a counter depending on some conditions.

    I have declared the following:

    Code:
    Dim StationNameArray(1, 100) As Variant
    Dim LocationName As Object
    Set LocationName = CreateObject("Scripting.Dictionary")
    Dim LocationTagName As String
    I have populated the the LocationName object with strings of the variable names as defined in a static array.

    Code:
    StationNameArray(1, 1) = "PTSACECriticalCount"
    StationNameArray(1, 2) = "PTSASERCriticalCount"
    StationNameArray(1, 3) = "SCOLCECriticalCount"
    StationNameArray(1, 4) = "SCOLSERCriticalCount"
    StationNameArray(1, 5) = "SEVTCECriticalCount"
    StationNameArray(1, 6) = "SEVTSERCriticalCount"
    StationNameArray(1, 7) = "SHVACECriticalCount"
    StationNameArray(1, 8) = "SMSACECriticalCount"
    StationNameArray(1, 9) = "SMSASERCriticalCount"
    StationNameArray(1, 10) = "SOVACECriticalCount"
    StationNameArray(1, 11) = "SOVASERCriticalCount"
    StationNameArray(1, 12) = "SEATCriticalCount"
    StationNameArray(1, 13) = "PCONCriticalCount"
    StationNameArray(1, 14) = "PCOECriticalCount"
    
    Dim I As Integer
    Dim ArrayIndex As Integer
    For I = 1 To 54
        LocationName(I) = StationNameArray(1, I)
    Next I
    I start my loop

    Code:
    'Set Column First Row value
    ColumnFirstRow = 2
    'Set Column Last Row value
    ColumnLastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "R").End(-4162).Row
    'COUNT THE NUMBER OF ASSIGNED OPEN DEFECTS OF CRITICAL CATAGORY
    'Set the Search String values to below
    SearchString1 = "Critical"
    SearchString2 = "CE"
    
    
    'Start Loop for the entire column
    For ColumnRowCount = ColumnFirstRow To ColumnLastRow
    'Set the search string to the value in Labels Column
    MainString = ActiveSheet.Range("R" & ColumnRowCount)
    ' If the search function returns TRUE
    TRUE1 = Contains(MainString, SearchString1)
    TRUE2 = Contains(MainString, SearchString2)
    If TRUE1 And TRUE2 Then
        'AND If the STATUS column value is OPEN
        If ActiveSheet.Range("D" & ColumnRowCount) = "Open" Then
            MainString = ActiveSheet.Range("R" & ColumnRowCount)
            If Contains(MainString, SearchString) Then
            'Then add 1 to the Critical Count
            LocationName(ArrayIndex) = LocationName(ArrayIndex) + 1
            End If
        'End IF
        End If
    'End If
    End If
    'Next Row in the LABELS column
    Next ColumnRowCount
    
    
    Next LocationCount


    My issue is that the code fails at this step

    LocationName(ArrayIndex) = LocationName(ArrayIndex) + 1

    The referenced variable is defined as an Integer.

    In this example the statement should be equivalent to

    PTSACECriticalCount = PTSACECriticalCount + 1

    Please can someone tell me what I am doing wrong with dynamically reference a variable.

    Best Regards

    Vonryan
    Last edited by Bob Phillips; 2021-06-18 at 02:14 PM. Reason: Added code tags

  2. #2
    Seeker RET's Avatar
    Join Date
    Nov 2020
    Location
    Spain
    Posts
    18
    Articles
    0
    Excel Version
    2019
    Hi @vonryan,

    I can see where you declare ArrayIndex, but not where you set its value

    Dim I As Integer
    Dim ArrayIndex As Integer
    For I = 1 To 54
    LocationName(I) = StationNameArray(1, I)
    Next I

  3. #3
    Seeker vonryan's Avatar
    Join Date
    Feb 2021
    Posts
    10
    Articles
    0
    Excel Version
    365
    Dear Ret,

    I declared them at the top of the code such as follows:

    StationNameArray(1, 1) = "PTSACECriticalCount"
    StationNameArray(1, 2) = "PTSASERCriticalCount"
    StationNameArray(1, 3) = "SCOLCECriticalCount"
    StationNameArray(1, 4) = "SCOLSERCriticalCount"
    StationNameArray(1, 5) = "SEVTCECriticalCount"
    StationNameArray(1, 6) = "SEVTSERCriticalCount"
    StationNameArray(1, 7) = "SHVACECriticalCount"
    StationNameArray(1, 8) = "SMSACECriticalCount"
    StationNameArray(1, 9) = "SMSASERCriticalCount"
    StationNameArray(1, 10) = "SOVACECriticalCount"
    StationNameArray(1, 11) = "SOVASERCriticalCount"
    StationNameArray(1, 12) = "SEATCriticalCount"
    StationNameArray(1, 13) = "PCONCriticalCount"
    StationNameArray(1, 14) = "PCOECriticalCount"
    Best Regards

    Vonryan

  4. #4
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,854
    Articles
    0
    Excel Version
    O365
    There is so much that is odd about that code, it is nigh on impossible to work it through.

    You have:
    - undeclared variables (TRUE1, TRUE2, LocationTagName, SearchString1, SearchString2, SearchString, MainString, ColumnFirstRow , ColumnLastRow , ColumnRowCount, LocationCount)
    - a possible function that you don't show (Contains)
    - a Next without For (LocationCount)
    - no initialisation of SearchString
    - you haven't told RET where ArrayIndex is initialised
    - you use a constant value rather than the constant (End(-4162) as against End(xlUp))

  5. #5
    Seeker vonryan's Avatar
    Join Date
    Feb 2021
    Posts
    10
    Articles
    0
    Excel Version
    365
    Dear Bob,

    Thank you for your observations. I apologize for the lack of information, but I am quite new to this type of forum.

    I fully appreciate everyone's efforts in this manner. I have however completed the task I needed to do in a different manner. without using indirect addressing of variables. It is a bit long winded but it does the task.

    Once again, my apologize for the lack of details. I will be more observant in the future.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •