Tim Van Wassenhove

Passionate geek, interested in Technology. Proud father of two

25 Feb 2006

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