Variable Name Changes in Loop Operation

  • Thread starter Thread starter LiCal
  • Start date Start date
L

LiCal

I am testing two variables of similarly named and sequentially
numbered through a do..loop.

I know that I can do that via var() array.

but if I want to change the variable name via string changes, how to
do it?

........
day_1 = date
day_2 = dateadd("d", 7, day_1)

nn=1

do
msgbox "day_" & nn ' not working this way,
nn= nn+1
loop until nn>=3

.............

........
 
I am testing two variables of similarly named and sequentially
numbered through a do..loop.

I know that I can do that via var() array.

but if I want to change the variable name via string changes, how to
do it?

.......
day_1 = date
day_2 = dateadd("d", 7, day_1)

nn=1

do
msgbox "day_" & nn ' not working this way,
nn= nn+1
loop until nn>=3

............

........
Click to expand...

You could use a For...Next loop and use the counter like so...

Dim i As Integer
For i = 1 To 3
MsgBox "Day_" & CStr(i)
Next 'i
 
You could use a For...Next loop and use the counter like so...

  Dim i As Integer
  For i = 1 To 3
    MsgBox "Day_" & CStr(i)
  Next 'i
Click to expand...


I was trying to get the msgbox to show the Date not "Day_1" etc.
 
LiCal used his keyboard to write :
I was trying to get the msgbox to show the Date not "Day_1" etc.
Click to expand...

Ok, put the dates in an array and loop the array...

Dim sDates, vaDates, i As Integer
For i = 0 To 14 Step 7
sDates = sDates & "|" & Date + i
Next 'i
sDates = Mid$(sDates, 2)
vaDates = Split(sDates, "|")
For i = LBound(vaDates) To UBound(vaDates)
Debug.Print vaDates(i)
Next 'i
 
You can fine tune that as follows...
Dim sDates, vaDates, i As Integer
For i = 0 To 14 Step 7: sDates = sDates & "|" & Date + i: Next 'i
Click to expand...
vaDates = Split(Mid$(sDates, 2), "|")
For i = LBound(vaDates) To UBound(vaDates)
Debug.Print vaDates(i)
Next 'i
Click to expand...

BTW, you can change any part of a defined variable after it's been
defined.
 
Alternative approach...

Put dates directly into an array, resizing the array as you go.

Sub GetDates()
Dim vaDates(), i As Integer, j As Integer
For i = 0 To 14 Step 7
ReDim Preserve vaDates(j)
vaDates(j) = Date + i: j = j + 1
Next 'i
For i = LBound(vaDates) To UBound(vaDates)
Debug.Print vaDates(i)
Next 'i
End Sub
 
VBA doesn't allow you to create variable names this way. But you don't have to,
either. You can use Arithmetic.

Maybe...

Maybe:
msgbox "day_" & date + nn
or
msgbox "day_" & date + (nn * 7)

or with formatting:

msgbox "day_" & format(date + nn, "dd-mmm-yyyy")
or
msgbox "day_" & format(date + (nn * 7), "dd-mmm-yyyy")
 
Back
Top