Loop through variable names and set values using for loop

Question

I have inherited someone's bad code to try and fix, in this code there are 15 entries per day and are names as follows:

Mon01, Mon02, Mon03, Mon04, etc

I then have a grid with the same number of entries, I need to check if the value of Mon01 matches the first entry in the grid, if not then update its value. But as I said there are 7 grids so 7*15, so instead of having 105 lines of code i'd rather have 4.

My question is, is there a way to loop through and change the values of these variables. An example idea is:

for (i = 0; i < 15; i++)
    variable = "Mon" + i
    If (variable.value <> Grid.TextArray(i) Then 
        variable.value = Grid.TextArray(i) 
    End If
End For

I know the above isn't valid VB6 code, but its trying to get the message across best way I can.

My current revised code looks like this:

DB.Recordsource = "QUERY REMOVED"
Call DB.GetData(RS)

With RS
    Dim ChangeArray() As String

    If Trim(MonGrid.TextArray((1 * MonGrid.Cols) + 2)) <> ![Mon01] Then
        ![Mon01] = MonGrid.TextArray((1 * MonGrid.Cols) + 2)
        ReDim Preserve ChangeArray(0 To UBound(ChangeArray) + 1) As String
    End If

Mark Bertenshaw · Accepted Answer · 2014-04-30 12:35:06Z

1

You have my condolences of having to maintain such a horrible codebase.

The answer is that there is no way of doing this in Visual Basic. However, assuming that all MonXX variables are of the same type, you could declare Mon as an array, and then do a search and replace of Mon01, Mon02, etc. with Mon(01), Mon(02). You could probably use a decent editor that has regular expressions to match on Mon(\d\d) and replace it with Mon\1 - obviously, depending on how the regex facility works.

answered Apr 30, 2014 at 12:35

Mark Bertenshaw

5,7093 gold badges30 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

lardymonkey · Accepted Answer · 2014-05-01 12:23:46Z

You could expose the variables as properties and use the CallByName function, that might help you. The msdn info is here CallByName

it would look something like this:

Option Explicit
Dim m_Mon01 As String
Dim m_Mon02 As String
Dim m_Mon03 As String

Public Property Let Mon01(ByVal strID As String)
    m_Mon01 = strID
End Property

Public Property Let Mon02(ByVal strID As String)
    m_Mon02 = strID
End Property

Public Property Let Mon03(ByVal strID As String)
    m_Mon03 = strID
End Property

Private Sub Command_Click()
    Dim lngIndex  As Long
    Dim strName   As String

    For lngIndex = 1 To 3
        strName = "Mon" + Format$(lngIndex, "00")
        CallByName Me, strName, VbLet, "The value form the grid"
    Next lngIndex
End Sub

Collectives™ on Stack Overflow

Loop through variable names and set values using for loop

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related