Looping through an array

[DWTSG]

Here is my code

Dim curAmt(1 To 7) As Currency
Dim txtAmt(1 To 7) As String
Dim intCtr As Integer
Dim curTotal As Currency


For intCtr = 1 To 7
curAmt(intCtr) = Val(txtAmt(intCtr).Value)
curTotal = curTotal + curAmt(intCtr)
Next intCtr

'curAmt(2) = Val(txtAmt2.Value)
'curTotal = curTotal + curAmt(2)
'curAmt(3) = Val(txtAmt3.Value)
'curTotal = curTotal + curAmt(3)
'curAmt(4) = Val(txtAmt4.Value)
'curTotal = curTotal + curAmt(4)
'curAmt(5) = Val(txtAmt5.Value)
'curTotal = curTotal + curAmt(5)
'curAmt(6) = Val(txtAmt6.Value)
'curTotal = curTotal + curAmt(6)
'curAmt(7) = Val(txtAmt7.Value)
'curTotal = curTotal + curAmt(7)

I have commented out the portion of code that the Array and Loop are
supposed to take the place of. txtAmt are text boxes on a user form. My
problem arises from this line curAmt(intCtr) = Val(txtAmt(intCtr).Value)
.. I get a invalid qualifier error. If I change the array data type in the
declaration. I get a debug error.
Any suggestions would be great. Thanks

 

[Jay Freedman]

Hi, DWTSG,

If I recall correctly from your earlier post, the txtAmt names represent
text boxes on your userform. You can't use an array of strings for them --
there's no connection between the strings and the textboxes. The way to do
that is (a) remove the "Dim txtAmt" line, and (b) change the loop to this:

For intCtr = 1 To 7
curAmt(intCtr) = Val(Controls("txtAmt" & intCtr).Value)
curTotal = curTotal + curAmt(intCtr)
Next intCtr

What this does is take the literal string "txtAmt" and concatenate the
number in intCtr, so you get strings "txtAmt1", txtAmt2", and so on. Those
strings are used as indexes into the Controls collection of the userform, so
you get the same effect as if you had an array of controls (which VBA
doesn't support). Then the .Value and Val() functions work the same as in
your commented-out code.

 

[DWTSG]

Once again, Jay to the rescue. Thanks

Hi, DWTSG,

If I recall correctly from your earlier post, the txtAmt names represent
text boxes on your userform. You can't use an array of strings for them --
there's no connection between the strings and the textboxes. The way to do
that is (a) remove the "Dim txtAmt" line, and (b) change the loop to this:

For intCtr = 1 To 7
curAmt(intCtr) = Val(Controls("txtAmt" & intCtr).Value)
curTotal = curTotal + curAmt(intCtr)
Next intCtr

What this does is take the literal string "txtAmt" and concatenate the
number in intCtr, so you get strings "txtAmt1", txtAmt2", and so on. Those
strings are used as indexes into the Controls collection of the userform, so
you get the same effect as if you had an array of controls (which VBA
doesn't support). Then the .Value and Val() functions work the same as in
your commented-out code.