Programming Excel with VBA and .NET

Change Recorded Code

Recorded code is a great way to learn, but it’s not really a program. Real programs are much more flexible, and recorded code always has the following limitations:

Follows a linear path

Excel can’t infer logic from the user’s actions—even if the user repeats an action, Excel only records the action twice. It doesn’t know how to record “repeat until end” or “do this task if...” Excel can “replace all” and perform other global actions, but that’s still linear logic.

Actions apply to the active item

Excel bases recorded actions on whatever worksheet, range, or chart is currently selected by the user. If you want your code to work with other sheets or ranges, you need to either change the active selection or add object references that tell the code which items to work with.

Uses cell addresses

For example, Range("A1:C16"). Although Excel keeps references on worksheets up-to-date, Excel can’t update addresses in code. That means if your data is moved on the worksheet, the code won’t work correctly. To fix this, use Excel range properties or named ranges instead of addresses in code.

Methods include all the default arguments

That means lines of code are sometimes longer and more complicated than they really need to be. You can often simplify recording by removing unneeded default arguments.

Doesn’t use variables

Most programs create names to identify things that can change as the code executes. These names are called variables. Recorded code doesn’t ...