Access a control by name

I know a mathematician that can do magic with stats. That’s also the reason why he works at Eurostat. He’s automating a lot of his work by programming in Visual Basic for Applications. He asked me if i wanted to look at his code because he had the feeling there was a smell. Here are a couple lines:

With SomeForm
 .txtJanvier60.Value = vaData1(1, 2)
 .txtFevrier60.Value = vaData1(1, 3)
 .txtJanvier61.Value = vaData1(1, 2)
 .txtFevrier61.Value = vaData1(1, 3)
 ...
 .txtJanvier70.Value = vaData1(1, 2)
 .txtFevrier70.Value = vaData1(1, 3)
End With

It took me 5 minutes to search the web and change his code as following:

Dim months(1) as String
months(0) = "Janvier"
months(1) = "Fevrier"

With SomeForm
 For i = 60 to 70
  For j = 0 to UBound(months)
   .Controls("txt" & months(j) & CStr(i)).Value = vaData1(1, j + 2)
  Next j
 Next i
End With
This entry was posted in Visual Basic. Bookmark the permalink.

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>