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 on Saturday, February 25th, 2006 at 02:52 and is filed under Visual Basic. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

Comments are closed.