Yes, I know... There is a great book that I used to get started with VBA and
Excel a bunch of years ago. It is called Excel 2000 Power Programming with VBA
by Walkenbach... Chapter 27 has a section called using VBA to write  VBA code.
It shows how to create and execute entire functions as text and then execute
them.

But... the original post had a different type of self modifying code... the
poster was trying to create a variable name by concatenating a constant text
string to a integer that changed within a loop. I don't believe that this
kind of self modifying code is possible in VB or VBA but I would love to be
proven wrong. I believe that the only modern Microsoft Language that has this
capability is VFP. In this language it is called Macro Substitution.

BTW... VFP is a great way to automate MS Excel applications.... it lets you
create exe files that operate directly on the Excel Object using a language and
a program construction that is very similar to VBA. As a bonus, your
applications can be distributed without triggering the security warning that
comes with embedded macros.