Dynamic Variable Use

vonryan

New member
Joined
Feb 4, 2021
Messages
10
Reaction score
0
Points
0
Excel Version(s)
365
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 a moderator:
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
 
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
 
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))
 
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.
 
Back
Top